[PERFORM] overestimate on empty table - Mailing list 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.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