Re: Array index not used for query on first element?

On Dec 7, 2007 4:12 PM, John D. Burger <john(at)mitre(dot)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.