Bob Jones wrote: > I've been playing around with hierarchical queries a bit and one thing > I wanted to do is build a query that gives the ultimate parent for a > given child. > > The two queries below seem to be a good a shortlist as any. > > I'm no query-plan guru, but these seem to be largely identical aside > from one uses "filter IS NULL" and the other uses "top-N heapsort". > > Would there be a reason to prefer one over the other (or perhaps > there's an altogether more efficient way of doing this query ?!?). > My gut-instinct says the sort version ?
The ultimate criterion which one is better is the execution time, but you probably need more data to tell with certainty. At a short glance, I'd say that they are pretty much the same. The filter and the top-1-sort will both require a single scan through the result set and one operation per row found. And the recursive queries are pretty similar, right? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com