0

SQL

CREATE TABLE actions (
    action_id  bigint GENERATED ALWAYS AS IDENTITY,
    block_time timestamptz NOT NULL DEFAULT now(),
    block_num  int NOT NULL,
    trx_id     text NOT NULL,
    contract   text NOT NULL,
    action     text NOT NULL,
    path       text NOT NULL,
    "authorization" jsonb NOT NULL,
    account_ram_deltas jsonb,
    receivers  jsonb NOT NULL,
    data       jsonb,

    CONSTRAINT actions_pkey PRIMARY KEY (action_id, block_time)
 )

CREATE INDEX idxcontractaction ON actions (contract, action);
CREATE INDEX idxdatagin ON actions USING GIN (data);
CREATE INDEX idxreceivergin ON actions USING GIN (receivers jsonb_path_ops);
SELECT create_hypertable('actions', 'block_time', chunk_time_interval => 259200000000);

Query 1:

Query 1 works great for small number of rows

SELECT action_id
FROM actions
WHERE receivers @> '"smalluser"'
ORDER BY block_time desc, block_num desc
LIMIT 100 
"Limit  (cost=718048.53..718060.51 rows=100 width=20) (actual time=24.415..26.896 rows=100 loops=1)"
"  ->  Gather Merge  (cost=718048.53..796111.69 rows=651968 width=20) (actual time=24.413..26.638 rows=100 loops=1)"
"        Workers Planned: 4"
"        Workers Launched: 4"
"        ->  Sort  (cost=717048.48..717455.96 rows=162992 width=20) (actual time=1.889..1.901 rows=20 loops=5)"
"              Sort Key: _hyper_125_1638_chunk.block_time DESC, _hyper_125_1638_chunk.block_num DESC"
"              Sort Method: top-N heapsort  Memory: 39kB"
"              Worker 0:  Sort Method: quicksort  Memory: 25kB"
"              Worker 1:  Sort Method: quicksort  Memory: 25kB"
"              Worker 2:  Sort Method: quicksort  Memory: 25kB"
"              Worker 3:  Sort Method: quicksort  Memory: 25kB"
"              ->  Parallel Append  (cost=478.46..710819.04 rows=162992 width=20) (actual time=0.046..1.532 rows=389 loops=5)"
"                    ->  Parallel Bitmap Heap Scan on _hyper_125_1638_chunk  (cost=609.18..80416.60 rows=18480 width=20) (actual time=0.024..0.039 rows=9 loops=1)"
"                          Recheck Cond: (receivers @> '"eoscafeblock"'::jsonb)"
"                          Heap Blocks: exact=5"
"                          ->  Bitmap Index Scan on _hyper_125_1638_chunk_idxreceivertimegin  (cost=0.00..590.70 rows=73919 width=0) (actual time=0.019..0.020 rows=9 loops=1)"
"                                Index Cond: (receivers @> '"eoscafeblock"'::jsonb)"
"                    ->  Parallel Bitmap Heap Scan on _hyper_125_1640_chunk  (cost=483.97..63912.79 rows=14689 width=20) (actual time=0.022..0.041 rows=11 loops=1)"
"                          Recheck Cond: (receivers @> '"eoscafeblock"'::jsonb)"
"                          Heap Blocks: exact=7"
...

Query 1 is slow for large number of rows

