Re: plan time of MASSIVE partitioning ... - Mailing list pgsql-hackers

Leonardo Francalanci <m_lists@yahoo.it> writes:
> I tried a simple test case:

> create table t (a integer, b text);

> DO $$DECLARE i int;
> BEGIN
>             FOR i IN 0..9000 LOOP
>                 EXECUTE 'create table t' || i || ' ( CHECK (a >' || i*10 || ' 
> and a <= ' || (i+1)*10 || ' ) ) INHERITS (t)';
>    EXECUTE 'create index tidx' || i || ' ON t' || i || '  (a)';
> END LOOP;
> END$$;

> explain select * from t where a > 1060 and a < 1090;

This is going to be dominated by constraint exclusion checking.  There's
basically no fix for that except a more explicit representation of the
partitioning rules.  If the planner has to make 8999 theorem proofs to
remove the 8999 unwanted partitions from the plan, it's gonna take
awhile.
        regards, tom lane