Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 7D325B5DBDC for ; Tue, 21 Jun 2011 07:49:22 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 01684-10 for ; Tue, 21 Jun 2011 10:49:15 +0000 (UTC) X-Greylist: whitelisted by SQLgrey-1.7.6 Received: from smtp4-g21.free.fr (smtp4-g21.free.fr [212.27.42.4]) by mail.postgresql.org (Postfix) with ESMTP id 90D4BB5DBD3 for ; Tue, 21 Jun 2011 07:49:13 -0300 (ADT) Received: from [192.168.0.17] (unknown [78.237.44.8]) by smtp4-g21.free.fr (Postfix) with ESMTP id D90A14C80DE for ; Tue, 21 Jun 2011 12:49:07 +0200 (CEST) Message-ID: <4E00771E.80403@club-internet.fr> Date: Tue, 21 Jun 2011 12:49:02 +0200 From: "F. BROUARD / SQLpro" Organization: sqlpro.developpez.com User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; fr; rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Inoptimal query plan for max() and multicolumn index References: <4DFF23CC020000250003E994@gw.wicourts.gov> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001, RFC_ABUSE_POST=0.001 X-Spam-Level: X-Archive-Number: 201106/196 X-Sequence-Number: 44129 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 > 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 *************************