SELECT action_id
FROM actions
WHERE receivers @> '"largeuser"'
ORDER BY block_time desc, block_num desc
LIMIT 100 
"Limit  (cost=718048.53..718060.51 rows=100 width=20) (actual time=6011.292..6179.493 rows=100 loops=1)"
"  ->  Gather Merge  (cost=718048.53..796111.69 rows=651968 width=20) (actual time=6011.290..6179.313 rows=100 loops=1)"
"        Workers Planned: 4"
"        Workers Launched: 4"
"        ->  Sort  (cost=717048.48..717455.96 rows=162992 width=20) (actual time=5991.026..5991.079 rows=80 loops=5)"
"              Sort Key: _hyper_125_1638_chunk.block_time DESC, _hyper_125_1638_chunk.block_num DESC"
"              Sort Method: top-N heapsort  Memory: 40kB"
"              Worker 0:  Sort Method: top-N heapsort  Memory: 40kB"
"              Worker 1:  Sort Method: top-N heapsort  Memory: 40kB"
"              Worker 2:  Sort Method: top-N heapsort  Memory: 40kB"
"              Worker 3:  Sort Method: top-N heapsort  Memory: 40kB"
"              ->  Parallel Append  (cost=478.46..710819.04 rows=162992 width=20) (actual time=44.080..5325.551 rows=937111 loops=5)"
"                    ->  Parallel Bitmap Heap Scan on _hyper_125_1638_chunk  (cost=609.18..80416.60 rows=18480 width=20) (actual time=52.257..1062.208 rows=83552 loops=1)"
"                          Recheck Cond: (receivers @> '"eosio"'::jsonb)"
"                          ->  Bitmap Index Scan on _hyper_125_1638_chunk_idxreceivertimegin  (cost=0.00..590.70 rows=73919 width=0) (actual time=34.226..34.227 rows=83552 loops=1)"
"                                Index Cond: (receivers @> '"eosio"'::jsonb)"
"                    ->  Parallel Bitmap Heap Scan on _hyper_125_1640_chunk  (cost=483.97..63912.79 rows=14689 width=20) (actual time=77.049..1406.442 rows=198923 loops=1)"
"                          Recheck Cond: (receivers @> '"eosio"'::jsonb)"
"                          ->  Bitmap Index Scan on _hyper_125_1640_chunk_idxreceivertimegin  (cost=0.00..469.28 rows=58757 width=0) (actual time=55.217..55.218 rows=198923 loops=1)"
"                                Index Cond: (receivers @> '"eosio"'::jsonb)"
...

Query 2

Query 2 works great for large number of rows

SELECT action_id
FROM actions
WHERE receivers @> '"largeuser"'
ORDER BY block_time desc
LIMIT 100 
"Limit  (cost=0.43..0.00 rows=100 width=16) (actual time=0.108..8.455 rows=100 loops=1)"
"  ->  Append  (cost=0.43..0.00 rows=100 width=16) (actual time=0.106..8.345 rows=100 loops=1)"
"        ->  Index Scan using _hyper_125_1650_chunk_actions_block_time_idx on _hyper_125_1650_chunk  (cost=0.43..1443629.70 rows=3022 width=16) (actual time=0.105..8.238 rows=100 loops=1)"
"              Filter: (receivers @> '"eosio"'::jsonb)"
"              Rows Removed by Filter: 20403"
"        ->  Index Scan using _hyper_125_1646_chunk_actions_block_time_idx on _hyper_125_1646_chunk  (cost=0.56..30888708.94 rows=38785 width=16) (never executed)"
"              Filter: (receivers @> '"eosio"'::jsonb)"
"        ->  Index Scan using _hyper_125_1645_chunk_actions_block_time_idx on _hyper_125_1645_chunk  (cost=0.56..41753084.10 rows=58071 width=16) (never executed)"
"              Filter: (receivers @> '"eosio"'::jsonb)"
"        ->  Index Scan using _hyper_125_1643_chunk_actions_block_time_idx on _hyper_125_1643_chunk  (cost=0.56..36909013.79 rows=58494 width=16) (never executed)"
"              Filter: (receivers @> '"eosio"'::jsonb)"
"        ->  Index Scan using _hyper_125_1640_chunk_actions_block_time_idx on _hyper_125_1640_chunk  (cost=0.56..48487135.99 rows=58733 width=16) (never executed)"
"              Filter: (receivers @> '"eosio"'::jsonb)"
"        ->  Index Scan using _hyper_125_1638_chunk_actions_block_time_idx on _hyper_125_1638_chunk  (cost=0.57..64549185.03 rows=73937 width=16) (never executed)"
"              Filter: (receivers @> '"eosio"'::jsonb)"
"        ->  Index Scan using _hyper_125_1635_chunk_actions_block_time_idx on _hyper_125_1635_chunk  (cost=0.56..40785225.64 rows=42479 width=16) (never executed)"
"              Filter: (receivers @> '"eosio"'::jsonb)"
"        ->  Index Scan using _hyper_125_1634_chunk_actions_block_time_idx on _hyper_125_1634_chunk  (cost=0.56..15406337.32 rows=24148 width=16) (never executed)"
"              Filter: (receivers @> '"eosio"'::jsonb)"
"        ->  Index Scan using _hyper_125_1630_chunk_actions_block_time_idx on _hyper_125_1630_chunk  (cost=0.56..28863768.79 rows=34805 width=16) (never executed)"
...

