From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Steve <cheetah@tanabi.org>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Strangely Variable Query Performance
Date: Thu, 12 Apr 2007 17:14:37 -0500
Message-ID: <1176416077.13754.26.camel@state.g2switchworks.com> (raw)
In-Reply-To: <Pine.GSO.4.64.0704121804000.17955@kittyhawk.tanabi.org>
References: <57271CC2-BCB1-4ACA-A177-4FF64F1BC33F@brandeis.edu>
<E1HbzJr-0005VG-GC@elasmtp-curtail.atl.sa.earthlink.net>
<16E9A17C-ADFA-43B2-B7EA-5C7A4D7E96D1@event-s.net>
<E1Hc0lc-0001iE-La@elasmtp-banded.atl.sa.earthlink.net>
<0914C05F-BA1B-4A6C-A1B1-52256BC851A4@event-s.net>
<1176396964.13754.16.camel@state.g2switchworks.com>
<Pine.LNX.4.64.0704121000570.32187@glacier.frostconsultingllc.com>
<Pine.GSO.4.64.0704121653030.17955@kittyhawk.tanabi.org>
<1176414724.13754.23.camel@state.g2switchworks.com>
<Pine.GSO.4.64.0704121804000.17955@kittyhawk.tanabi.org>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: smarlowe@g2switchworks.com, cheetah@tanabi.org
Subject: Re: Strangely Variable Query Performance
In-Reply-To: <1176416077.13754.26.camel@state.g2switchworks.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox