From: Tom Lane <tgl@sss.pgh.pa.us>
To: Greg Sikorski <gte@atomicrevs.demon.co.uk>
Cc: Peter Eisentraut <peter_e@gmx.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: <= Index.
Date: Sun, 31 Mar 2002 10:28:09 -0500
Message-ID: <28976.1017588489@sss.pgh.pa.us> (raw)
In-Reply-To: <OF12E0FEFE.59B34BA8-ON80256B8D.0050F0AD-80256B8D.00515EF1@atomicrevs.net>
References: <OF12E0FEFE.59B34BA8-ON80256B8D.0050F0AD-80256B8D.00515EF1@atomicrevs.net>
"Greg Sikorski" <gte@atomicrevs.demon.co.uk> writes:
> cmaster=# explain analyze SELECT user_id,channel_id FROM levels WHERE
> suspend_expires <= 1017589362 AND suspend_expires <> 0;
> NOTICE: QUERY PLAN:
> Seq Scan on levels (cost=0.00..13709.09 rows=2609 width=8) (actual
> time=208.98..1521.08 rows=17 loops=1)
> Total runtime: 1521.29 msec
First question is *can* the thing use an index? (Try "set enable_seqscan
to off" then explain again.) If not, it's probably a datatype
compatibility issue --- you'll need to quote or explicitly cast the
constant 1017589362 to match the type of suspend_expires.
If it could use an index but chooses not to, at least part of the
problem is the factor-of-100 overestimate in the number of matching
rows. That might be alleviated by increasing the statistics target
for the suspend_expires column. (ALTER TABLE SET STATISTICS, then
ANALYZE or VACUUM ANALYZE.)
You might also find that reducing random_page_cost produces better
indexscan cost estimates for your environment.
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-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, gte@atomicrevs.demon.co.uk, peter_e@gmx.net
Subject: Re: <= Index.
In-Reply-To: <28976.1017588489@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