From: F. BROUARD / SQLpro <sqlpro@club-internet.fr>
To: pgsql-performance@postgresql.org
Subject: Re: Inoptimal query plan for max() and multicolumn index
Date: Tue, 21 Jun 2011 12:49:02 +0200
Message-ID: <4E00771E.80403@club-internet.fr> (raw)
In-Reply-To: <BANLkTinWEEyWTO5e0Zq5omAgO=OLZ_FJ7g@mail.gmail.com>
References: <BANLkTimsNMhnAHk6Wz7gARfdkTmz2QKJ7g@mail.gmail.com>
<4DFF23CC020000250003E994@gw.wicourts.gov>
<BANLkTinWEEyWTO5e0Zq5omAgO=OLZ_FJ7g@mail.gmail.com>
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.Grittner@wicourts.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 *************************
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sqlpro@club-internet.fr
Subject: Re: Inoptimal query plan for max() and multicolumn index
In-Reply-To: <4E00771E.80403@club-internet.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox