Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id EAA17475451 for ; Sun, 31 Mar 2002 10:28:25 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g2VFS9F28979; Sun, 31 Mar 2002 10:28:09 -0500 (EST) To: "Greg Sikorski" Cc: Peter Eisentraut , pgsql-admin@postgresql.org Subject: Re: <= Index. In-reply-to: References: Comments: In-reply-to "Greg Sikorski" message dated "Sun, 31 Mar 2002 15:48:47 +0100" Date: Sun, 31 Mar 2002 10:28:09 -0500 Message-ID: <28976.1017588489@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200203/354 X-Sequence-Number: 3888 "Greg Sikorski" 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