From: | Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | select with max functions |
Date: | 2017-10-01 12:41:37 |
Message-ID: | CA+t6e1mVtJveyoRRW8fLzY0tJhXntLrYxpSrk0=dDH8q93VPEA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Gerardo Herzig | 2017-10-01 13:35:47 | Re: select with max functions |
Previous Message | milist ujang | 2017-10-01 01:36:15 | Re: BDR, wal sender, high system cpu, mutex_lock_common |