From: | Peter Haworth <pmh(at)edison(dot)ioppublishing(dot)com> |
---|---|
To: | Postgres general list <pgsql-general(at)postgresql(dot)org> |
Subject: | Using some indexes but not others |
Date: | 2000-02-14 12:07:16 |
Message-ID: | ML-3.3.950530036.6838.pmh@edison.ioppublishing.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)edison(dot)ioppublishing(dot)com
"You're not going to watch the eclipse in yesterday's underpants?"
From | Date | Subject | |
---|---|---|---|
Next Message | RK Street | 2000-02-14 16:21:12 | Multi-Language Support and/or UTF-8 UNICODE |
Previous Message | Yury Don | 2000-02-14 12:07:11 | Re: [GENERAL] converting MSAccess db tables into postgresql |