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

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

From: "F(dot) BROUARD / SQLpro" <sqlpro(at)club-internet(dot)fr>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Inoptimal query plan for max() and multicolumn index
Date: 2011-06-21 10:49:02
Message-ID: 4E00771E.80403@club-internet.fr (view raw or flat)
Thread:
Lists: pgsql-performance
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 *************************


In response to

pgsql-performance by date

Next:From: Florian WeimerDate: 2011-06-21 11:19:43
Subject: Re: Contemplating SSD Hardware RAID
Previous:From: Yeb HavingaDate: 2011-06-21 07:55:32
Subject: Re: Contemplating SSD Hardware RAID

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