From: | John Papandriopoulos <dr(dot)jpap(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT |
Date: | 2010-12-05 11:06:39 |
Message-ID: | idfrnv$2bc$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 12/4/10 2:40 PM, Tom Lane wrote:
> [ 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.
Thanks for the pointer to the code and explanation.
In inheritance_planner(...) I see the memcpy of the input query tree, but for my example constraint exclusion would only result in one child being included. Or is the O(N^2) memory usage from elsewhere?
> 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).
Is this the same as saying that the inheritance_planner(...) can be avoided if it were known that the children have the same rowtype as the parent? Is it easy to check?
> But the inheritance mechanism was never intended to scale to anything like
> this number of children.
Unfortunately so. :(
When I push the number of child tables up to 10k, the SELECT planning starts to slow down (~1 sec), though no swapping.
> 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.
Thanks for the reference---I'm well aware of it, but it was not clear to me why: the reason I was structuring my partition inheritance as a tree, because I thought it was simply a case of time-to-scan the CHECK constraints at any level in the inheritance hierarchy. You've been a great help in helping my understanding PostgreSQL inheritance.
Best,
John
From | Date | Subject | |
---|---|---|---|
Next Message | John Papandriopoulos | 2010-12-05 11:10:04 | Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT |
Previous Message | Jochen Erwied | 2010-12-04 23:38:39 | Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT |