Re: Partitions and the optimizer.

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