(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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
