From: | Venkata B Nagothi <nag1010(at)gmail(dot)com> |
---|---|
To: | Job <Job(at)colliniconsulting(dot)it> |
Cc: | Rob Sargent <robjsargent(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Postgres 9.6.1 big slowdown by upgrading 8.4.22 |
Date: | 2017-01-07 03:43:32 |
Message-ID: | CAEyp7J8ZNcdjyvgPbChADtd_2bExWtXqBqAABKcT8bsi4pGCBg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sat, Jan 7, 2017 at 2:56 AM, Job <Job(at)colliniconsulting(dot)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' where 'record.com' like '%.%' and
> function_cloud_view_orari('53', '192.168.10.234', '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'::character
> varying))::text <> ''::text)
> Total runtime: 1.531 ms
>
> POSTGRES 9.6.1:
> explain analyze select 'record.com' where 'record.com' like '%.%' and
> function_cloud_view_orari('53', '192.168.10.234', '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'::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
> 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 ?
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 ?
Regards,
Venkata B N
Database Consultant
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2017-01-07 09:55:58 | Re: Postgres 9.6.1 big slowdown by upgrading 8.4.22 |
Previous Message | Melvin Davidson | 2017-01-06 23:47:15 | Re: Postgres 9.6.1 big slowdown by upgrading 8.4.22 |