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