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 7147B9DCB97 for ; Sat, 4 Mar 2006 12:10:06 -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 76385-05 for ; Sat, 4 Mar 2006 12:10:04 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 136279DCADB for ; Sat, 4 Mar 2006 12:10:03 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k24G9xwq014851; Sat, 4 Mar 2006 11:09:59 -0500 (EST) To: Greg Stark cc: Alex Adriaanse , pgsql-performance@postgresql.org Subject: Re: Bad row estimates In-reply-to: <87wtfasng0.fsf@stark.xeocode.com> References: <4408E91C.90104@innovacomputing.com> <87wtfasng0.fsf@stark.xeocode.com> Comments: In-reply-to Greg Stark message dated "04 Mar 2006 02:01:35 -0500" Date: Sat, 04 Mar 2006 11:09:59 -0500 Message-ID: <14850.1141488599@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.112 required=5 tests=[AWL=0.112] X-Spam-Score: 0.112 X-Spam-Level: X-Archive-Number: 200603/26 X-Sequence-Number: 17463 Greg Stark writes: > (I don't think the end_ts in the index is buying you much, despite its > appearance in the Index Cond in the plan.) Well, it saves some trips to the heap, but the indexscan is still going to run from the beginning of the index to start_ts = now(), because btree has no idea that there's any correlation between the two index columns. If you could put some a-priori bound on the interval width, you could add a WHERE constraint "AND now() - max_width <= start_ts", which'd constrain the index scan and possibly also get you a better planner estimate. Otherwise I think you really need a special datatype for time intervals and a GIST or r-tree index on it :-(. regards, tom lane