Re: [GENERAL] simple query with radically different plan after 9.0 - 9.2 upgrade
On Tue, Nov 12, 2013 at 2:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: That's right, we store 90 days and roll up data older than that into a different table. Ah-hah. The default statistics target is 100, so indeed ANALYZE is going to be able to fit every date entry in the table into the most-common-values list. In this situation, you'd rather that there were some uncertainty left. Given that the distribution of the date column is (I assume) pretty level, you don't really need full information about this column. I'd try backing off the stats target for the date column (and only the date column --- see ALTER TABLE SET STATISTICS) to 50 or even less. That was it! I set it to 50 on all the 90-days tables and now we no longer see that regular increase in disk reads between midnight of the new day and the 1:37am re-analyze. Thanks!
Re: [GENERAL] simple query with radically different plan after 9.0 - 9.2 upgrade
Kevin Goess kgo...@bepress.com writes:
We noticed a big change after upgrading from 9.0 to 9.2. For
*yesterday's*date, the query plan is fine, like you'd expect
articles_1= explain (analyze, buffers) select 1
from hits_user_daily_count
where userid = 1590185 and date = '2013-07-30';
QUERY PLAN
--
Index Only Scan using hits_user_daily_count_pkey on
hits_user_daily_count (cost=0.00..8.02 rows=1 width=0) (actual
time=0.02
Index Cond: ((userid = 1590185) AND (date = '2013-07-30'::date))
Heap Fetches: 1
Buffers: shared hit=5
Total runtime: 0.044 ms
but for *today's* date it looks like it's reading all the rows for that
date, 15,277 buffers:
articles_1= explain (analyze, buffers) select 1
from hits_user_daily_count
where userid = 1590185 and date = '2013-08-01';
QUERY PLAN
--
Index Scan using hits_user_daily_count_date on hits_user_daily_count
(cost=0.00..7.92 rows=1 width=0) (actual time=11.957..1
Index Cond: (date = '2013-08-01'::date)
Filter: (userid = 1590185)
Rows Removed by Filter: 20149
Buffers: shared hit=15277
Total runtime: 17.924 ms
Hm. I can reproduce this fairly easily, per attached script --- but for
me, every PG release back to 8.4 does the same thing, so I'm a bit
mystified as to why it changed for you between 9.0 and 9.2.
The issue as I'm seeing it is that if ANALYZE didn't find any rows with
today's date, the planner will estimate the condition date = 'today'::date
as having zero selectivity, which makes an indexscan using just that
condition look as cheap as an indexscan using both columns. In fact,
cheaper, because the index on just date is smaller than the pkey index.
So it goes for what looks like the cheaper plan (notice the cost estimates
in your examples above).
Now, the only way to get to a zero selectivity estimate for var = const
is if the planner believes that the pg_stats most-common-values list
for the column is complete, and the constant is nowhere in the list.
So one plausible explanation for the change in behavior is that you
jacked up the statistics target for the date column enough so that
it includes all of the date values you keep in that column. Am I right
in guessing that you drop old data from this table? How far back?
We've addressed the problem by running 'analyze' on the table every day ate
about 1:30am. Buffer hits on that table go from about 1,000/sec to
70,000/sec between midnight and that analyze job, and then go back down to
1,000/sec and stay flat until midnight rolls around again.
Yeah, as soon as ANALYZE sees a few rows with the newer date, the
selectivity estimate will move up enough to discourage use of the
single-column index.
regards, tom lane
drop table hits_user_daily_count;
create table hits_user_daily_count (
userid integer not null,
date date not null,
num_hits integer default 0,
num_cover_page_hitsinteger default 0,
num_additional_files_hits integer default 0,
primary key (userid, date));
create index hits_user_daily_count_date on hits_user_daily_count(date);
create or replace function fill_for_date(d date, n int) returns void
language plpgsql as $$
begin
insert into hits_user_daily_count
select uid, d, random()*10, random()*10 from
generate_series(1,n) uid order by random();
end
$$;
select fill_for_date('today'::date - 5, 10);
select fill_for_date('today'::date - 4, 10);
select fill_for_date('today'::date - 3, 10);
select fill_for_date('today'::date - 2, 10);
select fill_for_date('today'::date - 1, 10);
analyze hits_user_daily_count;
-- If you include this step, the query for today actually takes a long time;
-- but you risk auto-analyze changing the stats and making the problem go away.
-- select fill_for_date('today'::date - 0, 2);
explain analyze select 1
from hits_user_daily_count
where userid = 15901 and date = 'yesterday'::date;
explain analyze select 1
from hits_user_daily_count
where userid = 15901 and date = 'today'::date;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simple query with radically different plan after 9.0 - 9.2 upgrade
Thanks for the reply!
Your analysis matches everything I see here, so what you say is probably
the case. As to why it changed for us with the 9.0 = 9.2 upgrade, I also
don't know--the change was pretty dramatic though. Since we've compensated
for it, and since you say the current behavior is actually what's expected,
I'm happy.
But since you went to the trouble to reply:
Now, the only way to get to a zero selectivity estimate for var = const
is if the planner believes that the pg_stats most-common-values list
for the column is complete, and the constant is nowhere in the list.
So one plausible explanation for the change in behavior is that you
jacked up the statistics target for the date column enough so that
it includes all of the date values you keep in that column.
I'm not following you there, but I'm not a full-time database guy.
Attached is the pg_stats for that column in case you find that interesting
or helpful.
Am I right
in guessing that you drop old data from this table? How far back?
That's right, we store 90 days and roll up data older than that into a
different table.
--
Kevin M. Goess
Software Engineer
Berkeley Electronic Press
kgo...@bepress.com
510-665-1200 x179
www.bepress.com
bepress: sustainable scholarly publishing
# select * from pg_stats where tablename = 'hits_user_daily_count' and attname
= 'date';
-[ RECORD 1
]--+---
schemaname | public
tablename | hits_user_daily_count
attname| date
inherited | f
null_frac | 0
avg_width | 4
n_distinct | 91
most_common_vals |
{2013-11-11,2013-10-21,2013-10-07,2013-10-09,2013-10-03,2013-10-23,2013-11-06,2013-09-16,2013-10-02,2013-10-08,2013-09-23,2013-10-29,2013-10-15,2013-09-30,2013-10-22,2013-11-07,2013-10-28,2013-09-11,2013-11-05,2013-10-16,2013-10-30,2013-10-10,2013-11-04,2013-09-24,2013-09-17,2013-10-14,2013-10-01,2013-10-17,2013-11-08,2013-10-24,2013-09-09,2013-09-19,2013-09-10,2013-09-25,2013-10-04,2013-09-18,2013-10-31,2013-09-04,2013-09-26,2013-10-20,2013-08-29,2013-10-18,2013-08-27,2013-10-13,2013-09-12,2013-08-14,2013-09-13,2013-09-02,2013-10-25,2013-11-03,2013-08-19,2013-09-05,2013-09-27,2013-10-06,2013-10-11,2013-09-15,2013-09-03,2013-09-22,2013-10-27,2013-11-10,2013-08-28,2013-11-01,2013-08-26,2013-09-20,2013-10-19,2013-11-09,2013-10-12,2013-08-15,2013-08-30,2013-08-16,2013-08-25,2013-09-21,2013-09-28,2013-11-02,2013-10-05,2013-08-23,2013-09-08,2013-09-06,2013-09-29,2013-10-26,2013-09-07,2013-09-14,2013-09-01,2013-08-31,2013-08-20,2013-08-17,2013-08-24,2013-08-18,2013-08-22,2013-08-21,2013-11-12}
most_common_freqs |
{0.0144667,0.0137556,0.0136889,0.0135333,0.0134,0.0134,0.013,0.0133111,0.0132667,0.0132,0.0131556,0.0131333,0.0130667,0.013,0.0129778,0.0129333,0.0128889,0.0128,0.0127333,0.0126444,0.0126,0.0125778,0.0125111,0.0124889,0.0123778,0.0123778,0.0122667,0.0122667,0.0122,0.0121556,0.0120889,0.0119778,0.0119111,0.0118444,0.0118,0.0117333,0.0116,0.0114222,0.0114,0.0113556,0.0113111,0.0112889,0.011,0.011,0.0109556,0.0109333,0.0109111,0.0108889,0.0108444,0.0108444,0.0108222,0.0107333,0.0107333,0.0107333,0.0107333,0.0106889,0.0106,0.0105778,0.0105556,0.0105111,0.0104889,0.0104889,0.0104,0.0103778,0.010,0.0102667,0.0102,0.00995556,0.0098,0.00975556,0.0097,0.0097,0.0097,0.0096,0.0096,0.00948889,0.0094,0.0094,0.0092,0.0092,0.0086,0.00848889,0.00846667,0.0083,0.00828889,0.00826667,0.0081,0.0077,0.00717778,0.00497778,0.0018}
histogram_bounds |
correlation| 0.276451
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simple query with radically different plan after 9.0 - 9.2 upgrade
Kevin Goess kgo...@bepress.com writes: Now, the only way to get to a zero selectivity estimate for var = const is if the planner believes that the pg_stats most-common-values list for the column is complete, and the constant is nowhere in the list. So one plausible explanation for the change in behavior is that you jacked up the statistics target for the date column enough so that it includes all of the date values you keep in that column. Am I right in guessing that you drop old data from this table? How far back? That's right, we store 90 days and roll up data older than that into a different table. Ah-hah. The default statistics target is 100, so indeed ANALYZE is going to be able to fit every date entry in the table into the most-common-values list. In this situation, you'd rather that there were some uncertainty left. Given that the distribution of the date column is (I assume) pretty level, you don't really need full information about this column. I'd try backing off the stats target for the date column (and only the date column --- see ALTER TABLE SET STATISTICS) to 50 or even less. Still bemused by the change from 9.0 to 9.2. But there were some small changes in the cost estimation equations for indexscans, so maybe on your real data instead of my toy example the pkey index still managed to look cheaper to 9.0 but not so much to 9.2. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] simple query with radically different plan after 9.0 - 9.2 upgrade
Given this table articles_1= \d hits_user_daily_count; Table public.hits_user_daily_count Column | Type | Modifiers ---+-+--- userid| integer | not null date | date| not null num_hits | integer | default 0 num_cover_page_hits | integer | default 0 num_additional_files_hits | integer | default 0 Indexes: hits_user_daily_count_pkey PRIMARY KEY, btree (userid, date) hits_user_daily_count_date btree (date) whose data looks like this: articles_1= select * from hits_user_daily_count limit 5; userid |date| num_hits | num_cover_page_hits | num_additional_files_hits -++--+-+--- 1590185 | 2013-07-10 |3 | 4 | 0 391907 | 2013-07-10 | 16 | 12 | 0 1739541 | 2013-08-03 |1 | 0 | 0 1798435 | 2013-07-10 |0 | 1 | 0 1521468 | 2013-07-10 |2 | 0 | 0 We noticed a big change after upgrading from 9.0 to 9.2. For *yesterday‘s*date, the query plan is fine, like you’d expect articles_1= explain (analyze, buffers) select 1 from hits_user_daily_count where userid = 1590185 and date = '2013-07-30'; QUERY PLAN -- Index Only Scan using hits_user_daily_count_pkey on hits_user_daily_count (cost=0.00..8.02 rows=1 width=0) (actual time=0.02 Index Cond: ((userid = 1590185) AND (date = '2013-07-30'::date)) Heap Fetches: 1 Buffers: shared hit=5 Total runtime: 0.044 ms but for *today‘s* date it looks like it’s reading all the rows for that date, 15,277 buffers: articles_1= explain (analyze, buffers) select 1 from hits_user_daily_count where userid = 1590185 and date = '2013-08-01'; QUERY PLAN -- Index Scan using hits_user_daily_count_date on hits_user_daily_count (cost=0.00..7.92 rows=1 width=0) (actual time=11.957..1 Index Cond: (date = '2013-08-01'::date) Filter: (userid = 1590185) Rows Removed by Filter: 20149 Buffers: shared hit=15277 Total runtime: 17.924 ms (The dates in the queries are old because I've had this email in draft for a while, but the behavior is still identical). We‘ve addressed the problem by running ’analyze' on the table every day ate about 1:30am. Buffer hits on that table go from about 1,000/sec to 70,000/sec between midnight and that analyze job, and then go back down to 1,000/sec and stay flat until midnight rolls around again. Before the 9.0 - 9.2 upgrade, the behavior would be flat all day. Any ideas what would be causing that problem?
