BUG #12859: views much slower in 9.4.1 than 8.4.7

Hi,

On 12.3.2015 21:59, lcarson@sdsc.edu wrote:

The following bug has been logged on the website:

Bug reference: 12859
Logged by: lcarson
Email address: lcarson@sdsc.edu
PostgreSQL version: 9.4.1
Operating system: redhat 6
Description:

We have views that are running anywhere from 20x slower to failing to
complete in 9.4.1 whereas in 8.4.7 they produced results in seconds
or tensof seconds on the same platform.

After upgrading from 8.4 to 9.3, I ran ANALYZE on the entire db and
it DID improve performance for some, but not all, of the views in
question.

Here is an explain plan of one of the slow views in 9.4:
http://explain.depesz.com/s/36n

While acknowledging that nested loops and sequential scans account
for 85% of the execution time, why would this run in seconds in 8.4
and minutes in 9.4? Is it because of changes to the planner?

You have not provided explain plans from 8.4, and I'd bet it was using a
different plan on that version.

The problem here are under-estimates, pushing the planner to use nested
loops - it simply believes there will be very few loops, but in reality
there are many more. The differences are several orders of magnitude,
starting with

Nested Loop (cost=0.29..79,861.74 rows=2 width=141)
(actual time=0.079..473.530 rows=23,832 loops=1)

for the inner-most nested loop (12000x more) and snowballing up to this

Nested Loop (cost=71,484.01..71,501.60 rows=1 width=28)
(actual time=379,042.815..383,820.341 rows=263,371 loops=1)

I wonder why the estimates are so much worse, though.

--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Tomas Vondra <tomas.vondra@2ndquadrant.com> writes:

On 12.3.2015 21:59, lcarson@sdsc.edu wrote:

We have views that are running anywhere from 20x slower to failing to
complete in 9.4.1 whereas in 8.4.7 they produced results in seconds
or tensof seconds on the same platform.

You have not provided explain plans from 8.4, and I'd bet it was using a
different plan on that version.

We'd really need far more information than that to investigate this.
Please see
https://wiki.postgresql.org/wiki/Slow_Query_Questions

Also, as suggested on that page, pgsql-performance is likely a better
venue for this discussion than pgsql-bugs.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs