Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dz0kf-0003P3-AV for pgsql-performance@arkaria.postgresql.org; Mon, 02 Oct 2017 13:27:17 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dz0ke-00023q-MQ for pgsql-performance@arkaria.postgresql.org; Mon, 02 Oct 2017 13:27:16 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dz0is-0007MU-Bd for pgsql-performance@postgresql.org; Mon, 02 Oct 2017 13:25:26 +0000 Received: from mail-wr0-x235.google.com ([2a00:1450:400c:c0c::235]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dz0io-00039Q-Eg for pgsql-performance@postgresql.org; Mon, 02 Oct 2017 13:25:25 +0000 Received: by mail-wr0-x235.google.com with SMTP id o44so2124490wrf.11 for ; Mon, 02 Oct 2017 06:25:22 -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=Xc6K8ZwDVXlOSRm+h/5ntr/9rjfZY5p1Pc2EHTcPRqM=; b=UxHSilx6Kgq2F9bGqR2xfv/vbiBtEjSt8nU7Fwv6+5+O2gSya1tNanWvErRbaCk5oO dh0K1yWIY9fJSA/izBYdaqB6JHHapcE8Qp8UNj+5auozDXJY0VRslURiWWxD1vZq5sKs N8TPQ31e446IttUWX0mhv7xXVmRl95wGZK8gTFuJroSN9wPJPNbdNgAz0W3zkTvJft/j cttDfFnpX+yfYUAd16diLAc0UHA7VUDfflzWjkolzN7IEvVJQmKTwa2RASIuS+pnItUC vjML5VEyKXhcMK9nDTlnHDME4oHtuEmlyWDh13r4242USuayxRWTQPTr4wa71JHCc/wC Qbhg== 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=Xc6K8ZwDVXlOSRm+h/5ntr/9rjfZY5p1Pc2EHTcPRqM=; b=FLxGMOAp10QQyOPqSODqrULyfKc9hkb7zpNU0m7Sq0TG4BxMlpAdsxNcTotc290aJu Md0EaxyGRKvSb75jUbTkk9PEsPrlf4bHYCYjSLHUCgowEGKbe2B6mKxWhowRD8Lq9gdL EEaPymHpDbhQ5x3yzE+8yQegGHlZofzGolQZLICL0sFGKKpVQA3se1DLD8JilCoiJ8F5 4VIHHqOC9jv8aLL1saSsSDGKPwDek3G8RCj3chl07XQ60XzcFgkM4bOcHY8+xmoPEyDW AsZjPNQ1TogCV+GxtdR81CAWgJlX0bH/gIE92e/H9gRq3OrVVU2hveboVkJwoop1eb7r 81Jw== X-Gm-Message-State: AMCzsaXvGFW0ljqBofA2iv1mWARBvXRsyqJCeabxt5Vl/Ur8PxeJeD5r QZLMih5QkZQxtQ56EcC6b5i6X5wkL9vajb/bd5FhvQ== X-Google-Smtp-Source: AOwi7QBwsgHWGAuswkMVdXYnCWtb2/uMKnx8wfTAKjQxbZgdxDoR1PNudNAUz0XFgDysXW9KwmKEv5xJkQxOBEr/S1M= X-Received: by 10.223.197.13 with SMTP id q13mr9946117wrf.272.1506950720808; Mon, 02 Oct 2017 06:25:20 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.212.16 with HTTP; Mon, 2 Oct 2017 06:25:19 -0700 (PDT) In-Reply-To: <0970df65-602e-724d-2c39-e4695ae48bb6@a-kretschmer.de> References: <0970df65-602e-724d-2c39-e4695ae48bb6@a-kretschmer.de> From: Mariel Cherkassky Date: Mon, 2 Oct 2017 16:25:19 +0300 Message-ID: Subject: Re: select with max functions To: Andreas Kretschmer Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="089e0826b234686d07055a904ef6" 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 --089e0826b234686d07055a904ef6 Content-Type: text/plain; charset="UTF-8" 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 : > > > Am 01.10.2017 um 14:41 schrieb Mariel Cherkassky: > >> 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; >> >> >> Any idea how can I continue from here ? Thanks , Mariel. >> > > > Maybe you can rewrite it, for instance to > > select distinct on (user_id, bb_open_date) user_id, bb_open_date, count(1) > from Manuim group by 1,2 having count(1) > 1; > > maybe much cheaper, but untested! If not, please share more details, at > least table-definition. > > Regards, Andreas > > -- > 2ndQuadrant - The PostgreSQL Support Company. > www.2ndQuadrant.com > > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > --089e0826b234686d07055a904ef6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Andreas I tried to rewrite it with the fu= nction rank() but I failed. The query you wrote isnt the same as what I sea= rch. 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 <andreas@a-kretschmer.de>:


Am 01.10.2017 um 14:41 schrieb Mariel Cherkassky:
Hi,
I need to use the max function in my query. I had very bad performance when= I used the max=C2=A0 :

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SELECT 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=A0Man= uim Ma
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHERE=C2=A0 Ma.Bb_Op= en_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 (SELECT max(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 Manuim 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 Man.User_Id =3D M= a.User_Id
=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.Us= er_Id
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0HAVING COUNT(*) >= 1;


 Any idea how can I continue from here ? Thanks , Mariel.


Maybe you can rewrite it, for instance to

select distinct on (user_id, bb_open_date) user_id, bb_open_date, count(1) = from Manuim group by 1,2 having count(1) > 1;

maybe much cheaper, but untested! If not, please share more details, at lea= st table-definition.

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-pe= rformance

--089e0826b234686d07055a904ef6--