0

I am trying to optimize the following sql query

select * from begin_transaction where ("group"->>'id')::bigint = '5'

without using additional indexing i get this

Gather (cost=1000.00..91957.50 rows=4179 width=750) (actual time=0.158..218.972 rows=715002 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   -> Parallel Seq Scan on begin_transaction (cost=0.00..90539.60 rows=1741 width=750) (actual time=0.020..127.525 rows=238334 loops=3)
         Filter: (((""group"" ->> 'id'::text))::bigint = '5'::bigint)
         Rows Removed by Filter: 40250
Planning Time: 0.039 ms
Execution Time: 235.200 ms

if add index (btree)

CREATE INDEX begin_transaction_group_id_idx
     ON public.begin_transaction USING btree (((("group"->>'id'::text))::bigint))

I receive

Bitmap Heap Scan on begin_transaction (cost=80.81..13773.97 rows=4179 width=750) (actual time=43.647..414.756 rows=715002 loops=1)
   Recheck Cond: (((""group"" ->> 'id'::text))::bigint = '5'::bigint)
   Rows Removed by Index Recheck: 52117
   Heap Blocks: exact=50534 lossy=33026
   -> Bitmap Index Scan on begin_transaction_group_id_idx (cost=0.00..79.77 rows=4179 width=0) (actual time=35.852..35.852 rows=715002 loops=1)
         Index Cond: (((""group"" ->> 'id'::text))::bigint = '5'::bigint)
Planning Time: 0.045 ms
Execution Time: 429.968 ms

any ideas how to go about it to increase performance? the group field is jsonb.

jps
  • 20,041
  • 15
  • 75
  • 79
mr mcwolf
  • 2,574
  • 2
  • 14
  • 27
  • 1
    Does `vacuum analyze begin_transaction;` change anything? –  Feb 06 '23 at 15:32
  • 1
    You seem to be finding and returning most of the table. The best way to optimize this is to stop doing it. What are you doing with most of a million rows, once you do get them? – jjanes Feb 06 '23 at 15:37
  • Read [jsonb indexing](https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING). – Adrian Klaver Feb 06 '23 at 16:18
  • @jjanes, a join is made on this result, which additionally filters the rows. tomorrow I will try to extract the other data first and add this result to it. – mr mcwolf Feb 06 '23 at 17:13

0 Answers0