From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Nicholson, Brad (Toronto, ON, CA)" <bnicholson(at)hpe(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: 9.6 Beta 2 Performance Regression on Recursive CTE |
Date: | 2016-07-05 17:11:17 |
Message-ID: | 3253.1467738677@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"Nicholson, Brad (Toronto, ON, CA)" <bnicholson(at)hpe(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | J. Cassidy | 2016-07-05 17:13:56 | Re: pg_dump fundenental question |
Previous Message | J. Cassidy | 2016-07-05 17:10:05 | Re: pg_dump fundenental question |