Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nbWcS-0001rB-0K for pgsql-sql@arkaria.postgresql.org; Tue, 05 Apr 2022 00:00:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nbWcQ-0008Qe-E6 for pgsql-sql@arkaria.postgresql.org; Tue, 05 Apr 2022 00:00:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nbWcP-0008QU-VA for pgsql-sql@lists.postgresql.org; Tue, 05 Apr 2022 00:00:22 +0000 Received: from premium22-1.web-hosting.com ([68.65.122.103]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nbWcM-0000qG-Ok for pgsql-sql@lists.postgresql.org; Tue, 05 Apr 2022 00:00:20 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=misuse.org; s=default; h=Content-Type:Cc:To:Subject:Message-ID:Date:From:In-Reply-To: References:MIME-Version:Sender:Reply-To:Content-Transfer-Encoding:Content-ID: Content-Description:Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc :Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=JFzqXXydr/2myf2KUvTVfrcBIJjx/Fv4FGNc8fsFMTs=; b=T7EHi5MPYFMEIUUYJ1KR1sqQYU 5eVTOGxACSZ7izvL1j/geZrBNDDmCWWOG0IeAO1OcWm4+TpPO1JdgQrM+52JJ7TGpwMsuOt5T2CM9 pzVn8U1LCocdlIQ9cpGfCx3kWRYAfOZlIeBdL/r11mnFGXhRoWF9IkgZ3YLNldpZ/D4TVbNhp++al NvYWkTEcoHMZ6w2Sdw0XXH+TrkyD6x6BQksUYJhrqUjSDZT3pvoeBhhIJheGXDVlAkn7Gi0oEjOJr ao7Uq/ogZbi1QhMcAg0OhVY0fTtSgfDl73GdThT12oJWA1KOS8eFnDm49psiMvnh/8qWj2Cb8Jr7K aT/rXN3Q==; Received: from mail-qk1-f182.google.com ([209.85.222.182]:42537) by premium22.web-hosting.com with esmtpsa (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1nbWcG-009yzF-5K for pgsql-sql@lists.postgresql.org; Mon, 04 Apr 2022 20:00:17 -0400 Received: by mail-qk1-f182.google.com with SMTP id 85so9069850qkm.9 for ; Mon, 04 Apr 2022 17:00:11 -0700 (PDT) X-Gm-Message-State: AOAM533BCpbMk8w0PoFuWOV3b/tAHEWe7b1017G1CqczlMB9W2BPwzCK dgOAE/w95avFeV3Kf3/2c6+1bPOuTEQNtFC5MGc= X-Google-Smtp-Source: ABdhPJy7WfOT324qaI1slQOXJdgNAW2FjaRHyywhH9Fb8T4dVqhvLdwNwumveKGmxOCxmpDk+2cNTtve/6fkm0mUfIw= X-Received: by 2002:a37:54c7:0:b0:67d:54c6:46b4 with SMTP id i190-20020a3754c7000000b0067d54c646b4mr577728qkb.68.1649116810986; Mon, 04 Apr 2022 17:00:10 -0700 (PDT) MIME-Version: 1.0 References: <3817c56b-458d-5295-e8bc-1001231dc5c8@gmail.com> In-Reply-To: From: Steve Midgley Date: Mon, 4 Apr 2022 17:00:00 -0700 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: How to just get the last in a recursive query To: "David G. Johnston" Cc: Shaozhong SHI , Rob Sargent , pgsql-sql Content-Type: multipart/alternative; boundary="000000000000b649f905dbdced8d" X-OutGoing-Spam-Status: No, score=4.9 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - premium22.web-hosting.com X-AntiAbuse: Original Domain - lists.postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - misuse.org X-Get-Message-Sender-Via: premium22.web-hosting.com: authenticated_id: science@misuse.org X-Authenticated-Sender: premium22.web-hosting.com: science@misuse.org X-Source: X-Source-Args: X-Source-Dir: X-From-Rewrite: unmodified, already matched List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b649f905dbdced8d Content-Type: text/plain; charset="UTF-8" On Mon, Apr 4, 2022 at 4:32 PM David G. Johnston wrote: > On Mon, Apr 4, 2022, 16:21 Shaozhong SHI wrote: > >> That is not the most efficient in this case. > > > Can you prove that statement? Provide a query that is more efficient. > Just to share the SQL from that example WITH RECURSIVE walk_network(id, segment) AS ( SELECT id, segment FROM network WHERE id = 6 UNION ALL SELECT n.id, n.segment FROM network n, walk_network w WHERE ST_DWithin( ST_EndPoint(w.segment), ST_StartPoint(n.segment),0.01))SELECT idFROM walk_network David J (kind of off-topic): There's no *order by *in the original query, so I could imagine that adding any order by clause at all would make the query less efficient. But maybe it could become more efficient if the planner picks a better index as a result? David (OP): My main point is that in this example, since no order by clause is provided, it is meaningless to talk about a "last" or "first" item. SQL, afaik, is not required to produce the results in any order whatsoever, when no order by clause is provided (corrections welcome if that's not accurate). So while you might grab the last item somehow this time, it might not be the last item, the next time you run the query. So I'd say you should add an appropriate order by query, and then you can measure "ASC" vs "DESC" with "LIMIT 1" to see if either one is less efficient. (I'm in David J's camp that it's unlikely to make any difference) Steve --000000000000b649f905dbdced8d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Apr 4, 2022 at 4:32 PM David G. J= ohnston <david.g.johnston@= gmail.com> wrote:
On Mon, Apr 4, 202= 2, 16:21 Shaozhong SHI <shishaozhong@gmail.com> wrote:
That is not the most efficient in this ca= se.

Can you= prove that statement?=C2=A0 Provide a query that is more efficient.
<= /div>

Just to share the SQL from that examp= le
WITH RECURSIVE=
 walk_networ=
k(id, segment) AS (
  SELECT i=
d, segment=20
    FROM n=
etwork=20
    WHERE =
id =3D 6
  UNION ALL
  SELECT n=
.id, n.segment
    FROM n=
etwork n, walk_network w
    WHERE =
ST_DWithin(<=
/span>
      ST_EndPoint(w.segment),
      ST_StartPoint=
(n.segment),0.01)
)
SELECT id<=
/span>
FROM walk_=
network

David J (kind of off-t= opic): There's no order by in the original query, so I could ima= gine that adding any order by clause at all would make the query less effic= ient. But maybe it could become more efficient if the planner picks a bette= r index as a result?

David (OP): My main point is = that in this example, since no order by clause is provided, it is meaningle= ss to talk about a "last" or "first" item. SQL, afaik, = is not required to produce the results in any order whatsoever, when no ord= er by clause is provided (corrections welcome if that's not accurate). = So while you might grab the last item somehow this time, it might not be th= e last item, the next time you run the query. So I'd say you should add= an appropriate order by query, and then you can measure "ASC" vs= "DESC" with "LIMIT 1" to see if either one is less eff= icient. (I'm in David J's camp that it's unlikely to make any d= ifference)

Steve
--000000000000b649f905dbdced8d--