How to improve PostgreSQL intersect speed?-CodePudding

I have created the following table in postgres:

CREATE TABLE mytable (id serial, c1 int, c2 int)

The following query takes 0.9 ms to complete:

select id from mytable where c1=555 and c2=444;

The following query takes 6.5 ms to complete:

select id from mytable where c1=555 intersect select id from mytable where c2=444;

The query plan for the first query is:

 Bitmap Heap Scan on mytable  (cost=46.90..54.84 rows=2 width=4) (actual time=0.623..0.626 rows=2 loops=1)
   Recheck Cond: ((c2 = 444) AND (c1 = 555))
   Heap Blocks: exact=2
   ->  BitmapAnd  (cost=46.90..46.90 rows=2 width=0) (actual time=0.609..0.610 rows=0 loops=1)
         ->  Bitmap Index Scan on i2  (cost=0.00..23.31 rows=1985 width=0) (actual time=0.267..0.267 rows=1978 loops=1)
               Index Cond: (c2 = 444)
         ->  Bitmap Index Scan on i1  (cost=0.00..23.33 rows=1987 width=0) (actual time=0.258..0.258 rows=1988 loops=1)
               Index Cond: (c1 = 555)

and for the second plan is:

HashSetOp Intersect  (cost=23.81..10244.16 rows=1985 width=8) (actual time=6.784..6.804 rows=2 loops=1)
   ->  Append  (cost=23.81..10234.23 rows=3972 width=8) (actual time=0.543..6.093 rows=3966 loops=1)
         ->  Subquery Scan on "*SELECT* 2"  (cost=23.81..5106.08 rows=1985 width=8) (actual time=0.542..2.928 rows=1978 loops=1)
               ->  Bitmap Heap Scan on mytable  (cost=23.81..5086.23 rows=1985 width=4) (actual time=0.540..2.636 rows=1978 loops=1)
                     Recheck Cond: (c2 = 444)
                     Heap Blocks: exact=1810
                     ->  Bitmap Index Scan on i2  (cost=0.00..23.31 rows=1985 width=0) (actual time=0.331..0.331 rows=1978 loops=1)
                           Index Cond: (c2 = 444)
         ->  Subquery Scan on "*SELECT* 1"  (cost=23.83..5108.29 rows=1987 width=8) (actual time=0.537..2.790 rows=1988 loops=1)
               ->  Bitmap Heap Scan on mytable mytable_1  (cost=23.83..5088.42 rows=1987 width=4) (actual time=0.536..2.495 rows=1988 loops=1)
                     Recheck Cond: (c1 = 555)
                     Heap Blocks: exact=1812
                     ->  Bitmap Index Scan on i1  (cost=0.00..23.33 rows=1987 width=0) (actual time=0.340..0.340 rows=1988 loops=1)
                           Index Cond: (c1 = 555)

The difference in speed seems to be due to BitmapAnd being used in the first query execution. Is there anyway to make postgres do a similar execution for the second query as well ?

CodePudding user response：

Stepping onto an unpinned page which is already in shared buffers is quite slow. Not compared to reading that page from disk of course, but compared to anything else you do. Several atomic operations are required to make sure concurrent processes don't corrupt each other while they do this, and it will almost surely also involve CPU cache misses. Your HashSetOp needs to do this a lot more than the BitmapAnd does (around 1800 times more, based on your query plan numbers--but that is only counting table pages). The BitmapAnd intersects ctids read from the index in its local memory and then only hits the table for those surviving the intersect, while your HashSetOp needs to hit the table before it can do the intersect because it is intersecting SQL fields, not ctids.

You can improve the bad query by getting it to use index-only scans. If the query doesn't need to hit the table at all because it can find the SQL field in the index, then it needs to visit far fewer pages (the index pages have many relevant tuples crammed into each page)

If I build indexes on (c1, id) and (c2, id), so I got each subquery to use index-only scans, the resulting HashSetOp plan is 3 to 4 times faster than the original HashSetOp plan without those indexes, while still being 2 to 3 times slower than the BitmapAnd. This is on a freshly VACUUMed table, so that all pages are marked all-visible.