pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Martin Sarsale <martin@emepe3.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: seqscan instead of index scan
Date: Mon, 30 Aug 2004 16:48:05 -0400
Message-ID: <11309.1093898885@sss.pgh.pa.us> (raw)
In-Reply-To: <1093887997.1680.71.camel@kadaif>
References: <1093887997.1680.71.camel@kadaif>

Martin Sarsale <martin@emepe3.net> writes:
> I indexed columns c and d (separately) but this query used the slow
> seqscan instead of the index scan:

> select * from t where c<>0 or d<>0;

> After playing some time, I noticed that if I change the "or" for an
> "and", pg used the fast index scan (but the query w/ 'and' was not what
> I as looking for).

I don't think so.  <> is not an indexable operator --- it appears
nowhere in the index operator classes.  It would help if you showed us
*exactly* what you did instead of a not-very-accurate filtered version.
I'm going to assume that you meant > ...

> Now: I've no idea why it chooses to use a seq scan instead of the index
> scan (yes, I've just vacuum analyzed the table before running the
> query).

Because 7.4 doesn't have statistics about expression indexes, so it has
no idea that there are only a few rows with c+d > 0.

What I'd suggest is

	select * from t where c>0 union select * from t where d>0

with separate indexes on c and d.

Another possibility is a partial index on exactly the condition you
want:

	create index nonzero on t(c) where c>0 or d>0;

although I'm not certain if 7.4 has enough stats to recognize this as a win.

			regards, tom lane



view thread (14+ messages)  latest in thread

Message-ID: <11309.1093898885@sss.pgh.pa.us>
Permalink:  ../11309.1093898885@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/11309.1093898885@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, martin@emepe3.net
  Subject: Re: seqscan instead of index scan
  In-Reply-To: <11309.1093898885@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox