slow nested views in 9.3 - Mailing list pgsql-performance
| From | Pascal Depuis |
|---|---|
| Subject | slow nested views in 9.3 |
| Date | |
| Msg-id | CAFyY6QeRrAan88kfKXw+nJ_czRwfeH+J+EmjsgBd89G7XhFZow@mail.gmail.com Whole thread Raw |
| List | pgsql-performance |
I read that the query planner changes with every release. Was there a change from 8.4 to 9.3 that would account for a major (2 orders of magnitude) difference in execution time for nested views after we upgraded to 9.3?
Prod server running Red Hat Enterprise Linux Server release 5.11 (Tikanga) and Pg 9.3.4 on a 2 x 2.33GHZ processor, 24GB of RAM, 900 GB of RAID 5 storage on 6 drive server.
Pg configuration:
autovacuum,on,configuration file
autovacuum_analyze_scale_factor,0.1,configuration file
autovacuum_analyze_threshold,50,configuration file
autovacuum_max_workers,3,configuration file
autovacuum_naptime,1min,configuration file
autovacuum_vacuum_cost_delay,20ms,configuration file
autovacuum_vacuum_cost_limit,-1,configuration file
autovacuum_vacuum_scale_factor,0.2,configuration file
autovacuum_vacuum_threshold,50,configuration file
checkpoint_completion_target,0.9,configuration file
checkpoint_segments,16,configuration file
client_encoding,UTF8,session
client_min_messages,warning,configuration file
DateStyle,"ISO, MDY",configuration file
deadlock_timeout,5s,configuration file
default_text_search_config,pg_catalog.english,configuration file
effective_cache_size,4GB,configuration file
from_collapse_limit,8,configuration file
geqo_effort,5,configuration file
geqo_threshold,12,configuration file
hot_standby,on,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_connections,on,configuration file
log_destination,stderr,configuration file
log_directory,/dbms/postgresql/logs/dtfprod,configuration file
log_disconnections,on,configuration file
log_duration,off,configuration file
log_error_verbosity,terse,configuration file
log_filename,postgresql-%a.log,configuration file
log_hostname,on,configuration file
log_line_prefix,< %m %u %d %h >,configuration file
log_min_error_statement,error,configuration file
log_min_messages,error,configuration file
log_rotation_age,1d,configuration file
log_rotation_size,100MB,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
max_wal_senders,5,configuration file
port,5432,configuration file
random_page_cost,2,configuration file
shared_buffers,2GB,configuration file
ssl,on,configuration file
stats_temp_directory,pg_stat_tmp,configuration file
temp_buffers,16MB,configuration file
TimeZone,US/Pacific,configuration file
track_activities,on,configuration file
track_activity_query_size,1024,configuration file
track_counts,on,configuration file
track_functions,none,configuration file
track_io_timing,off,configuration file
update_process_title,on,configuration file
wal_keep_segments,1920,configuration file
wal_level,hot_standby,configuration file
wal_sender_timeout,1min,configuration file
wal_sync_method,fdatasync,configuration file
work_mem,5MB,configuration file
thanks, PasDep
pgsql-performance by date: