Array index not used for query on first element?
It seemed reasonable to me that a select on the first element of an
array column could use an index on the column, but, as seen in this
example, I can't get it to do so:
=> create temp table tempPaths (path int[] primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"temppaths_pkey" for table "temppaths"
CREATE TABLE
=> set enable_seqscan to off;
SET
=> explain select * from temppaths where path[1] = 43;
QUERY PLAN
------------------------------------------------------------------------
--
Seq Scan on temppaths (cost=100000000.00..100000022.50 rows=5
width=32)
Filter: ("path"[1] = 43)
(2 rows)
This is under 7.4. Is this different on less paleolithic versions of
PG, or is there some other issue?
Thanks.
- John Burger
MITRE
On Dec 7, 2007 4:12 PM, John D. Burger <john@mitre.org> wrote:
This is under 7.4.
Urgh!
Is this different on less paleolithic versions of
PG, or is there some other issue?
Same here:
select version();
PostgreSQL 8.3beta4, compiled by Visual C++ build 1400
select * from temppaths where path[1] = 43;
Seq Scan on temppaths (cost=0.00..26.38 rows=7 width=32) (actual
time=0.005..0.005 rows=0 loops=1)
Filter: (path[1] = 43)
Total runtime: 0.065 ms
Maybe you could use an expression index:
create index axo on temppaths((path[1]));
select * from temppaths where path[1] = 43;
Bitmap Heap Scan on temppaths (cost=4.30..14.45 rows=7 width=32)
(actual time=0.018..0.018 rows=0 loops=1)
Recheck Cond: (path[1] = 43)
-> Bitmap Index Scan on axo (cost=0.00..4.30 rows=7 width=0)
(actual time=0.012..0.012 rows=0 loops=1)
Index Cond: (path[1] = 43)
Total runtime: 0.106 ms
Good luck.
"John D. Burger" <john@mitre.org> writes:
It seemed reasonable to me that a select on the first element of an
array column could use an index on the column, but, as seen in this
example, I can't get it to do so:
Nope. The operators that go along with a btree index are equality,
less than, etc on the whole indexed column. btree knows nothing
about extracting array elements.
You might find that contrib/intarray would help, though the operators it
can index are not exactly "array[x] = y".
regards, tom lane
Tom Lane wrote:
It seemed reasonable to me that a select on the first element of an
array column could use an index on the column, but, as seen in this
example, I can't get it to do so:Nope. The operators that go along with a btree index are equality,
less than, etc on the whole indexed column. btree knows nothing
about extracting array elements.
I guess I was working by analogy with queries like this:
select * from someTable where textColumn like 'foo%';
This will happily use an ordinary btree index on textColumn, right?
(Although I know it's only for the C locale with the default op
class.) Anyway, I guess this is a far more common case than matching
the prefix of an array. Like another poster suggested, I can have an
additional expression index on the first element of my array column.
- John Burger
MITRE