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 2410111F6243 for ; Tue, 18 Apr 2006 14:31:55 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.127 X-Spam-Level: X-Spam-Status: No, score=0.127 required=5 tests=[AWL=0.127] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id Ng8BuBrOt+fT for ; Tue, 18 Apr 2006 14:31:50 -0300 (ADT) 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 C171A11F625E for ; Tue, 18 Apr 2006 14:31:50 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k3IHVmcF014986; Tue, 18 Apr 2006 13:31:48 -0400 (EDT) To: Gavin Hamill cc: pgsql-performance@postgresql.org Subject: Re: Slow query - possible bug? In-reply-to: <44449E4C.7040804@laterooms.com> References: <443E3DF5.4020207@laterooms.com> <3c1395330604130546g6b091f75x511955817fe65dcd@mail.gmail.com> <443E4C9D.3090301@laterooms.com> <20329.1144942010@sss.pgh.pa.us> <44449E4C.7040804@laterooms.com> Comments: In-reply-to Gavin Hamill message dated "Tue, 18 Apr 2006 09:07:40 +0100" Date: Tue, 18 Apr 2006 13:31:48 -0400 Message-ID: <14985.1145381508@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200604/390 X-Sequence-Number: 18474 Gavin Hamill writes: > Tom Lane wrote: >> I'm thinking the planner is misestimating something, but it's hard >> to tell what without breaking it down. > (allocation0_."Date" between '2006-06-10 00:00:00.000000' and > '2006-06-10 00:00:00.000000'); > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------- > Index Scan using ix_date on "Allocation" allocation0_ (cost=0.00..4.77 > rows=1 width=34) (actual time=0.035..6706.467 rows=34220 loops=1) > Index Cond: (("Date" >= '2006-06-10'::date) AND ("Date" <= > '2006-06-10'::date)) > Total runtime: 6728.743 ms Bingo, there's our misestimation: estimated 1 row, actual 34220 :-( That's why it's choosing the wrong index: it thinks the condition on RoomID isn't going to reduce the number of rows fetched any further, and so the smaller index ought to be marginally cheaper to use. In reality, it works way better when using the two-column index. I think this is the same problem recently discussed about how the degenerate case for a range comparison is making an unreasonably small estimate, where it probably ought to fall back to some equality estimate instead. With the simple-equality form of the date condition, it does get a reasonable estimate, and so it picks the right index. There should be a fix for this by the time PG 8.2 comes out, but in the meantime you might find that it helps to write the range check in a way that doesn't have identical bounds, eg date >= '2006-06-10'::date AND date < '2006-06-11'::date regards, tom lane