John Papandriopoulos <dr.jpap@xxxxxxxxx> writes: > I set up my schema using a machine generated SQL file [1] that simply > creates a table > create table ptest ( id integer ); > and N = 0..4095 inherited children > create table ptest_N ( > check ( (id >= N_min) and (id <= N_max) ) > ) inherits (ptest); > that split the desired id::integer range into N buckets, one for each of > the N partitions. > I then immediately run a query-plan using EXPLAIN that exhibits the > described behavior: super-fast plan for a SELECT statement, without > swapping, and memory intensive (swapping) plans for DELETE and UPDATE. [ pokes at that for a bit ... ] Ah, I had forgotten that UPDATE/DELETE go through inheritance_planner() while SELECT doesn't. And inheritance_planner() makes a copy of the querytree, including the already-expanded range table, for each target relation. So the memory usage is O(N^2) in the number of child tables. It's difficult to do much better than that in the general case where the children might have different rowtypes from the parent: you need a distinct targetlist for each target relation. I expect that we can be a lot smarter when we have true partitioning support (which among other things is going to have to enforce that all the children have identical column sets). But the inheritance mechanism was never intended to scale to anything like this number of children. I remain of the opinion that you're using far too many child tables. Please note the statement at the bottom of http://www.postgresql.org/docs/9.0/static/ddl-partitioning.html: Partitioning using these techniques will work well with up to perhaps a hundred partitions; don't try to use many thousands of partitions. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
