Skip site navigation (1) Skip section navigation (2)

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

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-04 21:34:28
Message-ID: idec54$2hpd$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-performance
On 12/4/10 8:42 AM, Tom Lane wrote:
> John Papandriopoulos<dr(dot)jpap(at)gmail(dot)com>  writes:
>> I've recreated the same example with just one parent table, and 4096 child tables.
>
>> SELECT query planning is lightning fast as before; DELETE and UPDATE cause my machine to swap.
>
>> What's different about DELETE and UPDATE here?
>
> Hmm.  Rules?  Triggers?  You seem to be assuming the problem is at the
> planner stage but I'm not sure you've proven that.


My example starts off with a new database (e.g. createdb ptest).

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.

There are no triggers, no rules, no plpgsql functions, no indexes and no 
inserted data.


Is there a more simple example that might help me convince you that 
we're exercising just the planner stage?

Kindest,
John

[1] http://jpap.org/files/partition-test-flat.txt


In response to

Responses

pgsql-performance by date

Next:From: Mladen GogalaDate: 2010-12-04 21:58:10
Subject: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT
Previous:From: felixDate: 2010-12-04 19:45:19
Subject: Re: Update problem on large table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group