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 4630F9DCD15 for ; Sat, 4 Mar 2006 04:15:11 -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 85273-01 for ; Sat, 4 Mar 2006 04:15:11 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from stark.xeocode.com (stark.xeocode.com [216.58.44.227]) by postgresql.org (Postfix) with ESMTP id 741D89DCD4E for ; Sat, 4 Mar 2006 04:15:04 -0400 (AST) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1FFRup-00042y-00; Sat, 04 Mar 2006 03:15:03 -0500 To: Greg Stark Cc: Alex Adriaanse , pgsql-performance@postgresql.org Subject: Re: Bad row estimates References: <4408E91C.90104@innovacomputing.com> <87wtfasng0.fsf@stark.xeocode.com> In-Reply-To: <87wtfasng0.fsf@stark.xeocode.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 04 Mar 2006 03:15:02 -0500 Message-ID: <87r75isk1l.fsf@stark.xeocode.com> Lines: 16 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.129 required=5 tests=[AWL=0.129] X-Spam-Score: 0.129 X-Spam-Level: X-Archive-Number: 200603/20 X-Sequence-Number: 17457 Greg Stark writes: > The "now() BETWEEN test_table_1.start_ts AND test_table_1.end_ts can't be > answered completely using a btree index. You could try using a GIST index here > but I'm not clear how much it would help you (or how much work it would be). To add to my own comment you could also try creating two separate indexes on start_ts and end_ts. Postgres can combine the two indexes using a bitmap scan. It's not a complete solution like a GIST index would be though. It also doesn't help at all with the planner estimating how many records will actually match. -- greg