To: pgsql-performance@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: Recursive query gets slower when adding an index
Date: Fri, 19 Oct 2012 19:22:05 +0200
Lines: 32
Message-ID: <k5s27q$qge$1@ger.gmane.org>
References: <k5rb2q$42s$1@ger.gmane.org> <18218.1350656456@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
	rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
In-Reply-To: <18218.1350656456@sss.pgh.pa.us>

Tom Lane wrote on 19.10.2012 16:20:
> Thomas Kellerer <spam_eater@gmx.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