Обсуждение: 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