Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 57A972E002F for ; Sun, 24 Feb 2008 15:10:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 92091-05 for ; Sun, 24 Feb 2008 15:10:24 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 743A72E002E for ; Sun, 24 Feb 2008 15:10:26 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m1OJAPVI006254; Sun, 24 Feb 2008 14:10:25 -0500 (EST) To: Sean Leach cc: pgsql-performance Subject: Re: Weird issue with planner choosing seq scan In-reply-to: <7ECB45D8-33ED-45FC-A0DA-D3DE25B6CC8E@wiggum.com> References: <5289.1203875431@sss.pgh.pa.us> <7ECB45D8-33ED-45FC-A0DA-D3DE25B6CC8E@wiggum.com> Comments: In-reply-to Sean Leach message dated "Sun, 24 Feb 2008 10:41:26 -0800" Date: Sun, 24 Feb 2008 14:10:25 -0500 Message-ID: <6253.1203880225@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/307 X-Sequence-Number: 28849 Sean Leach writes: > Now - here is prod: > db=> select count(1) from u_counts; > count > --------- > 3292215 > (1 row) > -> Seq Scan on u_counts c (cost=0.00..444744.45 > rows=1106691 width=4) (actual time=1429.996..7893.178 rows=1036015 > loops=1) > Filter: (stamp > (now() - '1 day'::interval)) Given that this scan actually is selecting about a third of the table, I'm not sure that the planner is doing the wrong thing. It's hard to see how an indexscan would be an improvement. [ thinks for a bit... ] Actually, the problem might be the 3M executions of now() and interval subtraction that you get in the seqscan case. What results do you get if you write it with a sub-select like this: explain analyze SELECT node,count(*) AS counts FROM u_counts c,res r WHERE c.res_id=r.id AND stamp > (SELECT current_timestamp - interval '1 day') AND r.rtype='udns' AND r.location=1 GROUP BY node; regards, tom lane