Query 2 is very slow for small number (~1000)

SELECT action_id
FROM actions
WHERE receivers @> '"smalluser"'
ORDER BY block_time desc
LIMIT 100 
"Limit  (cost=0.43..0.00 rows=100 width=16)"
"  ->  Append  (cost=0.43..0.00 rows=100 width=16)"
"        ->  Index Scan using _hyper_125_1650_chunk_actions_block_time_idx on _hyper_125_1650_chunk  (cost=0.43..1443706.36 rows=3022 width=16)"
"              Filter: (receivers @> '"eoscafeblock"'::jsonb)"
"        ->  Index Scan using _hyper_125_1646_chunk_actions_block_time_idx on _hyper_125_1646_chunk  (cost=0.56..30887314.42 rows=38783 width=16)"
"              Filter: (receivers @> '"eoscafeblock"'::jsonb)"
"        ->  Index Scan using _hyper_125_1645_chunk_actions_block_time_idx on _hyper_125_1645_chunk  (cost=0.56..41735220.08 rows=58046 width=16)"
"              Filter: (receivers @> '"eoscafeblock"'::jsonb)"
"        ->  Index Scan using _hyper_125_1643_chunk_actions_block_time_idx on _hyper_125_1643_chunk  (cost=0.56..36916526.01 rows=58506 width=16)"
"              Filter: (receivers @> '"eoscafeblock"'::jsonb)"
"        ->  Index Scan using _hyper_125_1640_chunk_actions_block_time_idx on _hyper_125_1640_chunk  (cost=0.56..48506672.77 rows=58757 width=16)"
"              Filter: (receivers @> '"eoscafeblock"'::jsonb)"
"        ->  Index Scan using _hyper_125_1638_chunk_actions_block_time_idx on _hyper_125_1638_chunk  (cost=0.57..64533892.66 rows=73919 width=16)"
"              Filter: (receivers @> '"eoscafeblock"'::jsonb)"
"        ->  Index Scan using _hyper_125_1635_chunk_actions_block_time_idx on _hyper_125_1635_chunk  (cost=0.56..40794773.18 rows=42489 width=16)"
"              Filter: (receivers @> '"eoscafeblock"'::jsonb)"
"        ->  Index Scan using _hyper_125_1634_chunk_actions_block_time_idx on _hyper_125_1634_chunk  (cost=0.56..15404798.54 rows=24146 width=16)"
"              Filter: (receivers @> '"eoscafeblock"'::jsonb)"
"        ->  Index Scan using _hyper_125_1630_chunk_actions_block_time_idx on _hyper_125_1630_chunk  (cost=0.56..28861060.88 rows=34801 width=16)"
"              Filter: (receivers @> '"eoscafeblock"'::jsonb)"
"        ->  Index Scan using _hyper_125_1629_chunk_actions_block_time_idx on _hyper_125_1629_chunk  (cost=0.56..40934554.82 rows=43126 width=16)"
...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.