[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    [PERFORM] slow nested views in 9.3
From:       Pascal Depuis <pascal.depuis () gmail ! com>
Date:       2015-02-05 22:29:04
Message-ID: CAFyY6QeRrAan88kfKXw+nJ_czRwfeH+J+EmjsgBd89G7XhFZow () mail ! gmail ! com
[Download RAW message or body]

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?

http://stackoverflow.com/questions/24067543/nested-views-much-slower-in-pg-9-3-4-than-8-4-8

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

[Attachment #3 (text/html)]

<div dir="ltr"><div>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?</div><div><br></div><a \
href="http://stackoverflow.com/questions/24067543/nested-views-much-slower-in-pg-9-3-4 \
-than-8-4-8">http://stackoverflow.com/questions/24067543/nested-views-much-slower-in-pg-9-3-4-than-8-4-8</a><br><div><br></div><div>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.</div><div><br></div><div>Pg \
configuration:</div><div><div>autovacuum,on,configuration \
file</div><div>autovacuum_analyze_scale_factor,0.1,configuration \
file</div><div>autovacuum_analyze_threshold,50,configuration \
file</div><div>autovacuum_max_workers,3,configuration \
file</div><div>autovacuum_naptime,1min,configuration \
file</div><div>autovacuum_vacuum_cost_delay,20ms,configuration \
file</div><div>autovacuum_vacuum_cost_limit,-1,configuration \
file</div><div>autovacuum_vacuum_scale_factor,0.2,configuration \
file</div><div>autovacuum_vacuum_threshold,50,configuration \
file</div><div>checkpoint_completion_target,0.9,configuration \
file</div><div>checkpoint_segments,16,configuration \
file</div><div>client_encoding,UTF8,session</div><div>client_min_messages,warning,configuration \
file</div><div>DateStyle,&quot;ISO, MDY&quot;,configuration \
file</div><div>deadlock_timeout,5s,configuration \
file</div><div>default_text_search_config,pg_catalog.english,configuration \
file</div><div>effective_cache_size,4GB,configuration \
file</div><div>from_collapse_limit,8,configuration \
file</div><div>geqo_effort,5,configuration \
file</div><div>geqo_threshold,12,configuration \
file</div><div>hot_standby,on,configuration \
file</div><div>lc_messages,en_US.UTF-8,configuration \
file</div><div>lc_monetary,en_US.UTF-8,configuration \
file</div><div>lc_numeric,en_US.UTF-8,configuration \
file</div><div>lc_time,en_US.UTF-8,configuration \
file</div><div>listen_addresses,*,configuration \
file</div><div>log_connections,on,configuration \
file</div><div>log_destination,stderr,configuration \
file</div><div>log_directory,/dbms/postgresql/logs/dtfprod,configuration \
file</div><div>log_disconnections,on,configuration \
file</div><div>log_duration,off,configuration \
file</div><div>log_error_verbosity,terse,configuration \
file</div><div>log_filename,postgresql-%a.log,configuration \
file</div><div>log_hostname,on,configuration file</div><div>log_line_prefix,&lt; %m \
%u %d %h &gt;,configuration \
file</div><div>log_min_error_statement,error,configuration \
file</div><div>log_min_messages,error,configuration \
file</div><div>log_rotation_age,1d,configuration \
file</div><div>log_rotation_size,100MB,configuration \
file</div><div>log_timezone,US/Pacific,configuration \
file</div><div>log_truncate_on_rotation,on,configuration \
file</div><div>logging_collector,on,configuration \
file</div><div>maintenance_work_mem,256MB,configuration \
file</div><div>max_connections,200,configuration \
file</div><div>max_stack_depth,8MB,configuration \
file</div><div>max_wal_senders,5,configuration file</div><div>port,5432,configuration \
file</div><div>random_page_cost,2,configuration \
file</div><div>shared_buffers,2GB,configuration file</div><div>ssl,on,configuration \
file</div><div>stats_temp_directory,pg_stat_tmp,configuration \
file</div><div>temp_buffers,16MB,configuration \
file</div><div>TimeZone,US/Pacific,configuration \
file</div><div>track_activities,on,configuration \
file</div><div>track_activity_query_size,1024,configuration \
file</div><div>track_counts,on,configuration \
file</div><div>track_functions,none,configuration \
file</div><div>track_io_timing,off,configuration \
file</div><div>update_process_title,on,configuration \
file</div><div>wal_keep_segments,1920,configuration \
file</div><div>wal_level,hot_standby,configuration \
file</div><div>wal_sender_timeout,1min,configuration \
file</div><div>wal_sync_method,fdatasync,configuration \
file</div><div>work_mem,5MB,configuration file</div></div><div><br></div><div>thanks, \
PasDep</div><div><br></div><div><br></div><div><br></div><div><br></div><div><br></div></div>




[prev in list] [next in list] [prev in thread] [next in thread]