X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 69C8E9DCB87 for ; Sat, 4 Mar 2006 11:46:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 70693-03 for ; Sat, 4 Mar 2006 11:46:23 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 4F8539DCB6C for ; Sat, 4 Mar 2006 11:46:16 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 62E4B5647D; Sat, 4 Mar 2006 09:46:06 -0600 (CST) Received: (hashcash-sendmail, from uid 1001); Sat, 4 Mar 2006 09:46:05 -0600 Date: Sat, 4 Mar 2006 09:46:05 -0600 From: "Jim C. Nasby" To: Greg Stark Cc: Alex Adriaanse , pgsql-performance@postgresql.org Subject: Re: Bad row estimates Message-ID: <20060304154605.GC80721@pervasive.com> References: <4408E91C.90104@innovacomputing.com> <87wtfasng0.fsf@stark.xeocode.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <87wtfasng0.fsf@stark.xeocode.com> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060304:gsstark@mit.edu::y2TyZ+Z5TLA1gEOk:000V87 X-Hashcash: 1:20:060304:alex@innovacomputing.com::mhtySMQYjSE/M9Bu:0000000000000 00000000000000000000000093ym X-Hashcash: 1:20:060304:pgsql-performance@postgresql.org::ex9wm2xL/8A6lj2t:00000 0000000000000000000000006HGS X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.109 required=5 tests=[AWL=0.109] X-Spam-Score: 0.109 X-Spam-Level: X-Archive-Number: 200603/25 X-Sequence-Number: 17462 On Sat, Mar 04, 2006 at 02:01:35AM -0500, Greg Stark wrote: > Alex Adriaanse writes: > > > SELECT count(*) FROM test_table_1 > > INNER JOIN test_table_2 ON > > (test_table_2.s_id = 13300613 AND test_table_1.id = test_table_2.n_id) > > WHERE now() BETWEEN test_table_1.start_ts AND test_table_1.end_ts > > AND test_table_1.id = test_table_1.g_id; Something else that helps in cases like this is to place both an upper and lower boundary on one (or both) fields if possible. For example, if you know that start_ts and end_ts will always be within 1 hour of each other, adding the following will help: AND start_ts >= now()-'1 hour'::interval AND end_ts <= now()+'1 hour'::interval -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461