pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestions on plan with INSERT/SELECT on partitioned table
2+ messages / 2 participants
[nested] [flat]
* Questions on plan with INSERT/SELECT on partitioned table
@ 2010-02-12 16:03 Connors, Bill <BConnors@rochgrp.com>
2010-02-12 18:20 ` Re: Questions on plan with INSERT/SELECT on partitioned table Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Connors, Bill @ 2010-02-12 16:03 UTC (permalink / raw)
To: pgsql-performance
I have been trying to track down a performance issue we've been having with a INSERT INTO ... SELECT query run against a partitioned table on postgres. The problem appears to be in the plan building of the query and after some further research I think I have nailed down a simplified example of the problem. Attached is a simple script that will build an example of our table structure load 2 records and run the explain that produces the plan in question. The query plan looks like the following:
QUERY PLAN
------------------------------------------------------------------
--------------------------
Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false
Nested Loop (cost=23.50..47.08 rows=4 width=1036)
-> Append (cost=0.00..23.50 rows=2 width=520)
-> Seq Scan on base (cost=0.00..11.75 rows=1 width=520)
Filter: (id = 1)
-> Seq Scan on base_1 base (cost=0.00..11.75 rows=1 width=520)
Filter: (id = 1)
-> Materialize (cost=23.50..23.52 rows=2 width=520)
-> Append (cost=0.00..23.50 rows=2 width=520)
-> Seq Scan on another (cost=0.00..11.75 rows=1 width=520)
Filter: (id = 1)
-> Seq Scan on another_1 another (cost=0.00..11.75 rows=1 width=520)
Filter: (id = 1)
Result (cost=23.50..47.08 rows=1 width=1036)
One-Time Filter: false
-> Nested Loop (cost=23.50..47.08 rows=1 width=1036)
-> Append (cost=0.00..23.50 rows=2 width=520)
-> Seq Scan on base (cost=0.00..11.75 rows=1 width=520)
Filter: (id = 1)
-> Seq Scan on base_1 base (cost=0.00..11.75 rows=1 width=520)
Filter: (id = 1)
-> Materialize (cost=23.50..23.52 rows=2 width=520)
-> Append (cost=0.00..23.50 rows=2 width=520)
-> Seq Scan on another (cost=0.00..11.75 rows=1 width=520)
Filter: (id = 1)
-> Seq Scan on another_1 another (cost=0.00..11.75 rows=1 width=520)
Filter: (id = 1)
Result (cost=23.50..47.08 rows=1 width=1036)
One-Time Filter: false
-> Nested Loop (cost=23.50..47.08 rows=1 width=1036)
-> Append (cost=0.00..23.50 rows=2 width=520)
-> Seq Scan on base (cost=0.00..11.75 rows=1 width=520)
Filter: (id = 1)
-> Seq Scan on base_1 base (cost=0.00..11.75 rows=1 width=520)
Filter: (id = 1)
-> Materialize (cost=23.50..23.52 rows=2 width=520)
-> Append (cost=0.00..23.50 rows=2 width=520)
-> Seq Scan on another (cost=0.00..11.75 rows=1 width=520)
Filter: (id = 1)
-> Seq Scan on another_1 another (cost=0.00..11.75 rows=1 width=520)
Filter: (id = 1)
(45 rows)
The problem appears to be the multiple Result sections. I don't understand why this is happening but I do know that a new results section occurs for each new partition you add. The result is that in my actual system where we have a couple hundred partitions this query takes minutes to plan. I've tried this on a Dell (24 core 2.66 GHz) with 192 GB of RAM running postgres 8.3.7 and an IBM 570 (16 core 1.6 Ghz Power 5) with 16 GB of RAM running postgres 8.4.2 both running RedHat Enterprise 5.0 and both take what I would consider way to long to generate the plan.
The 8.3.7 version has constraint exclusion on and the 8.4.2 version has constraint exclusion partial.
Attachments:
test.sql (application/octet-stream, 1.9K) download
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Questions on plan with INSERT/SELECT on partitioned table
2010-02-12 16:03 Questions on plan with INSERT/SELECT on partitioned table Connors, Bill <BConnors@rochgrp.com>
@ 2010-02-12 18:20 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2010-02-12 18:20 UTC (permalink / raw)
To: Connors, Bill <BConnors@rochgrp.com>; +Cc: pgsql-performance
"Connors, Bill" <BConnors@rochgrp.com> writes:
> ... in my actual system where we have a couple hundred partitions this
> query takes minutes to plan.
Please note what the documentation says under "Partitioning Caveats".
The current partitioning support is not meant to scale past a few dozen
partitions. So the solution to your problem is to not have so many
partitions.
There are plans to make some fundamental changes in partitioning
support, and one of the main reasons for that is to allow it to scale to
larger numbers of partitions. This is just in the arm-waving stage
though ...
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-02-12 18:20 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-12 16:03 Questions on plan with INSERT/SELECT on partitioned table Connors, Bill <BConnors@rochgrp.com>
2010-02-12 18:20 ` Tom Lane <tgl@sss.pgh.pa.us>
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