Re: 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