There is only one server at this point. The 8.4 machine was upgraded to 9.3 about a year ago and we have no 8.4 backups so it's difficult if not impossible to recreate the 8.4 environment AFAIK. One of our developers pointed out the discrepancy in execution times. I decomposed a slow view and found out that it consists of a view calling a view calling a view (3 deep). This is the analyze explain plan of the innermost view:
http://explain.depesz.com/s/IMg And as you requested: SELECT version(); Postgresql 9.3.4 SELECT name, current_setting(name), source FROM pg_settings WHERE source NOT IN ('default', 'override'); autovacuum,on,configuration file checkpoint_completion_target,0.9,configuration file checkpoint_segments,16,configuration file client_encoding,UTF8,session client_min_messages,notice,configuration file DateStyle,"ISO, MDY",configuration file deadlock_timeout,5s,configuration file default_text_search_config,pg_catalog.english,configuration file effective_cache_size,8GB,configuration file effective_io_concurrency,6,configuration file lc_messages,en_US.UTF-8,configuration file lc_monetary,en_US.UTF-8,configuration file lc_numeric,en_US.UTF-8,configuration file lc_time,en_US.UTF-8,configuration file listen_addresses,*,configuration file log_checkpoints,on,configuration file log_connections,on,configuration file log_destination,stderr,configuration file log_directory,/dbms/postgresql/logs/dtfdev,configuration file log_disconnections,on,configuration file log_duration,off,configuration file log_error_verbosity,verbose,configuration file log_filename,postgresql-%a.log,configuration file log_hostname,on,configuration file log_line_prefix,"%t [%p]: [%l-1] db=%d,user=%u ",configuration file log_lock_waits,on,configuration file log_min_duration_statement,0,configuration file log_min_error_statement,error,configuration file log_min_messages,warning,configuration file log_rotation_age,1d,configuration file log_rotation_size,500MB,configuration file log_statement,none,configuration file log_temp_files,0,configuration file log_timezone,US/Pacific,configuration file log_truncate_on_rotation,on,configuration file logging_collector,on,configuration file maintenance_work_mem,256MB,configuration file max_connections,200,configuration file max_stack_depth,8MB,configuration file port,2222,configuration file random_page_cost,2,configuration file search_path,"acct, ""$user"", public",session shared_buffers,4GB,configuration file ssl,on,configuration file temp_buffers,16MB,configuration file TimeZone,US/Pacific,configuration file wal_level,minimal,configuration file wal_sync_method,fdatasync,configuration file work_mem,5MB,configuration file server has 24GB of RAM from postgresql.conf: shared_buffers = 4GB effective_cache_size = 8GB work_mem = 5MB (note: I increased work_mem to 500MB and repeated the experiment, no difference in exec. time) On Mar 18, 2015, at 10:16 AM, Kevin Grittner <[email protected]<mailto:[email protected]>> wrote: "Carson, Leonard" <[email protected]<mailto:[email protected]>> wrote: While acknowledging that nested loops and sequential table scans account for 85% of the execution time which suggests that a better query may be needed, why would the same query run in seconds on 8.x but take minutes on 9.x? First, please show the output of this from both servers: SELECT version(); SELECT name, current_setting(name), source FROM pg_settings WHERE source NOT IN ('default', 'override'); Then, for your newer server, please follow the steps outlined here: https://wiki.postgresql.org/wiki/SlowQueryQuestions My first guess would be that at some point your costing parameters were tuned on the old system, but have not yet been tuned on the new one. Rather than blindly using the old settings for the new server, it would be good to see the information requested on the above-cited page to determine good settings for the new server. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
