Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id C522A9FBBE0 for ; Thu, 12 Apr 2007 19:14:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 54539-10 for ; Thu, 12 Apr 2007 19:14:38 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id A842E9FBBBF for ; Thu, 12 Apr 2007 19:14:42 -0300 (ADT) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Thu, 12 Apr 2007 22:14:37 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 12 Apr 2007 17:14:37 -0500 Subject: Re: Strangely Variable Query Performance From: Scott Marlowe To: Steve Cc: PostgreSQL Performance In-Reply-To: References: <57271CC2-BCB1-4ACA-A177-4FF64F1BC33F@brandeis.edu> <16E9A17C-ADFA-43B2-B7EA-5C7A4D7E96D1@event-s.net> <0914C05F-BA1B-4A6C-A1B1-52256BC851A4@event-s.net> <1176396964.13754.16.camel@state.g2switchworks.com> <1176414724.13754.23.camel@state.g2switchworks.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1176416077.13754.26.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Thu, 12 Apr 2007 17:14:37 -0500 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/269 X-Sequence-Number: 24146 On Thu, 2007-04-12 at 17:04, Steve wrote: > >> Seq Scan on detail_summary ds (cost=0.00..1902749.83 rows=9962 width=4) > >> Filter: ((receipt >= '1998-12-30'::date) AND (encounter_id = ANY > >> ('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[]))) > >> (2 rows) > > > > How accurate is the row estimate made by the planner? (explain analyze > > to be sure) > > > > Results: > > Seq Scan on detail_summary ds (cost=0.00..1902749.83 rows=9962 width=4) > (actual time=62871.386..257258.249 rows=112 loops=1) > Filter: ((receipt >= '1998-12-30'::date) AND (encounter_id = ANY > ('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[]))) > Total runtime: 257258.652 ms So there's a misjudgment of the number of rows returned by a factor of about 88. That's pretty big. Since you had the same number without the receipt date (I think...) then it's the encounter_id that's not being counted right. Try upping the stats target on that column and running analyze again and see if you get closer to 112 in your analyze or not.