Re: To prefer sorts or filters in postgres, that is the question....

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