MIME-Version: 1.0
From: Mariel Cherkassky <mariel.cherkassky@gmail.com>
Date: Sun, 1 Oct 2017 15:41:37 +0300
Message-ID: 
 <CA+t6e1mVtJveyoRRW8fLzY0tJhXntLrYxpSrk0=dDH8q93VPEA@mail.gmail.com>
Subject: select with max functions
To: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary="001a114b2d903bcdbf055a7b945b"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--001a114b2d903bcdbf055a7b945b
Content-Type: text/plain; charset="UTF-8"

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.

--001a114b2d903bcdbf055a7b945b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: base64

PGRpdiBkaXI9InJ0bCI+PGRpdiBkaXI9Imx0ciI+SGksPC9kaXY+PGRpdiBkaXI9Imx0ciI+SSBu
ZWVkIHRvIHVzZSB0aGUgbWF4IGZ1bmN0aW9uIGluIG15IHF1ZXJ5LiBJIGhhZCB2ZXJ5IGJhZCBw
ZXJmb3JtYW5jZSB3aGVuIEkgdXNlZCB0aGUgbWF4wqAgOsKgPC9kaXY+PGRpdiBkaXI9Imx0ciI+
PGJyPjwvZGl2PjxkaXYgZGlyPSJsdHIiPjxkaXYgZGlyPSJsdHIiPsKgIMKgIMKgIMKgIMKgIMKg
IMKgIMKgU0VMRUNUIE1hLlVzZXJfSWQsPC9kaXY+PGRpdiBkaXI9Imx0ciI+wqAgwqAgwqAgwqAg
wqAgwqAgwqAgwqAgwqAgwqAgwqAgQ09VTlQoKikgQ09VTlQ8L2Rpdj48ZGl2IGRpcj0ibHRyIj7C
oCDCoCDCoCDCoCDCoCDCoCDCoCDCoEZST03CoCDCoE1hbnVpbSBNYTwvZGl2PjxkaXYgZGlyPSJs
dHIiPsKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgV0hFUkXCoCBNYS5CYl9PcGVuX0RhdGXCoCA9wqA8
L2Rpdj48ZGl2IGRpcj0ibHRyIj7CoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDC
oCDCoCDCoCDCoCDCoCAoU0VMRUNUIG1heChCYl9PcGVuX0RhdGUpPC9kaXY+PGRpdiBkaXI9Imx0
ciI+wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqBG
Uk9NwqAgwqBNYW51aW0gTWFuPC9kaXY+PGRpdiBkaXI9Imx0ciI+wqAgwqAgwqAgwqAgwqAgwqAg
wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqBXSEVSRcKgIE1hbi5Vc2VyX0lkID0g
TWEuVXNlcl9JZDwvZGl2PjxkaXYgZGlyPSJsdHIiPsKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKg
IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgICk8L2Rpdj48ZGl2IGRpcj0ibHRyIj7CoCDCoCDCoCDC
oCDCoCDCoCDCoCDCoEdST1VQwqAgQlkgTWEuVXNlcl9JZDwvZGl2PjxkaXYgZGlyPSJsdHIiPsKg
IMKgIMKgIMKgIMKgIMKgIMKgIMKgSEFWSU5HIENPVU5UKCopICZndDsgMTs8L2Rpdj48L2Rpdj48
ZGl2IGRpcj0ibHRyIj48ZGl2Pjxicj48L2Rpdj48ZGl2Pjxicj48L2Rpdj48ZGl2PjxkaXY+wqAg
wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAg
wqAgwqAgwqAgwqBRVUVSWSBQTEFOwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAg
wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqDCoDwvZGl2PjxkaXY+LS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tPC9kaXY+PGRpdj7CoEdyb3Vw
QWdncmVnYXRlwqAgKGNvc3Q9MC41Ni4uMzI1MDU1NDc4NC4xMyByb3dzPTExNTExMSB3aWR0aD0x
OCk8L2Rpdj48ZGl2PsKgIMKgR3JvdXAgS2V5OiBtYS51c2VyX2lkPC9kaXY+PGRpdj7CoCDCoEZp
bHRlcjogKGNvdW50KCopICZndDsgMSk8L2Rpdj48ZGl2PsKgIMKgLSZndDvCoCBJbmRleCBTY2Fu
IHVzaW5nIG1hbnVpbV9pX3VzZXJfaWQgb24gbWFudWltIG1hwqAgKGNvc3Q9MC41Ni4uMzI1MDU1
MjI5NS41OSByb3dzPTE3ODMyNCB3aWR0aD0xMCk8L2Rpdj48ZGl2PsKgIMKgIMKgIMKgIMKgRmls
dGVyOiAoYmJfb3Blbl9kYXRlID0gKFN1YlBsYW4gMSkpPC9kaXY+PGRpdj7CoCDCoCDCoCDCoCDC
oFN1YlBsYW4gMTwvZGl2PjxkaXY+wqAgwqAgwqAgwqAgwqAgwqAtJmd0O8KgIEFnZ3JlZ2F0ZcKg
IChjb3N0PTkwLjk4Li45MC45OSByb3dzPTEgd2lkdGg9OCk8L2Rpdj48ZGl2PsKgIMKgIMKgIMKg
IMKgIMKgIMKgIMKgIMKgLSZndDvCoCBJbmRleCBTY2FuIHVzaW5nIG1hbnVpbV9pX3VzZXJfaWQg
b24gbWFudWltIG1hbsKgIChjb3N0PTAuNTYuLjkwLjkyIHJvd3M9MjIgd2lkdGg9OCk8L2Rpdj48
ZGl2PsKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgSW5kZXggQ29uZDogKCh1c2Vy
X2lkKTo6dGV4dCA9IChtYS51c2VyX2lkKTo6dGV4dCk8L2Rpdj48ZGl2Pig5IHJvd3MpPC9kaXY+
PC9kaXY+PGRpdj48YnI+PC9kaXY+PGRpdj48YnI+PC9kaXY+PGRpdj48YnI+PC9kaXY+PGRpdj5T
byBJIHVzZWQgdGhlIGxpbWl0IDEgb3B0aW9uIDrCoDwvZGl2PjxkaXY+PGJyPjwvZGl2PjxkaXY+
PGRpdj7CoCDCoCDCoCDCoCDCoCDCoCDCoCDCoFNFTEVDVCBNYS5Vc2VyX0lkLDwvZGl2PjxkaXY+
wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgQ09VTlQoKikgQ09VTlQ8L2Rpdj48ZGl2
PsKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgRlJPTcKgIMKgTWFudWltIE1hPC9kaXY+PGRpdj7CoCDC
oCDCoCDCoCDCoCDCoCDCoCDCoFdIRVJFwqAgTWEuQmJfT3Blbl9EYXRlwqAgPcKgPC9kaXY+PGRp
dj7CoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCAoU0VM
RUNUIEJiX09wZW5fRGF0ZTwvZGl2PjxkaXY+wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAg
wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqBGUk9NwqAgwqBNYW51aW0gTWFuPC9kaXY+PGRpdj7CoCDC
oCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoFdIRVJFwqAg
TWFuLlVzZXJfSWQgPSBNYS5Vc2VyX0lkIG9yZGVyIGJ5wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAg
wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqBiYl9vcGVuX2RhdGUgZGVzYyBsaW1pdCAxPC9k
aXY+PGRpdj7CoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDC
oCApPC9kaXY+PGRpdj7CoCDCoCDCoCDCoCDCoCDCoCDCoCDCoEdST1VQwqAgQlkgTWEuVXNlcl9J
ZDwvZGl2PjxkaXY+wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqBIQVZJTkcgQ09VTlQoKikgJmd0OyAx
OzwvZGl2PjwvZGl2PjxkaXY+PGJyPjwvZGl2PjxkaXY+YW5kIHRoZSBwZXJmb3JtYW5jZSBhcmUg
c3RpbGwgdGhlIHNhbWUgOsKgPC9kaXY+PGRpdj48YnI+PC9kaXY+PGRpdj48ZGl2PsKgIMKgIMKg
IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKg
IMKgIMKgIMKgIFFVRVJZIFBMQU7CoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDC
oCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoDwvZGl2PjxkaXY+LS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tPC9kaXY+PGRp
dj7CoEdyb3VwQWdncmVnYXRlwqAgKGNvc3Q9MC41Ni4uMzI1MjI0ODg2My40NiByb3dzPTExNTEx
MSB3aWR0aD0xOCk8L2Rpdj48ZGl2PsKgIMKgR3JvdXAgS2V5OiBtYS51c2VyX2lkPC9kaXY+PGRp
dj7CoCDCoEZpbHRlcjogKGNvdW50KCopICZndDsgMSk8L2Rpdj48ZGl2PsKgIMKgLSZndDvCoCBJ
bmRleCBTY2FuIHVzaW5nIG1hbnVpbV9pX3VzZXJfaWQgb24gbWFudWltIG1hwqAgKGNvc3Q9MC41
Ni4uMzI1MjI0NjM3NC45MiByb3dzPTE3ODMyNCB3aWR0aD0xMCk8L2Rpdj48ZGl2PsKgIMKgIMKg
IMKgIMKgRmlsdGVyOiAoYmJfb3Blbl9kYXRlID0gKFN1YlBsYW4gMSkpPC9kaXY+PGRpdj7CoCDC
oCDCoCDCoCDCoFN1YlBsYW4gMTwvZGl2PjxkaXY+wqAgwqAgwqAgwqAgwqAgwqAtJmd0O8KgIExp
bWl0wqAgKGNvc3Q9OTEuMDMuLjkxLjAzIHJvd3M9MSB3aWR0aD04KTwvZGl2PjxkaXY+wqAgwqAg
wqAgwqAgwqAgwqAgwqAgwqAgwqAtJmd0O8KgIFNvcnTCoCAoY29zdD05MS4wMy4uOTEuMDkgcm93
cz0yMiB3aWR0aD04KTwvZGl2PjxkaXY+wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAg
wqBTb3J0IEtleTogbWFuLmJiX29wZW5fZGF0ZSBERVNDPC9kaXY+PGRpdj7CoCDCoCDCoCDCoCDC
oCDCoCDCoCDCoCDCoCDCoCDCoCDCoC0mZ3Q7wqAgSW5kZXggU2NhbiB1c2luZyBtYW51aW1faV91
c2VyX2lkIG9uIG1hbnVpbSBtYW7CoCAoY29zdD0wLjU2Li45MC45MiByb3dzPTIyIHdpZHRoPTgp
PC9kaXY+PGRpdj7CoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoElu
ZGV4IENvbmQ6ICgodXNlcl9pZCk6OnRleHQgPSAobWEudXNlcl9pZCk6OnRleHQpPC9kaXY+PGRp
dj4oMTEgcm93cyk8L2Rpdj48L2Rpdj48ZGl2Pjxicj48L2Rpdj48ZGl2Pjxicj48L2Rpdj48ZGl2
Pjxicj48L2Rpdj48ZGl2PnRoZSByZWFkaW5nIG9uIHRoZSB0YWJsZSBtYW51aW0gdGFrZXMgYSBs
b3Qgb2YgZWZmb3J0LCB3aGF0IGVsc2UgY2FuIEkgZG8gPyB0aGUgdGFibGVgcyBzaXplIGlzIDhH
LsKgPC9kaXY+PGRpdj48YnI+PC9kaXY+PGRpdj48ZGl2PnNlbGVjdCBjb3VudCgqKSBmcm9tIG1h
bnVpbTs8L2Rpdj48ZGl2PsKgIGNvdW50wqAgwqA8L2Rpdj48ZGl2Pi0tLS0tLS0tLS08L2Rpdj48
ZGl2PsKgMzU2NjQ4Mjg8L2Rpdj48ZGl2PigxIHJvdyk8L2Rpdj48L2Rpdj48ZGl2Pjxicj48L2Rp
dj48ZGl2PnRoZSBpbmRleGVzIG9uIHRoZSB0YWJsZSA6wqA8L2Rpdj48ZGl2PsKgJnF1b3Q7bWFu
dWltX2JiX29wZW5fZGF0ZSZxdW90OyBidHJlZSAoYmJfb3Blbl9kYXRlKTxicj48L2Rpdj48ZGl2
PiZxdW90O21hbnVpbV9pX3VzZXJfaWQmcXVvdDsgYnRyZWUgKHVzZXJfaWQpPGJyPjwvZGl2Pjxk
aXY+PGJyPjwvZGl2PjxkaXY+PGJyPjwvZGl2PjxkaXY+QW55IGlkZWEgaG93IGNhbiBJIGNvbnRp
bnVlIGZyb20gaGVyZSA/IFRoYW5rcyAsIE1hcmllbC48L2Rpdj48L2Rpdj48L2Rpdj4NCg==
--001a114b2d903bcdbf055a7b945b--