Re: Recursive query gets slower when adding an index

Tom Lane wrote on 19.10.2012 16:20:
> Thomas Kellerer <spam_eater(at)gmx(dot)net> writes:
>> This is the execution plan without index: http://explain.depesz.com/s/ecCT
>> When I create an index on parent_id execution time increases to something between 110ms and 130ms
>> This is the execution plan with index: http://explain.depesz.com/s/xiL
>
> The reason you get a bad plan choice here is the severe underestimate of
> the average number of rows coming out of the worktable scan (ie, the
> size of the "recursive" result carried forward in each iteration).
>
> Unfortunately, it's really hard to see how we might make that number
> better. The current rule of thumb is "10 times the size of the
> nonrecursive term", which is why you get 10 here. We could choose
> another multiplier but it'd be just as bogus as the current one
> (unless somebody has some evidence about typical expansion factors?)
>
> I suppose though that there's some argument for discouraging the planner
> from assuming that the carried-forward result is small; so maybe we
> should use something larger than 10.
>

Thanks for the feedback.

I just noticed this behaviour because we ran the same query on SQL Server 2008 and that took well over 30seconds without the index
SQL Server *really* improved with the index and returned the result in 0.5 seconds whith the index in place.

So I was curious how much faster Postgres would be *with* the index ;)

Regards
Thomas