public inbox for [email protected]
help / color / mirror / Atom feedFrom: Steve Midgley <[email protected]>
To: David G. Johnston <[email protected]>
Cc: Shaozhong SHI <[email protected]>
Cc: Rob Sargent <[email protected]>
Cc: pgsql-sql <[email protected]>
Subject: Re: How to just get the last in a recursive query
Date: Mon, 4 Apr 2022 17:00:00 -0700
Message-ID: <CAJexoS+0T96aLiXhp2duvJ+TGgu03ARa7Ld=mNR+VNsfuVTWtw@mail.gmail.com> (raw)
In-Reply-To: <CAKFQuwZU1mex4t8JGz2DKJZ4cLf25w4vY9fDAJCYtJcpezWnbQ@mail.gmail.com>
References: <CA+i5JwbAz1nO_yVtfBfDeJwk8NmEQ_8bC15cOEMyc_j_Zi70Jg@mail.gmail.com>
<CA+i5Jwbh6Yy-zW3AX3WLCUJANky9w8HtVo99NPxMAc9APY0zhw@mail.gmail.com>
<[email protected]>
<CA+i5JwZR_XYcbU_bekoAo1rAE=6+X+je_7+4+repLztLZqxoHg@mail.gmail.com>
<CAKFQuwZU1mex4t8JGz2DKJZ4cLf25w4vY9fDAJCYtJcpezWnbQ@mail.gmail.com>
On Mon, Apr 4, 2022 at 4:32 PM David G. Johnston <[email protected]>
wrote:
> On Mon, Apr 4, 2022, 16:21 Shaozhong SHI <[email protected]> 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
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: How to just get the last in a recursive query
In-Reply-To: <CAJexoS+0T96aLiXhp2duvJ+TGgu03ARa7Ld=mNR+VNsfuVTWtw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox