From: | Gerardo Herzig <gherzig(at)fmed(dot)uba(dot)ar> |
---|---|
To: | Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: select with max functions |
Date: | 2017-10-01 13:35:47 |
Message-ID: | 58852558.43813.1506864947581.JavaMail.zimbra@fmed.uba.ar |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
----- Mensaje original -----
> De: "Mariel Cherkassky" <mariel(dot)cherkassky(at)gmail(dot)com>
> Para: pgsql-performance(at)postgresql(dot)org
> Enviados: Domingo, 1 de Octubre 2017 9:41:37
> Asunto: [PERFORM] select with max functions
>
> Hi,
> I need to use the max function in my query. I had very bad performance when
> I used the max :
>
> SELECT Ma.User_Id,
> COUNT(*) COUNT
> FROM Manuim Ma
> WHERE Ma.Bb_Open_Date =
> (SELECT max(Bb_Open_Date)
> FROM Manuim Man
> WHERE Man.User_Id = Ma.User_Id
> )
> GROUP BY Ma.User_Id
> HAVING COUNT(*) > 1;
>
>
> QUERY PLAN
>
> ---------------------------------------------------------------------------------------------------------
> GroupAggregate (cost=0.56..3250554784.13 rows=115111 width=18)
> Group Key: ma.user_id
> Filter: (count(*) > 1)
> -> Index Scan using manuim_i_user_id on manuim ma
> (cost=0.56..3250552295.59 rows=178324 width=10)
> Filter: (bb_open_date = (SubPlan 1))
> SubPlan 1
> -> Aggregate (cost=90.98..90.99 rows=1 width=8)
> -> Index Scan using manuim_i_user_id on manuim man
> (cost=0.56..90.92 rows=22 width=8)
> Index Cond: ((user_id)::text = (ma.user_id)::text)
> (9 rows)
>
>
>
> So I used the limit 1 option :
>
> 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;
>
> and the performance are still the same :
>
> QUERY PLAN
>
> ---------------------------------------------------------------------------------------------------------------
> GroupAggregate (cost=0.56..3252248863.46 rows=115111 width=18)
> Group Key: ma.user_id
> Filter: (count(*) > 1)
> -> Index Scan using manuim_i_user_id on manuim ma
> (cost=0.56..3252246374.92 rows=178324 width=10)
> Filter: (bb_open_date = (SubPlan 1))
> SubPlan 1
> -> Limit (cost=91.03..91.03 rows=1 width=8)
> -> Sort (cost=91.03..91.09 rows=22 width=8)
> Sort Key: man.bb_open_date DESC
> -> Index Scan using manuim_i_user_id on manuim man
> (cost=0.56..90.92 rows=22 width=8)
> Index Cond: ((user_id)::text =
> (ma.user_id)::text)
> (11 rows)
>
>
>
> the reading on the table manuim takes a lot of effort, what else can I do ?
> the table`s size is 8G.
>
> select count(*) from manuim;
> count
> ----------
> 35664828
> (1 row)
>
> the indexes on the table :
> "manuim_bb_open_date" btree (bb_open_date)
> "manuim_i_user_id" btree (user_id)
>
>
> Any idea how can I continue from here ? Thanks , Mariel.
Start by posting the results of "explain analyze" of that queries, so we can see some timming stuff.
Gerardo
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2017-10-01 18:48:45 | Re: select with max functions |
Previous Message | Mariel Cherkassky | 2017-10-01 12:41:37 | select with max functions |