Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT

От: Tom Lane
Тема: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT
Дата: ,
Msg-id: 13159.1291502400@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos)
Ответы: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Mladen Gogala)
Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos)
Список: pgsql-performance

Скрыть дерево обсуждения

Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
 Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Tom Lane, )
  Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
   Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Tom Lane, )
    Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
     Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Tom Lane, )
      Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Mladen Gogala, )
       Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Jochen Erwied, )
        Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
       Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
      Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
       Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Tom Lane, )
        Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Tom Lane, )
         Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
          Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Tom Lane, )
           Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (John Papandriopoulos, )
    Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT  (Mladen Gogala, )

John Papandriopoulos <> 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


В списке pgsql-performance по дате сообщения:

От: Jochen Erwied
Дата:
Сообщение: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT
От: John Papandriopoulos
Дата:
Сообщение: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT