Is it possible to get this PostgreSQL query down from 50ms to the order of a few ms?

I have a query that I want to make as fast as possible. It’s this:

explain analyze
            select
                *
            from
                rtsepezocoav_102999 av
            join rtco_102097 c on
                c.lo_id = av.co_id
            where
                av.ad_id = 335
                and av.pe_id = 70
                and av.se_id = 12

​x
 
explain analyze            select                *            from                rtsepezocoav_102999 av            join rtco_102097 c on                c.lo_id = av.co_id            where                av.ad_id = 335                and av.pe_id = 70                and av.se_id = 12​

I get the following plan:

Hash Join  (cost=1238.88..3275.61 rows=40581 width=80) (actual time=10.341..47.707 rows=41238 loops=1)
  Hash Cond: (av.co_id = c.lo_id)
  ->  Bitmap Heap Scan on rtsepezocoav_p_70_103719 av  (cost=868.38..2798.54 rows=40581 width=68) (actual time=4.550..17.615 rows=41238 loops=1)
        Recheck Cond: ((se_id = 12) AND (ad_id = 335))
        Filter: (pe_id = 70)
        Heap Blocks: exact=360
        ->  Bitmap Index Scan on rtsepezocoav_p_70_103719_se_id_ad_id_idx  (cost=0.00..858.23 rows=40581 width=0) (actual time=4.450..4.450 rows=41238 loops=1)
              Index Cond: ((se_id = 12) AND (ad_id = 335))
  ->  Hash  (cost=204.67..204.67 rows=13267 width=12) (actual time=5.759..5.759 rows=13267 loops=1)
        Buckets: 16384  Batches: 1  Memory Usage: 699kB
        ->  Seq Scan on rtco_102097 c  (cost=0.00..204.67 rows=13267 width=12) (actual time=0.009..2.125 rows=13267 loops=1)
Planning Time: 0.515 ms
Execution Time: 51.100 ms

 
Hash Join  (cost=1238.88..3275.61 rows=40581 width=80) (actual time=10.341..47.707 rows=41238 loops=1)  Hash Cond: (av.co_id = c.lo_id)  ->  Bitmap Heap Scan on rtsepezocoav_p_70_103719 av  (cost=868.38..2798.54 rows=40581 width=68) (actual time=4.550..17.615 rows=41238 loops=1)        Recheck Cond: ((se_id = 12) AND (ad_id = 335))        Filter: (pe_id = 70)        Heap Blocks: exact=360        ->  Bitmap Index Scan on rtsepezocoav_p_70_103719_se_id_ad_id_idx  (cost=0.00..858.23 rows=40581 width=0) (actual time=4.450..4.450 rows=41238 loops=1)              Index Cond: ((se_id = 12) AND (ad_id = 335))  ->  Hash  (cost=204.67..204.67 rows=13267 width=12) (actual time=5.759..5.759 rows=13267 loops=1)        Buckets: 16384  Batches: 1  Memory Usage: 699kB        ->  Seq Scan on rtco_102097 c  (cost=0.00..204.67 rows=13267 width=12) (actual time=0.009..2.125 rows=13267 loops=1)Planning Time: 0.515 msExecution Time: 51.100 ms​

Which is not terrible, I guess. But the actual query is more involved + I’ll be running this query in parallel on different shards. So I’m really focussed on getting this lightning quick. Is there anything I’m missing, or is this just the limit of postgres ? It seems a bit ‘slow’ in my mind, to join 40K records (rtsepezocoav_p_70_103719) with 10K records (rtco).

rtsepezocoav_p_70_103719 has indexes on: (ad_id, pe_id, se_id) (ad_id, se_id) (co_id)

rtco has index on (lo_id)

One of the first things that comes to mind, why does it use a “Bitmap Heap Scan” instead of an index only scan ?

When I do:

explain analyze select 1 from rtsepezocoav_102999 av

 
explain analyze select 1 from rtsepezocoav_102999 av​

I get an index only scan, and it takes 15ms (also seems long).

I’m on Postgres 12.

Answer

Thx for the tips, but in all honesty I think I was indeed performing a premature opimization…

Advertisement

Answer