Re: Inoptimal query plan for max() and multicolumn index

Le 20/06/2011 18:08, Vladimir Kulev a écrit :
>
> Yes, exactly :)

SQL Server does it but PG does not. Expect this for the future....

So try to rewrite the query like this :

select max(timestamp) from sms where number = '5502712'
UNIUON ALL,
select max(timestamp) from sms where number = '5802693'
UNION ALL
select max(timestamp) from sms where number = '5801981'

To see what happen to the query plan !

A +

>
> On Mon, Jun 20, 2011 at 7:41 PM, Kevin Grittner
> <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> I expect you're hoping for a plan similar to what this gives you?:
>>
>> explain analyze select greatest(
>> (select max(timestamp) from sms where number = '5502712'),
>> (select max(timestamp) from sms where number = '5802693'),
>> (select max(timestamp) from sms where number = '5801981'));
>

--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************