----- Mensaje original ----- > De: "Mariel Cherkassky" <mariel.cherkassky@xxxxxxxxx> > Para: pgsql-performance@xxxxxxxxxxxxxx > Enviados: Domingo, 1 de Octubre 2017 9:41:37 > Asunto: 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
