Re: [PERFORM] select with max functions - Mailing list pgsql-performance
| From | Mariel Cherkassky |
|---|---|
| Subject | Re: [PERFORM] select with max functions |
| Date | |
| Msg-id | CA+t6e1nbJvJkw9vb5qwF2=8ORMnFk8PVb57jKoEwxF212ov4mw@mail.gmail.com Whole thread Raw |
| In response to | Re: [PERFORM] select with max functions (Gerardo Herzig <gherzig@fmed.uba.ar>) |
| Responses |
Re: [PERFORM] select with max functions
|
| List | pgsql-performance |
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;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
----------------------------------------
GroupAggregate (cost=0.56..2430770384.80 rows=128137 width=18) (actual time=55.823..2970443.757 rows=1213 loops=1)
Group Key: ma.user_id
Filter: (count(*) > 1)
Rows Removed by Filter: 3693020
-> Index Scan using manuim_i_user_id on manuim ma (cost=0.56..2430767766.00 rows=178324 width=10) (actual time=0.249
..2966355.734 rows=3695461 loops=1)
Filter: (bb_open_date = (SubPlan 1))
Rows Removed by Filter: 31969367
SubPlan 1
-> Limit (cost=68.00..68.00 rows=1 width=8) (actual time=0.082..0.082 rows=0 loops=35664828)
-> Sort (cost=68.00..68.04 rows=16 width=8) (actual time=0.081..0.081 rows=0 loops=35664828)
Sort Key: man.bb_open_date DESC
Sort Method: quicksort Memory: 25kB
-> Index Scan using manuim_i_user_id on manuim man (cost=0.56..67.92 rows=16 width=8) (actual ti
me=0.001..0.069 rows=85 loops=35664828)
Index Cond: ((user_id)::text = (ma.user_id)::text)
Planning time: 0.414 ms
Execution time: 2970444.732 ms
(16 rows)
2017-10-02 16:45 GMT+03:00 Gerardo Herzig <gherzig@fmed.uba.ar>:
----- Mensaje original -----> De: "Mariel Cherkassky" <mariel.cherkassky@gmail.com>> Para: "Andreas Kretschmer" <andreas@a-kretschmer.de>> Enviados: Lunes, 2 de Octubre 2017 10:25:19> Asunto: Re: [PERFORM] select with max functions>> Andreas I tried to rewrite it with the function rank() but I failed. The> query you wrote isnt the same as what I search. Moreover, I cant use> explain analyze because it is taking to much time to run and I'm getting> timeout..>> 2017-10-01 21:48 GMT+03:00 Andreas Kretschmer <andreas@a-kretschmer.de>:Do a "set statement_timeout TO 0" prior to "explain analyze"
pgsql-performance by date: