From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-performance@postgresql.org
Subject: Re: Recursive query gets slower when adding an index
Date: Fri, 19 Oct 2012 19:22:05 +0200
Message-ID: <k5s27q$qge$1@ger.gmane.org> (raw)
In-Reply-To: <18218.1350656456@sss.pgh.pa.us>
References: <k5rb2q$42s$1@ger.gmane.org>
<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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: spam_eater@gmx.net
Subject: Re: Recursive query gets slower when adding an index
In-Reply-To: <k5s27q$qge$1@ger.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox