From: Tom Lane <tgl@sss.pgh.pa.us>
To: Greg Stark <gsstark@mit.edu>
Cc: josh@agliodbs.com, "Stacy White" <harsh@computer.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Partitioned table performance
Date: Wed, 15 Dec 2004 20:30:08 -0500
Message-ID: <18421.1103160608@sss.pgh.pa.us> (raw)
In-Reply-To: <87oegvqrst.fsf@stark.xeocode.com>
References: <000a01c4da74$84e072e0$0200a8c0@grownups>
<200412102152.40442.josh@agliodbs.com>
<001f01c4e26c$99719680$0200a8c0@grownups>
<200412151025.02262.josh@agliodbs.com>
<87oegvqrst.fsf@stark.xeocode.com>
Greg Stark <gsstark@mit.edu> writes:
> But I'm a bit puzzled. Why would Append have any significant cost? It's just
> taking the tuples from one plan node and returning them until they run out,
> then taking the tuples from another plan node. It should have no i/o cost and
> hardly any cpu cost. Where is the time going?
As best I can tell by profiling, the cost of the Append node per se is
indeed negligible --- no more than a couple percent of the runtime in
CVS tip for a test case similar to Stacy White's example.
It looks bad in EXPLAIN ANALYZE, but you have to realize that passing
the tuples up through the Append node doubles the instrumentation
overhead of EXPLAIN ANALYZE, which is pretty sizable already. (If you
turn on \timing in psql and try the query itself vs. EXPLAIN ANALYZE,
the actual elapsed time is about double, at least for me.)
The other effect, which I hadn't expected, is that the seqscans
themselves actually slow down. I get
regression=# explain analyze SELECT COUNT(*), MAX(bar1) FROM super_foo ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=16414.32..16414.32 rows=1 width=4) (actual time=32313.980..32313.988 rows=1 loops=1)
-> Append (cost=0.00..13631.54 rows=556555 width=4) (actual time=0.232..21848.401 rows=524289 loops=1)
-> Seq Scan on super_foo (cost=0.00..0.00 rows=1 width=4) (actual time=0.020..0.020 rows=0 loops=1)
-> Seq Scan on sub_foo1 super_foo (cost=0.00..6815.77 rows=278277 width=4) (actual time=0.187..6926.395 rows=262144 loops=1)
-> Seq Scan on sub_foo2 super_foo (cost=0.00..6815.77 rows=278277 width=4) (actual time=0.168..7026.953 rows=262145 loops=1)
Total runtime: 32314.993 ms
(6 rows)
regression=# explain analyze SELECT COUNT(*), MAX(bar1) FROM sub_foo1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=8207.16..8207.16 rows=1 width=4) (actual time=9850.420..9850.428 rows=1 loops=1)
-> Seq Scan on sub_foo1 (cost=0.00..6815.77 rows=278277 width=4) (actual time=0.202..4642.401 rows=262144 loops=1)
Total runtime: 9851.423 ms
(3 rows)
Notice the actual times for the sub_foo1 seqscans. That increase (when
counted for both input tables) almost exactly accounts for the
difference in non-EXPLAIN ANALYZE runtime.
After digging around, I find that the reason for the difference is that
the optimization to avoid a projection step (ExecProject) isn't applied
for scans of inheritance unions:
/*
* Can't do it with inheritance cases either (mainly because Append
* doesn't project).
*/
if (rel->reloptkind != RELOPT_BASEREL)
return false;
So if you were to try the example in a pre-7.4 PG, which didn't have
that optimization, you'd probably find that the speeds were just about
the same. (I'm too lazy to verify this though.)
I looked briefly at what it would take to cover this case, and decided
that it's a nontrivial change, so it's too late to do something about it
for 8.0. I think it's probably possible to fix it though, at least for
cases where the child tables have rowtypes identical to the parent.
regards, tom lane
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: tgl@sss.pgh.pa.us, gsstark@mit.edu, harsh@computer.org
Subject: Re: Partitioned table performance
In-Reply-To: <18421.1103160608@sss.pgh.pa.us>
* 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