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