To: "Tyrrill, Ed" <tyrrill_ed@emc.com>
cc: pgsql-performance@postgresql.org
Subject: Re: 
In-reply-to: <A23190A408F7094FAF446C1538222F76041E1C28@avaexch01.avamar.com> 
References: <A23190A408F7094FAF446C1538222F76041E1C28@avaexch01.avamar.com>
Comments: In-reply-to "Tyrrill, Ed" <tyrrill_ed@emc.com>
	message dated "Mon, 25 Jun 2007 14:28:32 -0700"
Date: Mon, 25 Jun 2007 18:10:15 -0400
Message-ID: <7008.1182809415@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Tyrrill, Ed" <tyrrill_ed@emc.com> writes:
> ... With 8.2.4.1 I get the same plan and performance with
> default_statistics_target set to either 10 or 100:

There's something fishy about that, because AFAICS from the CVS logs,
there are no relevant planner changes between 8.2.3 and 8.2.4.  You
should have gotten exactly the same behavior with both.  Maybe the
version difference you think you see is due to noise in ANALYZE's
random sampling --- are the plan choices stable if you repeat ANALYZE
several times at the same statistics target?

I'm also noticing some rather large variation in what ought to be
essentially the same seqscan cost:

>          ->  Seq Scan on backup_location  (cost=0.00..3520915.44
> rows=215090944 width=8) (actual time=0.048..333944.886 rows=215090786
> loops=1)

>          ->  Seq Scan on backup_location  (cost=0.00..3520915.44
> rows=215090944 width=8) (actual time=17.905..790499.303 rows=215090786
> loops=1)

>          ->  Seq Scan on backup_location  (cost=0.00..3520915.44
> rows=215090944 width=8) (actual time=7.110..246561.900 rows=215090786
> loops=1)

Got any idea what's up with that --- heavy background activity maybe,
or partially cached table data?  It's pretty tough to blame the plan for
a 3x variation in the cost of reading data.

Also, what do you have work_mem set to?  Have you changed any of the
planner cost parameters from their defaults?

			regards, tom lane