Thomas Kellerer <spam_eater@xxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
