Skip site navigation (1) Skip section navigation (2)

Re: <= Index.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Greg Sikorski" <gte(at)atomicrevs(dot)demon(dot)co(dot)uk>
Cc: Peter Eisentraut <peter_e(at)gmx(dot)net>, pgsql-admin(at)postgresql(dot)org
Subject: Re: <= Index.
Date: 2002-03-31 15:28:09
Message-ID: 28976.1017588489@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-admin
"Greg Sikorski" <gte(at)atomicrevs(dot)demon(dot)co(dot)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

In response to

pgsql-admin by date

Next:From: Greg SikorskiDate: 2002-03-31 19:25:11
Subject: Re: <= Index.
Previous:From: Greg SikorskiDate: 2002-03-31 14:48:47
Subject: Re: <= Index.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group