From: Gregory Stark <stark@enterprisedb.com>
To: Greg Sabino Mullane <greg@turnstep.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Overhauling GUCS
Date: Thu, 12 Jun 2008 18:25:50 +0100
Message-ID: <87iqwetvj5.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <f64390b17984f84f526285c89f126930@biglumber.com>
References: <f64390b17984f84f526285c89f126930@biglumber.com>
"Greg Sabino Mullane" <greg@turnstep.com> writes:
>> Also, I'd actually assert that "10" seems to be perfectly adequate for
>> the majority of users. That is, the number of users where I've
>> recommended increasing d_s_t for the whole database is smaller than the
>> number where I don't, and of course we never hear from most users at
>> all. So I'm pretty happy recommending "Leave the default. If you
>> encounter problem queries, increase it to 100, and analyse the database.
>
> Really? I'm the opposite: I never leave a client's setting at 10, that's
> just asking for trouble. Making it 100 *after* you encounter problem
> queries is reactive; I prefer being proactive.
Have you ever measured the system speed before and after?
> Nor is a setting of 10 "perfectly adequate":
What percentage of your plans actually change with the larger statistics? How
many for the better? How many were massively improved?
I suspect you're looking at some single-digit percentage slowdown for planning
across the board. In exchange if you a) have simple queries you probably see
none improving. If you b) have moderately complex queries you probably get
some single-digit percentage of them with improvements. And if you c) have
very complex queries you probably have a handful of them which see massive
improvements. Across the internet there are a whole lot more applications of
type (a) than the others...
> Frankly, I'd be shocked if there is any significant difference and all
> compared to the actual query run time.
Well you might start preparing to be shocked. Note that retrieving the
statistics is a query itself so it's not hard for it to be comparable to a
similarly simple query. It's not hard for a simple query using multiple
columns to be using more records of statistics than it is from the actual
data. And things can look much worse if that data is TOASTed and requires
further lookups and/or decompression...
> The orders of magnitude speed up of certain queries when the d_s_t goes
> above 98 is what spawned my original thread proposing a change to 100:
>
> http://markmail.org/message/tun3a3juxlsyjbsw
>
> While it's easy to get bogged down in theory about what things
> d_s_t should measure, the optimal size of buckets, etc., it's still
> a severe performance regression bug that should be fixed, IMO.
It was, three months ago:
http://archives.postgresql.org/pgsql-committers/2008-03/msg00129.php
100 is no longer a magic threshold for LIKE queries (in CVS HEAD)
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!
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-hackers@postgresql.org
Cc: stark@enterprisedb.com, greg@turnstep.com
Subject: Re: Overhauling GUCS
In-Reply-To: <87iqwetvj5.fsf@oxford.xeocode.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