pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedquery planner not using the correct index
13+ messages / 4 participants
[nested] [flat]
* query planner not using the correct index
@ 2008-08-06 21:35 Joshua Shanks <jjshanks@gmail.com>
2008-08-07 02:47 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 1 reply; 13+ messages in thread
From: Joshua Shanks @ 2008-08-06 21:35 UTC (permalink / raw)
To: pgsql-performance
This query is run on a test system just after a backup of the database
has been restored and it does exactly what I expect it to do
EXPLAIN ANALYZE SELECT foos.* FROM foos INNER JOIN bars ON foos.id =
bars.foos_id WHERE ((bars.bars_id = 12345)) ORDER BY attr1 LIMIT 3
OFFSET 0;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=12946.83..12946.83 rows=3 width=1175) (actual
time=0.123..0.131 rows=1 loops=1)
-> Sort (cost=12946.83..12950.83 rows=1602 width=1175) (actual
time=0.116..0.119 rows=1 loops=1)
Sort Key: foos.attr1
-> Nested Loop (cost=28.69..12035.56 rows=1602 width=1175)
(actual time=0.071..0.086 rows=1 loops=1)
-> Bitmap Heap Scan on bars (cost=28.69..2059.66
rows=1602 width=4) (actual time=0.036..0.039 rows=1 loops=1)
Recheck Cond: (bars_id = 12345)
-> Bitmap Index Scan on index_bars_on_bars_id
(cost=0.00..28.29 rows=1602 width=0) (actual time=0.024..0.024 rows=1
loops=1)
Index Cond: (bars_id = 12345)
-> Index Scan using foos_pkey on foos (cost=0.00..6.21
rows=1 width=1175) (actual time=0.017..0.021 rows=1 loops=1)
Index Cond: (foos.id = bars.foos_id)
Total runtime: 0.350 ms
This query is run on a production system and is using foos_1attr1
which is an index on attr1 which is a string.
EXPLAIN ANALYZE SELECT foos.* FROM foos INNER JOIN bars ON foos.id =
bars.foos_id WHERE ((bars.bars_id = 12345)) ORDER BY attr1 LIMIT 3
OFFSET 0;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..2847.31 rows=3 width=332) (actual
time=6175.515..6414.599 rows=1 loops=1)
-> Nested Loop (cost=0.00..287578.30 rows=303 width=332) (actual
time=6175.510..6414.591 rows=1 loops=1)
-> Index Scan using foos_1attr1 on foos
(cost=0.00..128038.65 rows=1602 width=332) (actual
time=0.182..2451.923 rows=2498 loops=1)
-> Index Scan using bars_1ix on bars (cost=0.00..0.37
rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=421939)
Index Cond: (foos.id = bars.foos_id)
Filter: (bars_id = 12345)
Total runtime: 6414.804 ms
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: query planner not using the correct index
2008-08-06 21:35 query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
@ 2008-08-07 02:47 ` Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 03:14 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Craig Ringer @ 2008-08-07 02:47 UTC (permalink / raw)
To: Joshua Shanks <jjshanks@gmail.com>; +Cc: pgsql-performance
Joshua Shanks wrote:
> This query is run on a test system just after a backup of the database
> has been restored and it does exactly what I expect it to do
[snip]
Obvious questions:
- Have you changed the random page cost on either installation?
- Have both installations had VACUUM ANALYZE run recently?
- Are the stats targets the same on both installations?
- Do both installations have similar shared buffers, total available
RAM info, etc?
--
Craig Ringer
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: query planner not using the correct index
2008-08-06 21:35 query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 02:47 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
@ 2008-08-07 03:14 ` Joshua Shanks <jjshanks@gmail.com>
2008-08-07 03:24 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 1 reply; 13+ messages in thread
From: Joshua Shanks @ 2008-08-07 03:14 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: pgsql-performance
> - Have you changed the random page cost on either installation?
This is whatever the default is for both boxes (commented config file says 4.0)
> - Have both installations had VACUUM ANALYZE run recently?
This is the first thing I did and didn't seem to do anything.
Oddly enough I just went and did a VACUUM ANALYZE on a newly restored
db on the test server and get the same query plan as production so I
am now guessing something with the stats from ANALYZE are making
postgres think the string index is the best bet but is clearly 1000's
of times slower.
> - Are the stats targets the same on both installations?
If you mean default_statistics_target that is also the default
(commented config file says 10)
> - Do both installations have similar shared buffers, total available RAM info, etc?
The boxes have different configs as the test box isn't as big as the
production on so it doesn't have as much resources available or
allocated to it.
I did run the query on the backup db box (exact same hardware and
configuration as the production box) which gets restored from a backup
periodically (how I populated the test db) and got the same results as
the test box.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: query planner not using the correct index
2008-08-06 21:35 query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 02:47 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 03:14 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
@ 2008-08-07 03:24 ` Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 15:11 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Craig Ringer @ 2008-08-07 03:24 UTC (permalink / raw)
To: Joshua Shanks <jjshanks@gmail.com>; +Cc: pgsql-performance
Joshua Shanks wrote:
>> - Have you changed the random page cost on either installation?
>
> This is whatever the default is for both boxes (commented config file says 4.0)
>
>> - Have both installations had VACUUM ANALYZE run recently?
>
> This is the first thing I did and didn't seem to do anything.
>
> Oddly enough I just went and did a VACUUM ANALYZE on a newly restored
> db on the test server and get the same query plan as production so I
> am now guessing something with the stats from ANALYZE are making
> postgres think the string index is the best bet but is clearly 1000's
> of times slower.
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: query planner not using the correct index
2008-08-06 21:35 query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 02:47 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 03:14 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 03:24 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
@ 2008-08-07 15:11 ` Joshua Shanks <jjshanks@gmail.com>
2008-08-07 21:12 ` Re: query planner not using the correct index Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Joshua Shanks @ 2008-08-07 15:11 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: pgsql-performance
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}
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: query planner not using the correct index
2008-08-06 21:35 query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 02:47 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 03:14 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 03:24 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 15:11 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
@ 2008-08-07 21:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-08-07 22:01 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2008-08-07 21:12 UTC (permalink / raw)
To: Joshua Shanks <jjshanks@gmail.com>; +Cc: Craig Ringer <craig@postnewspapers.com.au>; pgsql-performance
"Joshua Shanks" <jjshanks@gmail.com> writes:
> 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.
Well, you haven't told us how big any of these tables are, so it's
hard to tell if the n_distinct value is wrong or not ... but in
any case I don't think that the stats on attr1 have anything to do
with your problem. The reason that the "fast" query is fast is that
it benefits from the fact that there's only one bars row with
bars_id = 12345. So the question is how many such rows does the
planner now think there are (try "explain analyze select * from bars
where bars_id = 12345"), and if it's badly wrong, then you need to be
looking at the stats on bars.bars_id to find out why.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: query planner not using the correct index
2008-08-06 21:35 query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 02:47 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 03:14 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 03:24 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 15:11 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 21:12 ` Re: query planner not using the correct index Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-08-07 22:01 ` Joshua Shanks <jjshanks@gmail.com>
2008-08-07 22:38 ` Re: query planner not using the correct index Gregory Stark <stark@enterprisedb.com>
2008-08-07 23:48 ` Re: query planner not using the correct index Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 13+ messages in thread
From: Joshua Shanks @ 2008-08-07 22:01 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Craig Ringer <craig@postnewspapers.com.au>; pgsql-performance
On Thu, Aug 7, 2008 at 4:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Well, you haven't told us how big any of these tables are, so it's
> hard to tell if the n_distinct value is wrong or not ... but in
> any case I don't think that the stats on attr1 have anything to do
> with your problem. The reason that the "fast" query is fast is that
> it benefits from the fact that there's only one bars row with
> bars_id = 12345. So the question is how many such rows does the
> planner now think there are (try "explain analyze select * from bars
> where bars_id = 12345"), and if it's badly wrong, then you need to be
> looking at the stats on bars.bars_id to find out why.
>
> regards, tom lane
>
foo is 400,000+ rows
bar is 300,000+ rows
I was just about to write back about this as with all my tinkering
today I figured that to be the root cause.
SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
pg_stats WHERE tablename = 'bars' AND attname='bars_id';
null_frac | n_distinct | most_common_vals | most_common_freqs
-----------+------------+----------------------+---------------------------
0 | 14 | {145823,47063,24895} | {0.484667,0.257333,0.242}
Those 3 values in reality and in the stats account for 98% of the
rows. actual distinct values are around 350
That plus the information information on
http://www.postgresql.org/docs/8.3/static/indexes-ordering.html make
it all make sense as to why the query planner is doing what it is
doing.
The only problem is we rarely if ever call the query with the where
clause containing those values. I did some testing and the planner
works awesome if we were to call those values but 99.9% of the time we
are calling other values.
It seems like the planner would want to get the result set from
bars.bars_id condition and if it is big using the index on the join to
avoid the separate sorting, but if it is small (0-5 rows which is our
normal case) use the primary key index to join and then just quickly
sort. Is there any reason the planner doesn't do this?
I found a way to run the query as a subselect which is fast for our
normal case but doesn't work for the edge cases so I might just have
to do count on the bars_id and then pick a query based on that.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: query planner not using the correct index
2008-08-06 21:35 query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 02:47 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 03:14 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 03:24 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 15:11 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 21:12 ` Re: query planner not using the correct index Tom Lane <tgl@sss.pgh.pa.us>
2008-08-07 22:01 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
@ 2008-08-07 22:38 ` Gregory Stark <stark@enterprisedb.com>
2008-08-07 23:04 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Gregory Stark @ 2008-08-07 22:38 UTC (permalink / raw)
To: Joshua Shanks <jjshanks@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Craig Ringer <craig@postnewspapers.com.au>; pgsql-performance
"Joshua Shanks" <jjshanks@gmail.com> writes:
> Those 3 values in reality and in the stats account for 98% of the
> rows. actual distinct values are around 350
Measuring n_distinct from a sample is inherently difficult and unreliable.
When 98% of your table falls into those categories it's leaving very few
chances for the sample to find many other distinct values.
I haven't seen the whole thread, if you haven't tried already you could try
raising the statistics target for these columns -- that's usually necessary
anyways when you have a very skewed distribution like this.
> It seems like the planner would want to get the result set from
> bars.bars_id condition and if it is big using the index on the join to
> avoid the separate sorting, but if it is small (0-5 rows which is our
> normal case) use the primary key index to join and then just quickly
> sort. Is there any reason the planner doesn't do this?
Yeah, Heikki's suggested having a kind of "branch" plan node that knows how
where the break-point is between two plans and can call the appropriate one.
We don't have anything like that yet.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: query planner not using the correct index
2008-08-06 21:35 query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 02:47 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 03:14 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 03:24 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 15:11 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 21:12 ` Re: query planner not using the correct index Tom Lane <tgl@sss.pgh.pa.us>
2008-08-07 22:01 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 22:38 ` Re: query planner not using the correct index Gregory Stark <stark@enterprisedb.com>
@ 2008-08-07 23:04 ` Joshua Shanks <jjshanks@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Joshua Shanks @ 2008-08-07 23:04 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Craig Ringer <craig@postnewspapers.com.au>; pgsql-performance
On Thu, Aug 7, 2008 at 5:38 PM, Gregory Stark <stark@enterprisedb.com> wrote:
> Measuring n_distinct from a sample is inherently difficult and unreliable.
> When 98% of your table falls into those categories it's leaving very few
> chances for the sample to find many other distinct values.
>
> I haven't seen the whole thread, if you haven't tried already you could try
> raising the statistics target for these columns -- that's usually necessary
> anyways when you have a very skewed distribution like this.
>
I did some tweaking on default_statistics_target earlier in the thread
with no luck. I just retried it with default_statistics_target set to
500 and did the VACUUM ANALYZE on the other table this time and
started to see better results and more of the behavior I would expect.
Is there a way to set the stats target for just one column? That seems
like what we might need to do.
> Yeah, Heikki's suggested having a kind of "branch" plan node that knows how
> where the break-point is between two plans and can call the appropriate one.
> We don't have anything like that yet.
>
Is this already on a todo list or is there a bug for it?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: query planner not using the correct index
2008-08-06 21:35 query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 02:47 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 03:14 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 03:24 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 15:11 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 21:12 ` Re: query planner not using the correct index Tom Lane <tgl@sss.pgh.pa.us>
2008-08-07 22:01 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
@ 2008-08-07 23:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-08-07 23:55 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Tom Lane @ 2008-08-07 23:48 UTC (permalink / raw)
To: Joshua Shanks <jjshanks@gmail.com>; +Cc: Craig Ringer <craig@postnewspapers.com.au>; pgsql-performance
"Joshua Shanks" <jjshanks@gmail.com> writes:
> SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
> pg_stats WHERE tablename = 'bars' AND attname='bars_id';
> null_frac | n_distinct | most_common_vals | most_common_freqs
> -----------+------------+----------------------+---------------------------
> 0 | 14 | {145823,47063,24895} | {0.484667,0.257333,0.242}
> Those 3 values in reality and in the stats account for 98% of the
> rows. actual distinct values are around 350
So you need to increase the stats target for this column. With those
numbers the planner is going to assume that any value that's not one
of the big three appears about (1 - (0.484667+0.257333+0.242)) / 11
of the time, or several hundred times in 300K rows. If n_distinct were
up around 350 it would be estimating just a dozen or so occurrences,
which should push the join plan into the shape you want. It's likely
that it won't bother to include any more entries in most_common_vals
no matter how much you raise the target; but a larger sample should
definitely give it a better clue about n_distinct.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: query planner not using the correct index
2008-08-06 21:35 query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 02:47 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 03:14 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 03:24 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 15:11 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 21:12 ` Re: query planner not using the correct index Tom Lane <tgl@sss.pgh.pa.us>
2008-08-07 22:01 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 23:48 ` Re: query planner not using the correct index Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-08-07 23:55 ` Joshua Shanks <jjshanks@gmail.com>
2008-08-08 00:11 ` Re: query planner not using the correct index Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Joshua Shanks @ 2008-08-07 23:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Craig Ringer <craig@postnewspapers.com.au>; pgsql-performance
Yeah with default_statistics_target at 500 most_common_vals had 4
values with the fourth having a frequency of 1.5% and distinct have
250+ in it.
How do I increase the stats target for just one column?
On Thu, Aug 7, 2008 at 6:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Joshua Shanks" <jjshanks@gmail.com> writes:
>> SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
>> pg_stats WHERE tablename = 'bars' AND attname='bars_id';
>> null_frac | n_distinct | most_common_vals | most_common_freqs
>> -----------+------------+----------------------+---------------------------
>> 0 | 14 | {145823,47063,24895} | {0.484667,0.257333,0.242}
>
>> Those 3 values in reality and in the stats account for 98% of the
>> rows. actual distinct values are around 350
>
> So you need to increase the stats target for this column. With those
> numbers the planner is going to assume that any value that's not one
> of the big three appears about (1 - (0.484667+0.257333+0.242)) / 11
> of the time, or several hundred times in 300K rows. If n_distinct were
> up around 350 it would be estimating just a dozen or so occurrences,
> which should push the join plan into the shape you want. It's likely
> that it won't bother to include any more entries in most_common_vals
> no matter how much you raise the target; but a larger sample should
> definitely give it a better clue about n_distinct.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: query planner not using the correct index
2008-08-06 21:35 query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 02:47 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 03:14 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 03:24 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 15:11 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 21:12 ` Re: query planner not using the correct index Tom Lane <tgl@sss.pgh.pa.us>
2008-08-07 22:01 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 23:48 ` Re: query planner not using the correct index Tom Lane <tgl@sss.pgh.pa.us>
2008-08-07 23:55 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
@ 2008-08-08 00:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-08-08 14:28 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2008-08-08 00:11 UTC (permalink / raw)
To: Joshua Shanks <jjshanks@gmail.com>; +Cc: Craig Ringer <craig@postnewspapers.com.au>; pgsql-performance
"Joshua Shanks" <jjshanks@gmail.com> writes:
> How do I increase the stats target for just one column?
Look under ALTER TABLE.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: query planner not using the correct index
2008-08-06 21:35 query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 02:47 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 03:14 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 03:24 ` Re: query planner not using the correct index Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 15:11 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 21:12 ` Re: query planner not using the correct index Tom Lane <tgl@sss.pgh.pa.us>
2008-08-07 22:01 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 23:48 ` Re: query planner not using the correct index Tom Lane <tgl@sss.pgh.pa.us>
2008-08-07 23:55 ` Re: query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-08 00:11 ` Re: query planner not using the correct index Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-08-08 14:28 ` Joshua Shanks <jjshanks@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Joshua Shanks @ 2008-08-08 14:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Craig Ringer <craig@postnewspapers.com.au>; pgsql-performance
Just for closure I ended up doing
ALTER TABLE bars ALTER COLUMN bars_id SET STATISTICS 500;
On Thu, Aug 7, 2008 at 7:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Joshua Shanks" <jjshanks@gmail.com> writes:
>> How do I increase the stats target for just one column?
>
> Look under ALTER TABLE.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2008-08-08 14:28 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-08-06 21:35 query planner not using the correct index Joshua Shanks <jjshanks@gmail.com>
2008-08-07 02:47 ` Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 03:14 ` Joshua Shanks <jjshanks@gmail.com>
2008-08-07 03:24 ` Craig Ringer <craig@postnewspapers.com.au>
2008-08-07 15:11 ` Joshua Shanks <jjshanks@gmail.com>
2008-08-07 21:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-08-07 22:01 ` Joshua Shanks <jjshanks@gmail.com>
2008-08-07 22:38 ` Gregory Stark <stark@enterprisedb.com>
2008-08-07 23:04 ` Joshua Shanks <jjshanks@gmail.com>
2008-08-07 23:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-08-07 23:55 ` Joshua Shanks <jjshanks@gmail.com>
2008-08-08 00:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-08-08 14:28 ` Joshua Shanks <jjshanks@gmail.com>
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