Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dz1yY-0007Zi-G0 for pgsql-performance@arkaria.postgresql.org; Mon, 02 Oct 2017 14:45:42 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dz1yY-0005Sq-33 for pgsql-performance@arkaria.postgresql.org; Mon, 02 Oct 2017 14:45:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dz1yX-0005RX-Ls for pgsql-performance@postgresql.org; Mon, 02 Oct 2017 14:45:41 +0000 Received: from mail-wr0-x233.google.com ([2a00:1450:400c:c0c::233]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dz1yU-00031I-45 for pgsql-performance@postgresql.org; Mon, 02 Oct 2017 14:45:41 +0000 Received: by mail-wr0-x233.google.com with SMTP id u5so3979263wrc.5 for ; Mon, 02 Oct 2017 07:45:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=SnATG8pXTRkGSZqcEYoulJGs+XZWXJJZBcV51Qf3m18=; b=cjyDJ3L19ZG04lZKq7dyCY5THVlbxy5N7j1djLLsRef9lnkr9pPrp4oyWYARRg+RzS XY+c+s+MZkwN4g2fIDpl7DiUnRAm6VfELgZotItZJOEnfFEtj6uRXARdFb6xj9N7nENq Mv0/ErqlVqp96BRtZ99k7Lar9erXRjnG1uco95+XjzL6qv+mj3Q8C/k9MY6qHQOes7W6 QopIznyEblI2RB+s3w7IIzDhIg89k623L3YndtuPypKyk0si60v+J5yDxpbwGBgchdim iYOaZRbVERthNZ3SCFBRap3EjUw/JifaN2boWubxQTkp10+Xrmm3XKbTzBKAr3UR6Ii3 c5QQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=SnATG8pXTRkGSZqcEYoulJGs+XZWXJJZBcV51Qf3m18=; b=tZNXQT3MOkEnwzFLLLQN2Y1s2kdm/Hi8oDy8dMN0Gh5NVa+MNiRK7ynkijRiQzfGu8 cMLgw7cL5vKYm8kj8bA0WI93Q9Ec6jPT6NcuxSIxTjXfrph7x3h1LhA2WwzT1T97AOfd tTVW4HcDMNgNIbaD0+Ydt5nWYjV2IpPwX6VGiQyhwk9ehDGcceyOww/n59dTMfFwscd9 gr8KuBTY6eqgd24hGif9hApeEjEC3mgRagCbhkU/FDlebZLCQPjpCutqwvpOdYvOR907 93emxfVOzRyoP8NxRKs+wUSY8iStA8Q7O6J2Jhgju9GM4Qr6joRt4pbu96XIRNKrUvxz e0Xg== X-Gm-Message-State: AHPjjUhXCrDyOwFZs1ZBpYwGTub1nDBFCU/dMRAYDefAHzeU3B/+jZxT kU6FHN3TBJAn5rFP6ZsNMTuxWa0gT5nPRZwDZtqRRDx6 X-Google-Smtp-Source: AOwi7QDEUWv6qkpB1npk8zm6ZjmuqCIARlJhMs7pi+9hIRcKfGzZD9qjCduS9dQc2vzeg1l+0EtnPoxXwisW3H/bKPE= X-Received: by 10.223.179.10 with SMTP id j10mr16643080wrd.273.1506955536926; Mon, 02 Oct 2017 07:45:36 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.212.16 with HTTP; Mon, 2 Oct 2017 07:45:36 -0700 (PDT) In-Reply-To: <1990398729.67887.1506951911360.JavaMail.zimbra@fmed.uba.ar> References: <0970df65-602e-724d-2c39-e4695ae48bb6@a-kretschmer.de> <1990398729.67887.1506951911360.JavaMail.zimbra@fmed.uba.ar> From: Mariel Cherkassky Date: Mon, 2 Oct 2017 17:45:36 +0300 Message-ID: Subject: Re: select with max functions To: Gerardo Herzig Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="94eb2c1b453278b010055a916de9" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --94eb2c1b453278b010055a916de9 Content-Type: text/plain; charset="UTF-8" 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 : > > > ----- Mensaje original ----- > > De: "Mariel Cherkassky" > > Para: "Andreas Kretschmer" > > CC: pgsql-performance@postgresql.org > > 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 : > > Do a "set statement_timeout TO 0" prior to "explain analyze" > --94eb2c1b453278b010055a916de9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
explain analyze=C2=A0 =C2=A0S= ELECT Ma.User_Id,
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 COUNT(*) COUNT
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0FROM=C2=A0 =C2=A0Manuim Ma
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHERE=C2=A0 Ma.Bb_Open_= Date=C2=A0 =3D=C2=A0
=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 (SELECT Bb_Open_Date
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FROM=C2=A0 =C2=A0Man= uim Man
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0WHERE=C2=A0 Man.User_Id =3D Ma.User_Id order by=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0bb_open_date desc limit 1
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 )=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0GROUP=C2=A0 BY Ma.User_Id
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0HAVING COUNT(*) &g= t; 1;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0QUERY PLAN=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2= =A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0
---= ---------------------------------------------------------------------------= -------------------------------------------
----------------------------------------
=C2=A0GroupAggregate=C2=A0 (cost=3D0.56..2430770384.80 rows=3D128137 wid= th=3D18) (actual time=3D55.823..2970443.757 rows=3D1213 loops=3D1)
=C2=A0 =C2=A0Group Key: ma.user_id
=C2=A0 =C2=A0Filter: (count(*) > 1)
=C2=A0 =C2=A0Rows Removed by Filter: 3693020
=C2=A0 =C2=A0->=C2=A0 Index Scan using manuim_i_u= ser_id on manuim ma=C2=A0 (cost=3D0.56..2430767766.00 rows=3D178324 width= =3D10) (actual time=3D0.249
..2966355.734= rows=3D3695461 loops=3D1)
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0Filter: (bb_open_date =3D (SubPlan 1))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Rows Removed by Filter= : 31969367
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0SubPlan 1
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0->=C2=A0 Limit=C2=A0 (cost=3D68.00..68.00 rows=3D1 widt= h=3D8) (actual time=3D0.082..0.082 rows=3D0 loops=3D35664828)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0->=C2=A0 Sort=C2=A0 (cost=3D68.00..68.04 rows=3D16 width=3D8) = (actual time=3D0.081..0.081 rows=3D0 loops=3D35664828)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Sort Key: man.bb_open_date DESC
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Sort Method: quicksort=C2=A0 Memory: 25kB
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0 Index Scan using manuim_i_use= r_id on manuim man=C2=A0 (cost=3D0.56..67.92 rows=3D16 width=3D8) (actual t= i
me=3D0.001..0.069 rows=3D85 loops=3D356= 64828)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index= Cond: ((user_id)::text =3D (ma.user_id)::text)
=C2=A0Planning time: 0.414 ms
=C2= =A0Execution time: 2970444.732 ms
(16 row= s)

<= div class=3D"gmail_quote">
2017-10-02 16:45 GMT+03:00 Gerar= do Herzig <gherzig@fmed.uba.ar>:


----- Mensaje original -----
> De: "Mariel Cherkassky" <mariel.cherkassky@gmail.com>
> Para: "Andreas Kretschmer&quo= t; <andreas@a-kretschmer.de>
<= div style=3D"direction:ltr">> Enviados: Lunes, 2 de Octubre 2017 10:25:1= 9
> 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 th= e same as what I search. Moreover, I cant use
> explain analyze because it is taking to much time to run and I= 9;m getting
> timeout..
>
> 2017-10-01 = 21:48 GMT+03:00 Andreas Kretschmer <andreas@a-kretschmer.de>:

Do a "set statement_timeout TO 0&q= uot; prior to "explain analyze"

--94eb2c1b453278b010055a916de9--