From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
cc: Gerardo Herzig <gherzig@fmed.uba.ar>, pgsql-performance@postgresql.org
Subject: Re: select with max functions
In-reply-to: 
 <CA+t6e1nbJvJkw9vb5qwF2=8ORMnFk8PVb57jKoEwxF212ov4mw@mail.gmail.com>
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>
Comments: In-reply-to Mariel Cherkassky <mariel.cherkassky@gmail.com>
	message dated "Mon, 02 Oct 2017 17:45:36 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <16483.1506958199.1@sss.pgh.pa.us>
Date: Mon, 02 Oct 2017 11:29:59 -0400
Message-ID: <16484.1506958199@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


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