From: Tom Lane <tgl@sss.pgh.pa.us>
To: Thomas Kellerer <spam_eater@gmx.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Recursive query gets slower when adding an index
Date: Fri, 19 Oct 2012 10:20:56 -0400
Message-ID: <18218.1350656456@sss.pgh.pa.us> (raw)
In-Reply-To: <k5rb2q$42s$1@ger.gmane.org>
References: <k5rb2q$42s$1@ger.gmane.org>
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.
regards, tom lane
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: tgl@sss.pgh.pa.us, spam_eater@gmx.net
Subject: Re: Recursive query gets slower when adding an index
In-Reply-To: <18218.1350656456@sss.pgh.pa.us>
* 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