GIN low hanging fruit

From: Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: GIN low hanging fruit
Date: 2013-06-29 10:03:55
Message-ID: 51CEB10B.8020009@vmware.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
inline-gin-compareitempointers-1.patch text/x-diff 2.3 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Marc Mamin 2013-06-29 11:02:00 Re: psql and pset without any arguments
Previous Message Pavel Stehule 2013-06-29 09:19:43 Re: checking variadic "any" argument in parser - should be array