(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.04rows=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.273rows=0 loops=1) Group Key: qa.child, qa.parent -> Gather
(cost=293727.20..336790.89rows=54380 width=123) (actual time=2395.261..2395.261 rows=0 loops=1)
WorkersPlanned: 3 Workers Launched: 3 -> Hash Join (cost=292727.20..330352.89
rows=17542width=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=105280width=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=684322width=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.106rows=445582 loops=4) -> Sort (cost=68.49..70.94 rows=980 width=55)
(actualtime=0.031..0.031 rows=0 loops=3) Sort Key: (((qa.parent)::regclass)::oid)
Sort Method: quicksort Memory: 25kB -> Seq Scan
onqueued_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
onpg_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.938rows=0 loops=1) Group Key: qa.child, qa.parent -> Sort
(cost=306089.46..306127.06rows=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.10rows=15038 width=342) (actual time=4.932..4.932 rows=0 loops=1)
WorkersPlanned: 3 Workers Launched: 3 -> Hash Join
(cost=148711.02..302542.30rows=4851 width=342) (actual time=0.139..0.139 rows=0 loops=4)
HashCond: ((((qa.child)::regclass)::oid = colcld.attrelid) AND (colpar.attname = colcld.attname))
Join Filter: (colpar.atttypid <> colcld.atttypid) -> Hash Join
(cost=18.10..125851.98rows=29113 width=342) (actual time=0.137..0.137 rows=0 loops=4)
Hash Cond: (colpar.attrelid = ((qa.parent)::regclass)::oid) -> Parallel Seq Scan
onpg_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=0loops=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=2121397width=72) (never executed) -> Seq Scan on pg_attribute colcld
(cost=0.00..92010.97rows=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