Query not using indices when using columnar storage
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
PG 15
Citus 12.1
I am evaluating the columnar storage to compress old data for our use case.
So far the compression ratio is great, but the querying is very slow.
After analysis, and despite defining a primary key index, _all queries are scanning the whole table_.
(Domain model is ethereum call data)
Schemas :
```
Table "public.calls_columnar"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------+-----------+-----------+----------+---------+----------+-------------+--------------+-------------
height | height | | not null | | plain | | |
tx_index | integer | | not null | | plain | | |
trace_index | integer | | not null | | plain | | |
trace_address | integer[] | | | | extended | | |
subtraces | integer | | | | plain | | |
error | text | | | | extended | | |
action_from | bytea | | | | extended | | |
action_to | bytea | | | | extended | | |
action_value | numeric | | | | main | | |
action_input | bytea | | | | extended | | |
action_gas | numeric | | | | main | | |
action_call_type | call_type | | | | plain | | |
result_output | bytea | | | | extended | | |
result_gas_used | bigint | | | | plain | | |
Indexes:
"calls_columnar_pkey" PRIMARY KEY, btree (height, tx_index, trace_index)
Access method: columnar
```
```
Table "public.calls"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------+-----------+-----------+----------+---------+----------+-------------+--------------+-------------
height | height | | not null | | plain | | |
tx_index | integer | | not null | | plain | | |
trace_index | integer | | not null | | plain | | |
trace_address | integer[] | | | | extended | | |
subtraces | integer | | | | plain | | |
error | text | | | | extended | | |
action_from | bytea | | | | extended | | |
action_to | bytea | | | | extended | | |
action_value | numeric | | | | main | | |
action_input | bytea | | | | extended | | |
action_gas | numeric | | | | main | | |
action_call_type | call_type | | | | plain | | |
result_output | bytea | | | | extended | | |
result_gas_used | bigint | | | | plain | | |
Indexes:
"calls_pkey" PRIMARY KEY, btree (height, tx_index, trace_index)
Access method: heap
```
Exemple of a query that should be fast using the index (as index support is claimed in the documentation) :
```
explain analyse select * from calls_columnar where height between 1000 and 2000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Custom Scan (ColumnarScan) on calls_columnar (cost=0.00..909705.14 rows=575113 width=288) (actual time=130.746..50768.705 rows=153736 loops=1)
Filter: (((height)::bigint >= 1000) AND ((height)::bigint <= 2000))
Rows Removed by Filter: 114868939
Columnar Projected Columns: height, tx_index, trace_index, trace_address, subtraces, error, action_from, action_to, action_value, action_input, action_gas, action_call_type, result_output, result_gas_used
Planning Time: 3.430 ms
JIT:
Functions: 1
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.326 ms, Inlining 2.154 ms, Optimization 7.891 ms, Emission 5.067 ms, Total 15.439 ms
Execution Time: 50773.834 ms
(10 rows)
```
We can see clearly no index usage in the plan.
For reference, here is the plan for the regular table.
```
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Index Scan using calls_pkey on calls (cost=0.57..1139168.99 rows=1962967 width=178) (actual time=1.055..103.694 rows=153736 loops=1)
Index Cond: (((height)::bigint >= 1000) AND ((height)::bigint <= 2000))
Planning Time: 0.318 ms
JIT:
Functions: 2
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 1.972 ms, Inlining 0.000 ms, Optimization 0.000 ms, Emission 0.000 ms, Total 1.972 ms
Execution Time: 111.912 ms
(8 rows)
```
I have the feeling I am either not using the whole columnar store correctly or there is some pitfall in the planner regarding costs.
Contributor guide
Assessment
This issue has not been assessed yet.