From: | "Mladen Gogala" <mladen(dot)gogala(at)vmsinfo(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #5652: Optimizer does wrong thing with partitioned tables |
Date: | 2010-09-10 13:01:58 |
Message-ID: | 201009101301.o8AD1wBb094807@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 5652
Logged by: Mladen Gogala
Email address: mladen(dot)gogala(at)vmsinfo(dot)com
PostgreSQL version: 8.4.4
Operating system: Red Hat Linux 5.5, 64b
Description: Optimizer does wrong thing with partitioned tables
Details:
Optimizer chooses to scan each partitioned table sequentially, instead of
using the available index:
news=# explain select max(created_at) from moreover_documents;
QUERY PLAN
----------------------------------------------------------------------------
----
---------------------------------------
Aggregate (cost=5115432.65..5115432.66 rows=1 width=8)
-> Append (cost=0.00..5017318.72 rows=39245572 width=8)
-> Seq Scan on moreover_documents (cost=0.00..10.20 rows=20
width=8)
-> Seq Scan on moreover_documents_y2010m06 moreover_documents
(cost=0
.00..236550.85 rows=1859585 width=8)
-> Seq Scan on moreover_documents_y2010m07 moreover_documents
(cost=0
.00..2073604.38 rows=16276938 width=8)
-> Seq Scan on moreover_documents_y2010m08 moreover_documents
(cost=0
.00..2022494.13 rows=15670513 width=8)
-> Seq Scan on moreover_documents_y2010m09 moreover_documents
(cost=0
.00..684618.36 rows=5438436 width=8)
-> Seq Scan on moreover_documents_y2010m10 moreover_documents
(cost=0
.00..10.20 rows=20 width=8)
-> Seq Scan on moreover_documents_y2010m11 moreover_documents
(cost=0
.00..10.20 rows=20 width=8)
-> Seq Scan on moreover_documents_y2010m12 moreover_documents
(cost=0
.00..10.20 rows=20 width=8)
-> Seq Scan on moreover_documents_y2011m01 moreover_documents
(cost=0
.00..10.20 rows=20 width=8)
When on single partition, the optimizer does the right thing:
news=# explain select max(created_at) from moreover_documents_y2010m09;
QUERY PLAN
----------------------------------------------------------------------------
----
------------------------------------------------------------
Result (cost=0.15..0.16 rows=1 width=0)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.15 rows=1 width=8)
-> Index Scan Backward using mdocs_created_y2010m09 on
moreover_docu
ments_y2010m09 (cost=0.00..800757.60 rows=5438436 width=8)
Filter: (created_at IS NOT NULL)
(5 rows)
Index is available on each and every partition. There is only one way to
calculate the max for the entire partitioned table: to calculate max for
each partition and then select max of those. Optimizer should take into
account the available access paths for each partition, yet it doesn't do so.
From | Date | Subject | |
---|---|---|---|
Next Message | Euler Taveira de Oliveira | 2010-09-10 13:31:38 | Re: BUG #5652: Optimizer does wrong thing with partitioned tables |
Previous Message | Bruce Momjian | 2010-09-10 12:45:46 | Re: [BUGS] BUG #5305: Postgres service stops when closing Windows session |