public inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedoverestimate on empty table
4+ messages / 2 participants
[nested] [flat]
* overestimate on empty table
@ 2017-11-10 20:40 Justin Pryzby <pryzby@telsasoft.com>
2017-11-10 21:19 ` Re: overestimate on empty table Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Justin Pryzby @ 2017-11-10 20:40 UTC (permalink / raw)
To: pgsql-performance
(or, the opposite of the more common problem)
I wrote this query some time ago to handle "deferred" table-rewriting type
promoting ALTERs of a inheritence children, to avoid worst-case disk usage
altering the whole heirarchy, and also locking the entire heirarchy against
SELECT and INSERT.
ts=# explain analyze SELECT child c, parent p, array_agg(colpar.attname::text) cols, array_agg(colpar.atttypid::regtype) AS types FROM
queued_alters qa JOIN pg_attribute colpar ON qa.parent::regclass=colpar.attrelid JOIN
pg_attribute colcld ON qa.child::regclass=colcld.attrelid WHERE
colcld.attname=colpar.attname AND colpar.atttypid!=colcld.atttypid GROUP BY 1,2
ORDER BY regexp_replace(child, '.*_((([0-9]{4}_[0-9]{2})_[0-9]{2})|(([0-9]{6})([0-9]{2})?))$', '\3\5') DESC, -- by YYYYMM
child~'_[0-9]{6}$' DESC, -- monthly tables first
regexp_replace(child, '.*_', '') DESC -- by YYYYMMDD
LIMIT 1;
Unfortunately we get this terrible plan:
Limit (cost=337497.59..337497.60 rows=1 width=184) (actual time=2395.283..2395.283 rows=0 loops=1)
-> Sort (cost=337497.59..337500.04 rows=980 width=184) (actual time=2395.281..2395.281 rows=0 loops=1)
Sort Key: (regexp_replace((qa.child)::text, '.*_((([0-9]{4}_[0-9]{2})_[0-9]{2})|(([0-9]{6})([0-9]{2})?))$'::text, '\3\5'::text)) DESC, (((qa.child)::text ~ '_[0-9]{6}$'::text)) DESC, (regexp_replace((qa.child)::text, '.*_'::text, ''::text)) DESC
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=337470.64..337492.69 rows=980 width=184) (actual time=2395.273..2395.273 rows=0 loops=1)
Group Key: qa.child, qa.parent
-> Gather (cost=293727.20..336790.89 rows=54380 width=123) (actual time=2395.261..2395.261 rows=0 loops=1)
Workers Planned: 3
Workers Launched: 3
-> Hash Join (cost=292727.20..330352.89 rows=17542 width=123) (actual time=2341.328..2341.328 rows=0 loops=4)
Hash Cond: ((((qa.child)::regclass)::oid = colcld.attrelid) AND (colpar.attname = colcld.attname))
Join Filter: (colpar.atttypid <> colcld.atttypid)
-> Merge Join (cost=144034.27..151009.09 rows=105280 width=123) (actual time=514.820..514.820 rows=0 loops=4)
Merge Cond: (colpar.attrelid = (((qa.parent)::regclass)::oid))
-> Sort (cost=143965.78..145676.59 rows=684322 width=72) (actual time=514.790..514.790 rows=1 loops=4)
Sort Key: colpar.attrelid
Sort Method: external merge Disk: 78448kB
-> Parallel Seq Scan on pg_attribute colpar (cost=0.00..77640.22 rows=684322 width=72) (actual time=0.011..164.106 rows=445582 loops=4)
-> Sort (cost=68.49..70.94 rows=980 width=55) (actual time=0.031..0.031 rows=0 loops=3)
Sort Key: (((qa.parent)::regclass)::oid)
Sort Method: quicksort Memory: 25kB
-> Seq Scan on queued_alters qa (cost=0.00..19.80 rows=980 width=55) (actual time=0.018..0.018 rows=0 loops=3)
-> Hash (cost=92010.97..92010.97 rows=2121397 width=72) (actual time=1786.056..1786.056 rows=1782330 loops=4)
Buckets: 2097152 Batches: 2 Memory Usage: 106870kB
-> Seq Scan on pg_attribute colcld (cost=0.00..92010.97 rows=2121397 width=72) (actual time=0.027..731.554 rows=1782330 loops=4)
As the queued_alters table is typically empty (and autoanalyzed with
relpages=0), I see "why":
./src/backend/optimizer/util/plancat.c
| if (curpages < 10 &&
| rel->rd_rel->relpages == 0 &&
| !rel->rd_rel->relhassubclass &&
| rel->rd_rel->relkind != RELKIND_INDEX)
| curpages = 10;
Indeed it works much better if I add a child table as a test/kludge:
-> Sort (cost=306322.49..306323.16 rows=271 width=403) (actual time=4.945..4.945 rows=0 loops=1)
Sort Key: (regexp_replace((qa.child)::text, '.*_((([0-9]{4}_[0-9]{2})_[0-9]{2})|(([0-9]{6})([0-9]{2})?))$'::text, '\3\5'::text)) DESC, (((qa.child)::text ~ '_[0-9]{6}$'::text)) DESC, (regexp_replace((qa.child)::text, '.*_'::text, ''::text)) DESC
Sort Method: quicksort Memory: 25kB
-> GroupAggregate (cost=306089.46..306321.13 rows=271 width=403) (actual time=4.938..4.938 rows=0 loops=1)
Group Key: qa.child, qa.parent
-> Sort (cost=306089.46..306127.06 rows=15038 width=342) (actual time=4.936..4.936 rows=0 loops=1)
Sort Key: qa.child, qa.parent
Sort Method: quicksort Memory: 25kB
-> Gather (cost=149711.02..305046.10 rows=15038 width=342) (actual time=4.932..4.932 rows=0 loops=1)
Workers Planned: 3
Workers Launched: 3
-> Hash Join (cost=148711.02..302542.30 rows=4851 width=342) (actual time=0.139..0.139 rows=0 loops=4)
Hash Cond: ((((qa.child)::regclass)::oid = colcld.attrelid) AND (colpar.attname = colcld.attname))
Join Filter: (colpar.atttypid <> colcld.atttypid)
-> Hash Join (cost=18.10..125851.98 rows=29113 width=342) (actual time=0.137..0.137 rows=0 loops=4)
Hash Cond: (colpar.attrelid = ((qa.parent)::regclass)::oid)
-> Parallel Seq Scan on pg_attribute colpar (cost=0.00..77640.22 rows=684322 width=72) (actual time=0.005..0.005 rows=1 loops=4)
-> Hash (cost=14.71..14.71 rows=271 width=274) (actual time=0.016..0.016 rows=0 loops=4)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Append (cost=0.00..14.71 rows=271 width=274) (actual time=0.016..0.016 rows=0 loops=4)
-> Seq Scan on queued_alters qa (cost=0.00..2.21 rows=21 width=55) (actual time=0.012..0.012 rows=0 loops=4)
-> Seq Scan on qa2 qa_1 (cost=0.00..12.50 rows=250 width=292) (actual time=0.003..0.003 rows=0 loops=4)
-> Hash (cost=92010.97..92010.97 rows=2121397 width=72) (never executed)
-> Seq Scan on pg_attribute colcld (cost=0.00..92010.97 rows=2121397 width=72) (never executed)
But is there a better way (I don't consider adding a row of junk to be a significant improvement).
Thanks in advance for any suggestion.
Justin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: overestimate on empty table
2017-11-10 20:40 overestimate on empty table Justin Pryzby <pryzby@telsasoft.com>
@ 2017-11-10 21:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-11-11 17:19 ` Re: overestimate on empty table Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2017-11-10 21:19 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance
Justin Pryzby <pryzby@telsasoft.com> writes:
> As the queued_alters table is typically empty (and autoanalyzed with
> relpages=0), I see "why":
> ./src/backend/optimizer/util/plancat.c
> | if (curpages < 10 &&
> | rel->rd_rel->relpages == 0 &&
> | !rel->rd_rel->relhassubclass &&
> | rel->rd_rel->relkind != RELKIND_INDEX)
> | curpages = 10;
So I'm sure you read the comment above that, too.
I'm loath to abandon the principle that the planner should not believe
that tables are empty/tiny without some forcing function. There are
going to be way more people screaming about the plans they get from
too-small rowcount estimates than the reverse. However, maybe we could
do better about detecting whether a vacuum or analyze has really happened.
(Autovacuum won't normally touch a table until a fair number of rows have
been put in it, so if a table is tiny but has been vacuumed, we can
presume that that was a manual action.)
One idea is to say that relpages = reltuples = 0 is only the state that
prevails for a freshly-created table, and that VACUUM or ANALYZE should
always set relpages to at least 1 even if the physical size is zero.
Dunno if that would confuse people. Or we could bite the bullet and
add a "relanalyzed" bool flag to pg_class. It's not like that's going
to be a noticeable percentage increase in the row width ...
> But is there a better way (I don't consider adding a row of junk to be a significant improvement).
Not ATM.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: overestimate on empty table
2017-11-10 20:40 overestimate on empty table Justin Pryzby <pryzby@telsasoft.com>
2017-11-10 21:19 ` Re: overestimate on empty table Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-11-11 17:19 ` Justin Pryzby <pryzby@telsasoft.com>
2017-11-11 17:43 ` Re: overestimate on empty table Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Justin Pryzby @ 2017-11-11 17:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Fri, Nov 10, 2017 at 04:19:41PM -0500, Tom Lane wrote:
> Justin Pryzby <pryzby@telsasoft.com> writes:
> > (or, the opposite of the more common problem)
> > As the queued_alters table is typically empty (and autoanalyzed with
> > relpages=0), I see "why":
>
> > ./src/backend/optimizer/util/plancat.c
> > | if (curpages < 10 &&
> > | rel->rd_rel->relpages == 0 &&
> > | !rel->rd_rel->relhassubclass &&
> > | rel->rd_rel->relkind != RELKIND_INDEX)
> > | curpages = 10;
>
> So I'm sure you read the comment above that, too.
> One idea is to say that relpages = reltuples = 0 is only the state that
> prevails for a freshly-created table, and that VACUUM or ANALYZE should
> always set relpages to at least 1 even if the physical size is zero.
> Dunno if that would confuse people.
What about adding && rel->rd_rel->reltuples==0, and make VACUUM/ANALYZE instead
set only reltuples=1, since that's already done at costsize.c: clamp_row_est()
and therefor no additional confusion?
Justin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: overestimate on empty table
2017-11-10 20:40 overestimate on empty table Justin Pryzby <pryzby@telsasoft.com>
2017-11-10 21:19 ` Re: overestimate on empty table Tom Lane <tgl@sss.pgh.pa.us>
2017-11-11 17:19 ` Re: overestimate on empty table Justin Pryzby <pryzby@telsasoft.com>
@ 2017-11-11 17:43 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2017-11-11 17:43 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance
Justin Pryzby <pryzby@telsasoft.com> writes:
> On Fri, Nov 10, 2017 at 04:19:41PM -0500, Tom Lane wrote:
>> One idea is to say that relpages = reltuples = 0 is only the state that
>> prevails for a freshly-created table, and that VACUUM or ANALYZE should
>> always set relpages to at least 1 even if the physical size is zero.
>> Dunno if that would confuse people.
> What about adding && rel->rd_rel->reltuples==0, and make VACUUM/ANALYZE instead
> set only reltuples=1, since that's already done at costsize.c: clamp_row_est()
> and therefor no additional confusion?
1 tuple in 0 pages is a physically impossible situation, so I'm quite
sure that way *would* confuse people.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-11-11 17:43 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-11-10 20:40 overestimate on empty table Justin Pryzby <pryzby@telsasoft.com>
2017-11-10 21:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-11-11 17:19 ` Justin Pryzby <pryzby@telsasoft.com>
2017-11-11 17:43 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox