I have a table with a few indexes, which I thought were going to speed up
queries using the indexed columns. however, this doesn't appear to be the case.
I've got something like this, but with more columns:
create table jnl_refs (
ref_article varchar(26) not null,
ref_seqno integer not null,
ref_ref_article varchar(26) not null,
primary key(ref_article,ref_seqno)
);
create index jnl_refs_ix_2 on jnl_refs
using btree(ref_ref_inspec);
I would expect both of the following queries to use the appropriate index,
especially after a vacuum analyze:
journals2=> explain select * from jnl_refs
journals2-> where ref_article='1367-2630/1/1/001';
NOTICE: QUERY PLAN:
Index Scan using jnl_refs_pkey on jnl_refs (cost=34.70 rows=334 width=284)
EXPLAIN
journals2=> explain select * from jnl_refs
journals2-> where ref_ref_article='1367-2630/1/1/001';
NOTICE: QUERY PLAN:
Seq Scan on jnl_refs (cost=18509.01 rows=219589 width=284)
EXPLAIN
What may be causing this is that 232000 rows out the 249000 in the table have
ref_ref_article=''. Initially, the ''s were nulls, and I'd like them to go back
to being null if possible, but I thought that might be what was screwing up the
index.
This is with Postgres 6.5.3 on Solaris 2.5.1. Is this expected behaviour, and
if so, is there something I can do to force the use of the index. Illustra
allowed "select ... using(index=jnl_refs_is_2)", but I can't see anything like
that in the documentation.
--
Peter Haworth pmh@edison.ioppublishing.com
"You're not going to watch the eclipse in yesterday's underpants?"
