Fwd: views much slower in 9.3 than 8.4 - Mailing list pgsql-performance

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 <kgrittn@ymail.com> wrote:

"Carson, Leonard" <lcarson@sdsc.edu> 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