From: John Papandriopoulos <dr.jpap@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT
Date: Sat, 04 Dec 2010 13:34:28 -0800
Message-ID: <idec54$2hpd$1@news.hub.org> (raw)
In-Reply-To: <23792.1291480923@sss.pgh.pa.us>
References: <idbo6g$92r$1@news.hub.org>
<14012.1291443654@sss.pgh.pa.us>
<idd14l$a4u$1@news.hub.org>
<23792.1291480923@sss.pgh.pa.us>
On 12/4/10 8:42 AM, Tom Lane wrote:
> John Papandriopoulos<dr.jpap@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dr.jpap@gmail.com
Subject: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT
In-Reply-To: <idec54$2hpd$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox