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