From: Joshua Shanks <jjshanks@gmail.com>
To: Craig Ringer <craig@postnewspapers.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: query planner not using the correct index
Date: Thu, 7 Aug 2008 10:11:43 -0500
Message-ID: <84f0acdb0808070811l43a3da9eq240d272f8ddd105c@mail.gmail.com> (raw)
In-Reply-To: <489A6AF5.50908@postnewspapers.com.au>
References: <84f0acdb0808061435q5ebb607cjc311f1eb4da841a@mail.gmail.com>
<489A623A.4090400@postnewspapers.com.au>
<84f0acdb0808062014p29cf7482ied332225a0a92c63@mail.gmail.com>
<489A6AF5.50908@postnewspapers.com.au>
On Wed, Aug 6, 2008 at 10:24 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> OK, that's interesting. There are ways to examine Pg's statistics on
> columns, get an idea of which stats might be less than accurate, etc,
> but I'm not really familiar enough with it all to give you any useful
> advice on the details. I can make one suggestion in the vein of shotgun
> throubleshooting, though:
>
> Try altering the statistics targets on the tables of interest, or tweak
> the default_statistics_target, then rerun VACUUM ANALYZE and re-test.
> Maybe start with a stats target of 100 and see what happens.
>
> --
> Craig Ringer
I tried 100, 500, and 1000 for default_statistics_target. I think
below is the right query to examine the stats. None of the levels of
default_statistics_target I tried changed the query planners behavior.
It seems obvious that the stats on attr1 at the current level are
inaccurate as there are over 100,000 unique enteries in the table. But
even tweaking them to be more accurate doesn't seem to add any
benefit.
default_statistics_target = 10
SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
pg_stats WHERE tablename = 'foos' AND attname='attr1';
null_frac | n_distinct | most_common_vals | most_common_freqs
-----------+------------+------------------+-------------------
0 | 1789 | {""} | {0.625667}
default_statistics_target = 100
SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
pg_stats WHERE tablename = 'foo' AND attname='attr1';
null_frac | n_distinct | most_common_vals | most_common_freqs
-------------+------------+------------------+-------------------
0.000266667 | 17429 | {""} | {0.6223}
default_statistics_target = 500
SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
pg_stats WHERE tablename = 'foo' AND attname='attr1';
null_frac | n_distinct | most_common_vals | most_common_freqs
-------------+------------+------------------+-------------------
0.000293333 | -0.17954 | {""} | {0.62158}
default_statistics_target = 1000
SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
pg_stats WHERE tablename = 'foo' AND attname='attr1';
null_frac | n_distinct | most_common_vals | most_common_freqs
-------------+------------+------------------+-------------------
0.000293333 | -0.304907 | {""} | {0.621043}
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: jjshanks@gmail.com, craig@postnewspapers.com.au
Subject: Re: query planner not using the correct index
In-Reply-To: <84f0acdb0808070811l43a3da9eq240d272f8ddd105c@mail.gmail.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