citusdata / citusdata/citus

Query not using indices when using columnar storage

Open
#7,524 7 comments 1 reaction 0 assignees View on GitHub
columnar
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.