public inbox for [email protected]
help / color / mirror / Atom feedFrom: Mariel Cherkassky <[email protected]>
To: Gerardo Herzig <[email protected]>
Cc: [email protected]
Subject: Re: select with max functions
Date: Mon, 2 Oct 2017 17:45:36 +0300
Message-ID: <CA+t6e1nbJvJkw9vb5qwF2=8ORMnFk8PVb57jKoEwxF212ov4mw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CA+t6e1mVtJveyoRRW8fLzY0tJhXntLrYxpSrk0=dDH8q93VPEA@mail.gmail.com>
<[email protected]>
<CA+t6e1=9if3_zd734XkBFY7xiS_OosE2t9sSK3JgPav_E9RgNA@mail.gmail.com>
<[email protected]>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-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 <[email protected]>:
>
>
> ----- Mensaje original -----
> > De: "Mariel Cherkassky" <[email protected]>
> > Para: "Andreas Kretschmer" <[email protected]>
> > CC: [email protected]
> > 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 <[email protected]>:
>
> Do a "set statement_timeout TO 0" prior to "explain analyze"
>
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: select with max functions
In-Reply-To: <CA+t6e1nbJvJkw9vb5qwF2=8ORMnFk8PVb57jKoEwxF212ov4mw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox