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
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 |