X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5F2A6D1BB9E for ; Fri, 4 Jun 2004 11:41:54 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 38359-01 for ; Fri, 4 Jun 2004 14:41:52 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id B93B5D1B8C4 for ; Fri, 4 Jun 2004 11:41:51 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i54EfoVl027263; Fri, 4 Jun 2004 10:41:50 -0400 (EDT) To: =?ISO-8859-1?Q?Mikael_Kjellstr=F6m?= Cc: pgsql-performance@postgresql.org Subject: Re: Planner problem In-reply-to: <40C066C1.2090808@mksoft.nu> References: <40C066C1.2090808@mksoft.nu> Comments: In-reply-to =?ISO-8859-1?Q?Mikael_Kjellstr=F6m?= message dated "Fri, 04 Jun 2004 14:10:41 +0200" Date: Fri, 04 Jun 2004 10:41:50 -0400 Message-ID: <27262.1086360110@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200406/32 X-Sequence-Number: 7090 =?ISO-8859-1?Q?Mikael_Kjellstr=F6m?= writes: > I am having a bit of problem with the plan that the planner produces. Actually, your problem is with the row-count estimates. Some of them are pretty wildly off, which inevitably leads to bad plan choices. In particular the price row estimate is off by a factor of 200 in all three plans: > -> Index Scan using priceix2 on price (cost=0.00..3.41 rows=23 width=20) (actual time=0.042..25.811 rows=4402 loops=111) > Index Cond: ('2004-06-01'::date <= validstopdate) > Filter: (('2004-06-01'::date >= validstartdate) AND (deletiondate IS NULL) AND (organizationid = 1)) > -> Index Scan using priceix2 on price (cost=0.00..3.41 rows=23 width=20) (actual time=0.053..26.225 rows=4402 loops=1) > Index Cond: ('2004-06-01'::date <= validstopdate) > Filter: (('2004-06-01'::date >= validstartdate) AND (deletiondate IS NULL) AND (organizationid = 1)) > -> Index Scan using priceix2 on price (cost=0.00..3.41 rows=23 width=20) (actual time=0.050..26.475 rows=4402 loops=1) > Index Cond: ('2004-06-01'::date <= validstopdate) > Filter: (('2004-06-01'::date >= validstartdate) AND (deletiondate IS NULL) AND (organizationid = 1)) and priceavailable is off by a factor of 100: > -> Index Scan using priceavailableix1 on priceavailable pa (cost=0.00..5.91 rows=1 width=16) (actual time=0.067..4.182 rows=111 loops=1) > Index Cond: (pa.locationconnectionid = "outer".locationconnectionid) > Filter: (((direction = 'D'::text) OR (direction = 'B'::text)) AND (deletiondate IS NULL)) > -> Index Scan using priceavailableix1 on priceavailable pa (cost=0.00..141.57 rows=43 width=16) (actual time=0.048..48.739 rows=6525 loops=1) > Filter: (((direction = 'D'::text) OR (direction = 'B'::text)) AND (deletiondate IS NULL)) > -> Index Scan using priceavailableix1 on priceavailable pa (cost=0.00..5.91 rows=1 width=16) (actual time=0.070..0.965 rows=111 loops=1) > Index Cond: (pa.locationconnectionid = "outer".locationconnectionid) > Filter: (((direction = 'D'::text) OR (direction = 'B'::text)) AND (deletiondate IS NULL)) Are you sure you've vacuum analyzed these two tables recently? If so, what may be needed is to increase ANALYZE's statistics target for the columns used in the conditions. (See ALTER TABLE SET STATISTICS) I suspect that part of the story here has to do with cross-column correlations, which the present planner will never figure out since it has no cross-column statistics. But it's hard to believe that that's the problem for cases as simple as > Filter: (((direction = 'D'::text) OR (direction = 'B'::text)) AND (deletiondate IS NULL)) regards, tom lane