Re: 9.6 Beta 2 Performance Regression on Recursive CTE

"Nicholson, Brad (Toronto, ON, CA)" <bnicholson@hpe.com> writes:
> I'm seeing a performance regression on 9.6 Beta 2 compared to 9.5.3.
> The query is question is a recursive query on graph data stored as an
> adjacency list.

FWIW, I can't reproduce any regression with this example.  I get EXPLAIN
execution times like these, depending on which datatype and environment
I use:

9.5:
text, C locale: 9307.186 ms
citext, C locale: 12541.840 ms
citext, en_US.utf8 locale: 22551.332 ms

HEAD:
text, C locale: 9109.792 ms
citext, C locale: 12251.408 ms
citext, en_US.utf8 locale: 22129.683 ms

The plans are all the same of course.  The extra runtime for citext is
presumably due to the cost of smashing strings to lowercase within
citext_eq().

This is with assert-enabled builds of current branch tips, not the
release versions you're working from, but I don't recall that we've
changed anything recently that would be likely to affect this.

Maybe you're comparing an assert-enabled beta build to a
not-assert-enabled 9.5 build?

> As an aside, the cost mis-estimates are very common with these types of
> queries on graph data,

The only error I'm noticing is a bad guess about the size of the recursive
union result, which is unsurprising since it is only a guess.  If you've
heard of ways to estimate recursive union sizes more plausibly, maybe we
could do something about that.

            regards, tom lane