btree_gin and BETWEEN

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: btree_gin and BETWEEN
Date: 2015-06-23 16:29:46
Message-ID: CAMkU=1x0vVdDLsydYu6V9M0n1k5aw1mNiS1C8oL6mPWSuc=kwg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

If I use the btree_gin extension to build a gin index on a scalar value, it
doesn't work well with BETWEEN queries. It looks like it scans the whole
index, with the part of the index between the endpoints getting scanned
twice. It is basically executed as if "col1 between x and y" were "col1
between -inf and y and col1 between x and +inf".

It puts the correct tuples into the bitmap, because whichever inequality is
not being used to set the query endpoint currently is used as a filter
instead.

So I could just not build that index. But I want it for other reasons, and
the problem is that the planner thinks the index can implement the BETWEEN
query efficiently. So even if it has truly better options available, it
switches to using a falsely attractive btree_gin index.

create table foo as select random() as btree, random() as gin from
generate_series(1,3000000);
create index on foo using gin (gin);
create index on foo using btree (btree);
explain ( analyze, buffers) select count(*) from foo where btree between
0.001 and 0.00105;
explain ( analyze, buffers) select count(*) from foo where gin between
0.001 and 0.00105;

It would be nice if btree_gin supported BETWEEN and other range queries
efficiently, or at least if the planner knew it couldn't support them
efficiently. But I don't see where to begin on either one of these tasks.
Is either one of them plausible?

Cheers,

Jeff

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2015-06-23 16:40:45 Re: pg_rewind failure by file deletion in source server
Previous Message Robert Haas 2015-06-23 15:27:36 Re: Hash index creation warning