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

Re: benchmarking the query planner

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Gregory Stark <stark(at)enterprisedb(dot)com>, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, "jd(at)commandprompt(dot)com" <jd(at)commandprompt(dot)com>, Josh Berkus <josh(at)agliodbs(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: benchmarking the query planner
Date: 2009-01-02 11:17:29
Message-ID: Pine.GSO.4.64.0901020554260.193@westnet.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Thu, 11 Dec 2008, Tom Lane wrote:

> On the whole I think we have some evidence here to say that upping the
> default value of default_stats_target to 100 wouldn't be out of line,
> but 1000 definitely is.  Comments?

Circling back to where this started from now that the discussion has 
slowed down here, the original tuning suggestions Josh threw out were:

> Mixed:
>  default_statistics_target = 100
> DW:
>  default_statistics_target = 400

I think this data suggesting serious quadratic behavior doesn't kick in 
until much higher than original theorized supports increasing the mixed 
number to 100.  And if the database default goes to there I think that's 
even better.  Compared to the current default of 10, that makes for a 4X 
increase in planning time for Robert's original test case, and a 5.8X 
increase in the more difficult array test Tom came up with.

The only question really open in my mind is whether 400 is still 
considered too high for a DW application.  Relative to 100, Tom's numbers 
suggest that further 4X increase ends up turning into as much as a 8X 
increase in planning time.  Given the context of a DW application, where 
bad plans are really expensive, that suggestion of 400 seems confirmed to 
be sane now to me.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

In response to

pgsql-hackers by date

Next:From: Magnus HaganderDate: 2009-01-02 12:21:03
Subject: Overriding Kerberos parameters
Previous:From: Magnus HaganderDate: 2009-01-02 10:13:10
Subject: Re: new libpq SSL connection option

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