hydradatabase / hydradatabase/columnar
indexes are not preferred by the planner
- Dominant language
- C
- Stars
- 3k
- Forks
- 106
- PR merge metrics
- No merged PRs in 30d
Description
### What's wrong?
I used two tables `public.receipts` and `reader.receipts` in the test,`public.receipts` is a heap table, and `reader.receipts` is generated as columnar using pg_ivm. They have the same `hash` indexes on `action_hash`.
```
testnet=# \d+ public.block_receipts;
Unlogged table "public.block_receipts"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------------+------------------------+-----------+----------+-----------------------+----------+-------------+--------------+-------------
id | bigint | | not null | | plain | | |
block_height | bigint | | | | plain | | |
action_hash | character varying(64) | | not null | | extended | | |
gas_consumed | integer | | not null | 0 | plain | | |
contract_address | character varying(42) | | not null | ''::character varying | extended | | |
status | smallint | | not null | 0 | plain | | |
execution_revert_msg | character varying(255) | | not null | ''::character varying | extended | | |
Indexes:
"block_receipts_pkey" PRIMARY KEY, btree (id)
"idx_block_receipts_action_hash" hash (action_hash)
Access method: heap
testnet=# \d+ reader.block_receipts;
Table "reader.block_receipts"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------------+------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | bigint | | | | plain | | |
block_height | bigint | | | | plain | | |
action_hash | character varying(64) | | | | extended | | |
gas_consumed | integer | | | | plain | | |
contract_address | character varying(42) | | | | extended | | |
status | smallint | | | | plain | | |
execution_revert_msg | character varying(255) | | | | extended | | |
Indexes:
"block_receipts_index" UNIQUE, btree (id)
"idx_block_receipts_action_hash" hash (action_hash)
Access method: columnar
```
Then use the following query
```
select id,block_height,action_hash,gas_consumed,contract_address,status,execution_revert_msg
from reader.block_receipts
where action_hash='3866a5be503847400594d1911e5a411a83fc8b10f1ecd1aef4cb89bee7beeb94';
```
I have noticed that queries on columnar databases are relatively slow, and the query performance is comparable to when using no indexes.
explain results
```
#On Heap
Index Scan using idx_block_receipts_action_hash on block_receipts (cost=0.00..113907.92 rows=125081 width=786)
Index Cond: ((action_hash)::text = '3866a5be503847400594d1911e5a411a83fc8b10f1ecd1aef4cb89bee7beeb94'::text)
JIT:
Functions: 2
Options: Inlining false, Optimization false, Expressions true, Deforming true
(5 rows)
#On Columnar
Gather (cost=1000.00..30599.12 rows=125081 width=786)
Workers Planned: 7
-> Parallel Custom Scan (ColumnarScan) on block_receipts (cost=0.00..17091.02 rows=3573749 width=786)
Filter: ((action_hash)::text = '3866a5be503847400594d1911e5a411a83fc8b10f1ecd1aef4cb89bee7beeb94'::text)
Columnar Projected Columns: id, block_height, action_hash, gas_consumed, contract_address, status, execution_revert_msg
(5 rows)
Time: 0.941 ms
```
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.