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 Sat, Jan 7, 2017 at 2:56 AM, Job <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' 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

[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