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
