PostgreSQL – JSONB Query Performance Decreased by ORDER BY

jsonperformancepostgresql

I have a table with jsonb column. When I execute query below it evaluates quite fast. But then I need to add order by clause (created column is timestamp column). Execution time decreases by 1000 times.

select * from delivery where (document #> '{links}') @> '[{"type": "receipt","externalId": "9990863500764120180212"}]' limit 100;

Limit  (cost=0.00..4198.36 rows=100 width=137) (actual time=0.100..2.351 rows=100 loops=1)
  ->  Seq Scan on delivery  (cost=0.00..6843.32 rows=163 width=137) (actual time=0.099..2.338 rows=100 loops=1)
        Filter: ((document #> '{links}'::text[]) @> '[{"type": "receipt", "externalId": "9990863500764120180212"}]'::jsonb)
        Rows Removed by Filter: 1
Planning time: 0.184 ms
Execution time: 2.384 ms

select * from delivery where (document #> '{links}') @> '[{"type": "receipt","externalId": "9990863500764120180212"}]' order by created limit 100;

Limit  (cost=6423.11..6434.78 rows=100 width=137) (actual time=2243.874..2270.206 rows=100 loops=1)
  ->  Gather Merge  (cost=6423.11..6438.98 rows=136 width=137) (actual time=2243.872..2270.187 rows=100 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=5423.09..5423.26 rows=68 width=137) (actual time=2237.522..2237.617 rows=251 loops=3)
              Sort Key: created
              Sort Method: external sort  Disk: 8552kB
              ->  Parallel Seq Scan on delivery  (cost=0.00..5421.02 rows=68 width=137) (actual time=0.160..2152.447 rows=48670 loops=3)
                    Filter: ((document #> '{links}'::text[]) @> '[{"type": "receipt", "externalId": "9990863500764120180212"}]'::jsonb)
                    Rows Removed by Filter: 5518
Planning time: 0.556 ms
Execution time: 2272.830 ms

I tried to create index on created field – nothing happened. How can I improve performance of this query?

UPD I've tried to use indexes (btree on created and gin on links) as @LaurenzAlbe adviced.

Limit  (cost=601.36..601.61 rows=100 width=139) (actual time=4833.531..4833.555 rows=100 loops=1)
  ->  Sort  (cost=601.36..601.76 rows=163 width=139) (actual time=4833.529..4833.544 rows=100 loops=1)
        Sort Key: created
        Sort Method: top-N heapsort  Memory: 74kB
        ->  Bitmap Heap Scan on delivery  (cost=41.26..595.37 rows=163 width=139) (actual time=29.329..4801.109 rows=146010 loops=1)
              Recheck Cond: ((document #> '{links}'::text[]) @> '[{"type": "receipt", "externalId": "9990863500764120180212"}]'::jsonb)
              Heap Blocks: exact=4128
              ->  Bitmap Index Scan on delivery_expr_idx  (cost=0.00..41.22 rows=163 width=0) (actual time=28.531..28.531 rows=146010 loops=1)
                    Index Cond: ((document #> '{links}'::text[]) @> '[{"type": "receipt", "externalId": "9990863500764120180212"}]'::jsonb)
Planning time: 1.144 ms
Execution time: 4833.618 ms

Best Answer

Remove the GIN index delivery_expr_idx you created and make sure that created still has an index.

Then try

SELECT *
FROM (SELECT *
      FROM delivery
      ORDER BY created
      OFFSET 0 LIMIT 10000) AS q
WHERE document @>
      '{"links": [{"type": "receipt","externalId": "9990863500764120180212"}]}'
ORDER BY created
LIMIT 100;

I'm cheating with the LIMIT 10000, but since almost all rows satisfy the WHERE condition, I hope to get away with it.

Related Question