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