Re: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22

On 01/07/2017 04:43 AM, Venkata B Nagothi wrote:
>
> On Sat, Jan 7, 2017 at 2:56 AM, Job <Job@colliniconsulting.it
> <mailto:Job@colliniconsulting.it>> wrote:
>
>     __
>     Hi guys,
>     really much appreciated your replies.
>       >> You might want to include the query plans for each server
>     W e use a function, the explain analyze is quite similar:
>     POSTGRESQL 8.4.22:
>     explain analyze select 'record.com <http://record.com>' where
>     'record.com <http://record.com>' like '%.%' and
>     function_cloud_view_orari('53', '192.168.10.234', 'record.com
>     <http://record.com>') != '' limit 1;
>     QUERY PLAN
>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>       Limit (cost=0.03..0.04 rows=1 width=0) (actual time=1.488..1.488
>     rows=0 loops=1)
>         ->  Result  (cost=0.03..0.04 rows=1 width=0) (actual
>     time=1.485..1.485 rows=0 loops=1)
>               One-Time Filter:
>     ((function_cloud_view_orari('53'::character varying,
>     '192.168.10.234'::character varying, 'record.com
>     <http://record.com>'::character varying))::text <> ''::text)
>       Total runtime: 1.531 ms
>     POSTGRES 9.6.1:
>     explain analyze select 'record.com <http://record.com>' where
>     'record.com <http://record.com>' like '%.%' and
>     function_cloud_view_orari('53', '192.168.10.234', 'record.com
>     <http://record.com>') != '' limit 1;
>     QUERY PLAN
>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>       Limit (cost=0.03..0.04 rows=1 width=32) (actual time=4.216..4.216
>     rows=0 loops=1)
>         ->  Result  (cost=0.03..0.04 rows=1 width=32) (actual
>     time=4.215..4.215 rows=0 loops=1)
>               One-Time Filter:
>     ((function_cloud_view_orari('53'::character varying,
>     '192.168.10.234'::character varying, 'record.com
>     <http://record.com>'::character varying))::text <> ''::text)
>       Planning time: 0.046 ms
>       Execution time: 4.230 ms
>     There is only one condition that, by deleting, Query in new 9.6.1
>     Postgresql Server is very fast also on massive benchmark test.
>     The condition is this:
>     "exists ( select 1 from gruorari where
>     gruorari.idgrucate=grucategorie.id <http://grucategorie.id> and (
>     (('{'||gg_sett||'}')::int[] && array[EXTRACT(DOW FROM
>     NOW())::int])='t' and  now()::time between gruorari.dalle::time and
>     gruorari.alle::time) )"
>     We have a table of "weekly events", as example:
>     - monday from 12 to 14
>     - tuesday from 18 to 20
>     ...
>
>

> As already mentioned by others, i do not see a major performance
> problem (atleast based on the information you gave) due to upgrading
> to 9.6.1. Do you have latest statistics in place ? What about data ?
>

There almost certainly *is* performance problem, despite the queries
only take a few milliseconds. The timings show almost ~3x slowdown, and
if the application executes a lot of those queries, it can easily mean
3x increase in system load.

The question is what's causing it. I wonder whether this might be caused
by different data layout, or data not being frozen on 9.6 yet, or
something like that.

Job, can you explain how did you perform the upgrade (pg_upgrade or
pg_dump), and how you switch back to 8.4?

All this is based on the assumption the difference is consistent, and
not just random fluke.

 >
> If you can notice in the EXPLAIN output, there is a difference in
> the *width*. In 9.6.1 width is 32, any idea why ?
>

I'd guess Result was not filling the width field in 8.4, or something
like that. In any case, the plans are exactly the same in both versions.

regards