Using some indexes but not others

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?"

Browse pgsql-general by date

  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