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

Re: Partitions and the optimizer.

From: Jayadevan M <Jayadevan(dot)Maymala(at)ibsplc(dot)com>
To: Mladen Gogala <mgogala(at)vmsinfo(dot)com>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>,pgsql-novice-owner(at)postgresql(dot)org
Subject: Re: Partitions and the optimizer.
Date: 2010-08-31 03:35:32
Message-ID: OFDEFF461F.C16B2587-ON65257790.00136ED5-65257790.0013BDA5@ibsplc.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hello,
> Optimizer doesn't behave well when the tables are partitioned. Here is 
> the problem:
> 
> news=# explain
> select max("document#") from moreover_documents_y2010m08;
> 
> 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 pk_moreover_documents_y2010m08 
> on moreover_documents_y2010m08  (cost=0.00..2169871.61 rows=14615132 
> width=8)
>                  Filter: ("document#" IS NOT NULL)
> (5 rows)
> 
> Time: 31.191 ms
> news=# explain
> select max("document#") from moreover_documents;
>                                                       QUERY 
> PLAN 
> 
-----------------------------------------------------------------------------------------------------------------------
>  Aggregate  (cost=4227019.40..4227019.41 rows=1 width=8)
>    ->  Append  (cost=0.00..4145103.32 rows=32766432 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..236523.53 rows=1856853 width=8)
>          ->  Seq Scan on moreover_documents_y2010m07 moreover_documents 
> (cost=0.00..2073778.27 rows=16294327 width=8)
>          ->  Seq Scan on moreover_documents_y2010m08 moreover_documents 
> (cost=0.00..1834740.32 rows=14615132 width=8)
>          ->  Seq Scan on moreover_documents_y2010m09 moreover_documents 
> (cost=0.00..10.20 rows=20 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)
> (11 rows)
> 
> Time: 31.961 ms
> news=#
> 
> I have several partitions for the table, 3 of them are loaded. When I 
> search for the maximum value of the primary key column on a single 
> partition, the access path is as it should be, using the PK index. Each 
> of the partitions has the same PK. When I try selecting the maximum on 
> the entire table, the indexes on the partitions are not used, Postgres 
> does the full scan instead. The difference in timing is drastic:
> news=# select max("document#") from moreover_documents_y2010m08;
>     max 
> ------------
>  1175107508
> (1 row)
> 
> Time: 56.778 ms
> news=# select max("document#") from moreover_documents;
>     max 
> ------------
>  1175107508
> (1 row)
> 
> Time: 200490.228 ms
> news=#
> 
> -- 
I am a PostgreSQL novice myself. So please try my suggestions only if you 
have enough time to experiment. 
If you try 
select max("document#") from (
select "document#" from moreover_documents_y2010m08 
union 
select "document#" from moreover_documents_xxx
) 
would that improve performance?

Regards,
Jayadevan 





DISCLAIMER: 

"The information in this e-mail and any attachment is intended only for 
the person to whom it is addressed and may contain confidential and/or 
privileged material. If you have received this e-mail in error, kindly 
contact the sender and destroy all copies of the original communication. 
IBS makes no warranty, express or implied, nor guarantees the accuracy, 
adequacy or completeness of the information contained in this email or any 
attachment and is not liable for any errors, defects, omissions, viruses 
or for resultant loss or damage, if any, direct or indirect."






In response to

Responses

pgsql-novice by date

Next:From: Mladen GogalaDate: 2010-08-31 03:54:36
Subject: Re: Partitions and the optimizer.
Previous:From: Bastiaan OlijDate: 2010-08-31 01:19:20
Subject: Running totals

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