Re: Recursive query gets slower when adding an index
От | Thomas Kellerer |
---|---|
Тема | Re: Recursive query gets slower when adding an index |
Дата | |
Msg-id | k5s27q$qge$1@ger.gmane.org обсуждение исходный текст |
Ответ на | Re: Recursive query gets slower when adding an index (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-performance |
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 withoutthe 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
В списке pgsql-performance по дате отправления: