Re: How to just get the last in a recursive query

On Mon, Apr 4, 2022 at 4:32 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:

> On Mon, Apr 4, 2022, 16:21 Shaozhong SHI <shishaozhong@gmail.com> 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