Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sNGwc-007h0f-8P for pgsql-general@arkaria.postgresql.org; Fri, 28 Jun 2024 19:07:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sNGwa-006Piv-G1 for pgsql-general@arkaria.postgresql.org; Fri, 28 Jun 2024 19:07:36 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sNGwa-006Pin-4W for pgsql-general@lists.postgresql.org; Fri, 28 Jun 2024 19:07:36 +0000 Received: from mail-ed1-x531.google.com ([2a00:1450:4864:20::531]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sNGwY-0042IL-F0 for pgsql-general@lists.postgresql.org; Fri, 28 Jun 2024 19:07:35 +0000 Received: by mail-ed1-x531.google.com with SMTP id 4fb4d7f45d1cf-57d2fc03740so1248799a12.0 for ; Fri, 28 Jun 2024 12:07:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1719601653; x=1720206453; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=tFAOwTZNRV0Vq9vzftgdjotNl0Ionjpz6xwIw7N8OfI=; b=bDxlEfgIvoIJXBFVmz0AfyYBs+7Eg1UC6WDwjNTu6QoZsDW0SEc5xxszg4dgB00eCL EgMyNwxSExNWsla6W/dU+Azyhc+hpNhj1moVa3ApS4T4yRz7oTd7/DhHD80/CUdPIrlo 9bfj3nuadHZl77RrMd/mmtCTalJAkGuhohObH/AfvS3awi0ASsvnR/bzp46crM3rWspW iiFl2XdqqwPnaJc3OWNt41Vt54rBjXt9wWPw38jqvrltTWeWLbLEU1acQJglmx2CGaRn FoTtj0ZDxxq/lgDhvAW5/5viShAKrPe7u/P/TrCLaguNkWlvLPvJLkMdpw7qlMnGS/n6 qMRw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1719601653; x=1720206453; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=tFAOwTZNRV0Vq9vzftgdjotNl0Ionjpz6xwIw7N8OfI=; b=p0zGs9ckcAfHmGYusUPIAAiNnU+PR2kQc6JgUiaN0gyJd4dkCbiCEcWGrNGyx2tGOc eNHCBTB0UXca95G7DSXznvVrI+KPL7TP385W04JNO4Dc7nv5WXh+eA/ea/0TsUSpsQat KNXiVJS+lG4o92PlElUIGT2u9KMB2I9LvBiA61lgLnGvbH6FCten+qPrXl4z3ff49BIv U8qwBSt+ggx/qpPh4fpNu5+qxSpXjvEkGHbIaerOL57lYPlVg4rOK21b16oXpQ4o6vJb LwHAat9YEvXvQPL+/WZLq0VJlXgcy2+yRVxR6eBSAZXiWQzhJxFUC+CzyYw41A907lAJ SPTw== X-Forwarded-Encrypted: i=1; AJvYcCWaJR04Isah4G1sSQAlyoN8WuE9rPdS+auuCu8Jql5Wn7Vz3IQH3+emrEzfcSUfBO06hMk5Tczp6VuUO5T12UnxnVKImk0bZN4lktPKOznDL0oL X-Gm-Message-State: AOJu0YxFgrRtlRizjcxIHiBRSGmnVzZZaV9Uf0r5QTYJ6gMZFhMOfI3M ugA7rmACvJVOqX4RNYkxzpSS9FcCdjeblPonGR4Bd6hZljT0ZFZmDNBLrhV9EGCxDqV+UpthTnl +8CTSPeZOvuPoz51Kt8sh6UIUYA== X-Google-Smtp-Source: AGHT+IGIgAvypB4lm/LcECH/e1eVERuyaZDel+gCLxg4xtUbF0qqYDjn9cNcTCC4K3ggHD/8lVJ6PwSdGjFL7zr6Hg8= X-Received: by 2002:a17:906:99d0:b0:a72:5ba0:193e with SMTP id a640c23a62f3a-a725ba022aamr1091864266b.61.1719601652875; Fri, 28 Jun 2024 12:07:32 -0700 (PDT) MIME-Version: 1.0 References: <451083be-83e8-413d-bc3a-ed7f3a6d99a9@gmail.com> <84196842-d6bc-4813-8740-48acc227a1dc@gmail.com> <1905ff915c3.f065a5491661249.6426145716909430027@salesium.com> In-Reply-To: <1905ff915c3.f065a5491661249.6426145716909430027@salesium.com> From: agharta agharta Date: Fri, 28 Jun 2024 21:07:21 +0200 Message-ID: Subject: Re: A way to optimize sql about the last temporary-related row To: Richard Welty Cc: David Rowley , PostgreSQL General Content-Type: multipart/alternative; boundary="000000000000acc899061bf7f583" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000acc899061bf7f583 Content-Type: text/plain; charset="UTF-8" Sorry, my wrong settings on pc mail client. Sorry again. Agharta Il ven 28 giu 2024, 19:51 Richard Welty ha scritto: > not really in direct response to this conversation, but is there any reason > on the face of the planet why read receipts need to be sent to every single > recipient of the mailing list? > > just saying, > richard > > > > ---- On Fri, 28 Jun 2024 03:20:26 -0400 * >* wrote --- > > HOO-HA! This is HUGE! > > Only 2.2 seconds on my data!!!! Amazing! > > distinct on (field) *followed by "*" *is a hidden gem! > > Thank you so much and thanks to everyone who helped me! Thank you very > much!! > > Cheers, > > Agharta > > > > > Il 27/06/24 6:16 PM, David Rowley ha scritto: > > > > On Fri, 28 Jun 2024, 3:20 am agharta82@gmail.com, > wrote: > > > Now the query: > explain (verbose, buffers, analyze) > with last_table_ids as materialized( > select xx from ( > select LAST_VALUE(pk_id) over (partition by integer_field_2 order by > datetime_field_1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED > FOLLOWING) xx > from test_table > where integer_field_1 = 1 > and datetime_field_1 <= CURRENT_TIMESTAMP > ) ww group by ww.xx > > ), > last_row_per_ids as ( > select tt.* from last_table_ids lt > inner join test_table tt on (tt.pk_id = lt.xx) > > ) > > select * /* or count(*) */ from last_row_per_ids; > > > This query, on my PC, takes 46 seconds!!! > > > (Away from laptop and using my phone) > > Something like: > > select distinct on (integer_field_2) * from test_table where > integer_field_1 = 1 and datetime_field_1 <= CURRENT_TIMESTAMP order by > integer_field_2,datetime_field_1 desc; > > Might run a bit faster. However if it's slow due to I/O then maybe not > much faster. Your version took about 5 seconds on my phone and my version > ran in 1.5 seconds. > > It's difficult for me to check the results match with each query from my > phone. A quick scan of the first 10 or so records looked good. > > If the updated query is still too slow on cold cache then faster disks > might be needed. > > David > > > > > --000000000000acc899061bf7f583 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Sorry, my wrong settings on pc mail client.
Sorry again.
Agharta

Il ven 28 giu 2024, = 19:51 Richard Welty <rwelty@sales= ium.com> ha scritto:
=
not really in direct response to this conversation, but is ther= e any reason
on the face of the planet why read receipts need= to be sent to every single
recipient of the mailing list?

just saying,
=C2=A0 richard



---- On Fri, 28= Jun 2024 03:20:26 -0400 <agharta82@gmail.com> wrote ---=

HOO-HA! This is HUGE!

Only 2= .2 seconds on my data!!!! Amazing!

distinct on (field) followe= d by "*" is a hidden gem!

Thank you so much and tha= nks to everyone who helped me!=C2=A0 Thank you very much!!

Cheers,

Agharta

=C2=A0<= br>


Il 27/06/24 6:16 PM, David Rowley ha scritto:

<= /div>


On Fri, 28 Jun 2024, 3:20 am agharta82@gmail.com, <agharta82@gmail.com> wrote:
=C2=A0
Now the query:
explain (verbose, buffers, analyz= e)
with last_table_ids as materialized(
=C2=A0=C2=A0 select xx from= (
=C2=A0=C2=A0 select LAST_VALUE(pk_id) over (partition by integer_field_2 order by
datetime_field_1 RANGE BETWEEN = UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING) xx
=C2=A0=C2=A0 from test_table=
=C2=A0=C2=A0 where integer_field_1 =3D 1
=C2=A0=C2=A0 and datetime= _field_1 <=3D CURRENT_TIMESTAMP
=C2=A0=C2=A0 ) ww group by ww.xx
=
),
last_row_per_ids as (
=C2=A0=C2=A0 select tt.* from last_t= able_ids lt
=C2=A0=C2=A0 inner join test_table tt on (tt.pk_id =3D lt.x= x)

)

select * /* or count(*) */ from last_row_per_ids;

This query, on my PC, takes 46 seconds!!!

(Away from laptop and = using my phone)

Some= thing like:

select d= istinct on (integer_field_2) * from test_table where integer_field_1 =3D 1 and datetime_field_1 <=3D CURRENT_TIMESTAMP order by integer_field_2,datetime_field_1 desc;

Might run a bit faster.=C2=A0 However if it= 9;s slow due to I/O then maybe not much faster.=C2=A0 Your version took about 5 seconds on my phone and my version ran in 1.5 seconds.

It's difficult for= me to check the results match with each query from my phone. A quick scan of the first 10 or so records looked good.

If the updated query is still too slow on cold cache then faster disks might be needed.

David



--000000000000acc899061bf7f583--