DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.ca;
	h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=NMcOX1R0+Txhw/n4THEhixfJ3JX0Sc6MyW5GxkaH9zTdFCSzMyDMEG08qOF/Jd2+0sMb2OmQxRoTrATkDRLH78kD2J5hiV3gEqHRss+2baQTPC9GmypzMP5887awea0Y0G2qUgFho7MIuJdIFqu/GrL0pwi+kArD0nVvbaIR+E0=
	;
Message-ID: <20061019112357.66833.qmail@web55908.mail.re3.yahoo.com>
Date: Thu, 19 Oct 2006 07:23:57 -0400 (EDT)
From: Ioana Danes <ioanasoftware@yahoo.ca>
Subject: Re: Postgresql 8.1.4 - performance issues for select on view using
 max
To: Jeff Davis <pgsql@j-davis.com>, Dimitri Fontaine <dim@dalibo.com>
Cc: pgsql-performance@postgresql.org, Ioana Danes <ioanasoftware@yahoo.ca>
In-Reply-To: <1161207230.31645.266.camel@dogma.v10.wvs>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hello,

Actually what I expected from the planner for this
query (select max(transid) from view) was something
like this :

select max(transid) from (select max(transid) from
archive.transaction union all select max(transid) from
public.transaction)
 
and to apply the max function to each query of the
union. This is what is happening when you use a where
condition, it is using the indexes on each subquery of
the view...
ex: select transid from view where transid = 12;

This way it would be fast enough.

Also for order by and limit I was expecting the same
thing.


Thank you for your time,
Ioana Danes

> constraint exclusion and inheritance won't help him.
> 
> The problem is that he has two indexes, and he needs
> to find the max
> between both of them. PostgreSQL isn't smart enough
> to recognize that it
> can use two indexes, find the max in each one, and
> find the max of those
> two values.
> 
> Regards,
> 	Jeff Davis
> 
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com