While profiling Alexander's patches, I noticed that a lot of time is
spent in ginCompareItemPointers:
47,59% postmaster postgres gingetbitmap
46,73% postmaster postgres ginCompareItemPointers
2,31% postmaster postgres FunctionCall8Coll
1,54% postmaster postgres callConsistentFn
0,79% postmaster postgres ginarrayconsistent
0,63% postmaster postgres MemoryContextReset
I think much of the time spent in gingetbitmap has to do with calling
ginCompareItemPointers, too.
The query in question is this:
postgres=# explain analyze select * from intarrtbl where ii @>
'{1,2,3,4,5,6,7,8,9,10}'::int[];
QUERY PLAN
--------------------------------------------------------------------------------
-------------------------------------------------
Bitmap Heap Scan on intarrtbl (cost=3036.00..3040.01 rows=1 width=61)
(actual
time=405.461..405.461 rows=0 loops=1)
Recheck Cond: (ii @> '{1,2,3,4,5,6,7,8,9,10}'::integer[])
-> Bitmap Index Scan on gin_intarr_master (cost=0.00..3036.00
rows=1 width=
0) (actual time=405.458..405.458 rows=0 loops=1)
Index Cond: (ii @> '{1,2,3,4,5,6,7,8,9,10}'::integer[])
Total runtime: 405.482 ms
(5 rows)
Rewriting and inlining ginCompareItemPointers as attached helps a lot.
After the patch:
postgres=# explain analyze select * from intarrtbl where ii @>
'{1,2,3,4,5,6,7,8,9,10}'::int[];
QUERY PLAN
--------------------------------------------------------------------------------
-------------------------------------------------
Bitmap Heap Scan on intarrtbl (cost=3036.00..3040.01 rows=1 width=61)
(actual
time=149.694..149.694 rows=0 loops=1)
Recheck Cond: (ii @> '{1,2,3,4,5,6,7,8,9,10}'::integer[])
-> Bitmap Index Scan on gin_intarr_master (cost=0.00..3036.00
rows=1 width=
0) (actual time=149.691..149.691 rows=0 loops=1)
Index Cond: (ii @> '{1,2,3,4,5,6,7,8,9,10}'::integer[])
Total runtime: 149.716 ms
(5 rows)
This patch seems pretty uncontroversial, so I'll go commit it. Once we
get this elephant out of the room, performance testing the rest of the
gin patches become much more meaningful. We might want to optimize the
ItemPointerCompare function, used elsewhere in the backend, too, but
I'll leave that for a separate patch.
- Heikki