Skip site navigation (1) Skip section navigation (2)

Re: Strangely Variable Query Performance

From: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>
To: Steve <cheetah(at)tanabi(dot)org>
Cc: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Strangely Variable Query Performance
Date: 2007-04-12 22:14:37
Message-ID: 1176416077.13754.26.camel@state.g2switchworks.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patchespgsql-performance
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.

In response to

Responses

pgsql-performance by date

Next:From: Guido NeitzerDate: 2007-04-12 22:18:14
Subject: Re: Slow Postgresql server
Previous:From: SteveDate: 2007-04-12 22:04:33
Subject: Re: Strangely Variable Query Performance

pgsql-hackers by date

Next:From: Guido NeitzerDate: 2007-04-12 22:18:14
Subject: Re: Slow Postgresql server
Previous:From: SteveDate: 2007-04-12 22:04:33
Subject: Re: Strangely Variable Query Performance

pgsql-patches by date

Next:From: Guido NeitzerDate: 2007-04-12 22:18:14
Subject: Re: Slow Postgresql server
Previous:From: SteveDate: 2007-04-12 22:04:33
Subject: Re: Strangely Variable Query Performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group