Subject: Re: select with max functions
To: Tom Lane <tgl@sss.pgh.pa.us>,
 Mariel Cherkassky <mariel.cherkassky@gmail.com>
Cc: Gerardo Herzig <gherzig@fmed.uba.ar>, pgsql-performance@postgresql.org
References: 
 <CA+t6e1mVtJveyoRRW8fLzY0tJhXntLrYxpSrk0=dDH8q93VPEA@mail.gmail.com>
 <0970df65-602e-724d-2c39-e4695ae48bb6@a-kretschmer.de>
 <CA+t6e1=9if3_zd734XkBFY7xiS_OosE2t9sSK3JgPav_E9RgNA@mail.gmail.com>
 <1990398729.67887.1506951911360.JavaMail.zimbra@fmed.uba.ar>
 <CA+t6e1nbJvJkw9vb5qwF2=8ORMnFk8PVb57jKoEwxF212ov4mw@mail.gmail.com>
 <16484.1506958199@sss.pgh.pa.us>
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
Message-ID: <002ad4b4-ec5f-4e9f-fa48-5c19405292ee@catalyst.net.nz>
Date: Wed, 4 Oct 2017 10:16:37 +1300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.3.0
MIME-Version: 1.0
In-Reply-To: <16484.1506958199@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 03/10/17 04:29, Tom Lane wrote:
> Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
>> explain analyze   SELECT Ma.User_Id,
>>                        COUNT(*) COUNT
>>                 FROM   Manuim Ma
>>                 WHERE  Ma.Bb_Open_Date  =
>>                                    (SELECT Bb_Open_Date
>>                                     FROM   Manuim Man
>>                                     WHERE  Man.User_Id = Ma.User_Id order
>> by                                   bb_open_date desc limit 1
>>                                    )
>>                 GROUP  BY Ma.User_Id
>>                 HAVING COUNT(*) > 1;
> The core problem with this query is that the sub-select has to be done
> over again for each row of the outer table, since it's a correlated
> sub-select (ie, it refers to Ma.User_Id from the outer table).  Replacing
> a max() call with handmade logic doesn't do anything to help that.
> I'd try refactoring it so that you calculate the max Bb_Open_Date just
> once for each user id, perhaps along the lines of
>
> SELECT Ma.User_Id,
>         COUNT(*) COUNT
>         FROM   Manuim Ma,
>                (SELECT User_Id, max(Bb_Open_Date) as max
>                 FROM   Manuim Man
>                 GROUP BY User_Id) ss
>         WHERE  Ma.User_Id = ss.User_Id AND
>                Ma.Bb_Open_Date = ss.max
>         GROUP  BY Ma.User_Id
>         HAVING COUNT(*) > 1;
>
> This is still not going to be instantaneous, but it might be better.
>
> It's possible that an index on (User_Id, Bb_Open_Date) would help,
> but I'm not sure.
>
> 			regards, tom lane
>
>

Further ideas based on Tom's rewrite: If that MAX is still expensive it 
might be worth breaking


SELECT User_Id, max(Bb_Open_Date) as max
                FROM   Manuim Man
                GROUP BY User_Id

out into a VIEW, and considering making it MATERIALIZED, or creating an 
equivalent  trigger based summary table (there are examples in the docs 
of how to do this).

Cheers

Mark


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance