From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> |
Cc: | Gerardo Herzig <gherzig(at)fmed(dot)uba(dot)ar>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: select with max functions |
Date: | 2017-10-02 15:29:59 |
Message-ID: | 16484.1506958199@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Purav Chovatia | 2017-10-03 14:33:16 | Stored Procedure Performance |
Previous Message | Mariel Cherkassky | 2017-10-02 14:45:36 | Re: select with max functions |