Search Postgresql Archives

Re: Postgres 9.6.1 big slowdown by upgrading 8.4.22

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On 01/07/2017 04:43 AM, Venkata B Nagothi wrote:

On Sat, Jan 7, 2017 at 2:56 AM, Job <Job@xxxxxxxxxxxxxxxxxxxx <mailto:Job@xxxxxxxxxxxxxxxxxxxx>> 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


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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux