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

Re: Partitions and the optimizer.

From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
To: Jayadevan M <Jayadevan(dot)Maymala(at)ibsplc(dot)com>
Cc: Mladen Gogala <mgogala(at)vmsinfo(dot)com>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>, "pgsql-novice-owner(at)postgresql(dot)org" <pgsql-novice-owner(at)postgresql(dot)org>
Subject: Re: Partitions and the optimizer.
Date: 2010-08-31 03:54:36
Message-ID: 4C7C7CFC.4060707@vmsinfo.com (view raw or flat)
Thread:
Lists: pgsql-novice
Jayadevan M wrote:
>
> I am a PostgreSQL novice myself. So please try my suggestions only if you 
> have enough time to experiment. 
>   

Good ideas can come from anybody. Being a novice is legal in almost all 
states. Not sure about AZ, though.

> 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 
>   
Yes, this does improve performance:
news=> explain
select max(doc) from (
select max("document#") as doc from moreover_documents_y2010m08
union all
select max("document#") as doc from moreover_documents_y2010m07) as dc;
 QUER
Y 
PLAN                                                                          

 
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--
 Aggregate  (cost=0.34..0.35 rows=1 width=8)
   ->  Append  (cost=0.15..0.34 rows=2 width=8)
         ->  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_y2010
m08 on moreover_documents_y2010m08  (cost=0.00..2169871.61 rows=14615132 
width=8
)
                             Filter: ("document#" IS NOT NULL)
         ->  Result  (cost=0.15..0.16 rows=1 width=0)
               InitPlan 2 (returns $1)
                 ->  Limit  (cost=0.00..0.15 rows=1 width=8)
                       ->  Index Scan Backward using 
pk_moreover_documents_y2010
m07 on moreover_documents_y2010m07  (cost=0.00..2419975.91 rows=16294327 
width=8
)
                             Filter: ("document#" IS NOT NULL)
(12 rows)

The point of my post is that the optimizer should be doing that, not me. 
I recently saw a presentation about the Postgres optimizer which begun 
with: "don't assume that you're smarter than the optimizer". Well, I 
apparently an smarter than the optimizer and I am not even particularly 
smart.


-- 
Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com 


In response to

pgsql-novice by date

Next:From: A. KretschmerDate: 2010-08-31 06:28:31
Subject: Re: Running totals
Previous:From: Jayadevan MDate: 2010-08-31 03:35:32
Subject: Re: Partitions and the optimizer.

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