RE: [GENERAL] Resend: Using some indexes but not others

[Culberson, Philip]

Maybe you didn't send the right snippet from your SQL, but your example
shows you building an index on "ref_ref_inspec", and the query you are
having a problem with has a WHERE clause referencing "ref_ref_article".


-----Original Message-----
From: Peter Haworth [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 16, 2000 2:24 AM
To: Postgres general list
Subject: [GENERAL] Resend: Using some indexes but not others


I sent this to the list a month ago, but got no response. Does anyone have
an
idea what's going on here?

------- Begin Forwarded Message -------
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.

-------  End Forwarded Message  -------

-- 
        Peter Haworth   [EMAIL PROTECTED]
Q: How many developers does it take to change a light bulb?
A: The light bulb works fine on the system in my office . . .