I have two servers one has replication the other does not. The same query on both servers. One takes 225seconds on the replicated server the first time it runs and only 125ms on the other server the first time it runs. The second time you execute the query it drops to the 125ms. They are using the same query plan. What kind of things should I be looking at?
QUERY:
select distinct cast(max(VehicleUsed."VehicleUsedPrice.max") as int) as
"VehicleUsedPrice.max",cast(min(VehicleUsed."VehicleUsedPrice.min") as
int) as
"VehicleUsedPrice.min",cast(avg(VehicleUsed."VehicleUsedPrice.average")
as int) as "VehicleUsedPrice.average"
from VehicleUsed_v1 as VehicleUsed
inner join PostalCodeRegionCountyCity_v1 as PostalCodeRegionCountyCity
on
(lower(VehicleUsed.PostalCode)=lower(PostalCodeRegionCountyCity.PostalCo
de))
where (VehicleUsed.VehicleMakeId in (5,7,10,26,43,45,46,49,51,67,86))
and (PostalCodeRegionCountyCity.RegionId=44)
limit 500000
QUERY PLAN:
"Limit (cost=54953.88..54953.93 rows=1 width=12)"
" -> Unique (cost=54953.88..54953.93 rows=1 width=12)"
" -> Sort (cost=54953.88..54953.90 rows=1 width=12)"
" Sort Key: (max(vehicleused."VehicleUsedPrice.max")),
(min(vehicleused."VehicleUsedPrice.min")),
((avg(vehicleused."VehicleUsedPrice.average"))::integer)"
" -> Aggregate (cost=54953.73..54953.84 rows=1 width=12)"
" -> Hash Join (cost=4354.43..54255.18 rows=23284
width=12)"
" Hash Cond:
(lower((vehicleused.postalcode)::text) =
lower((postalcoderegioncountycity.postalcode)::text))"
" -> Bitmap Heap Scan on vehicleused_v1
vehicleused (cost=3356.65..48157.38 rows=50393 width=18)"
" Recheck Cond: (vehiclemakeid = ANY
('{5,7,10,26,43,45,46,49,51,67,86}'::integer[]))"
" -> Bitmap Index Scan on
vehicleused_v1_i08 (cost=0.00..3306.26 rows=50393 width=0)"
" Index Cond: (vehiclemakeid = ANY
('{5,7,10,26,43,45,46,49,51,67,86}'::integer[]))"
" -> Hash (cost=711.12..711.12 rows=2606
width=6)"
" -> Index Scan using
postalcoderegioncountycity_v1_i05 on postalcoderegioncountycity_v1
postalcoderegioncountycity (cost=0.00..711.12 rows=2606 width=6)"
" Index Cond: (regionid = 44)"
SERVER SETTINGS:
The settings are the same on each server with the exception of the
replication:
PGSQL9.0.3
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of
addresses;
# defaults to 'localhost', '*' =
all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
# (change requires restart)
bonjour_name = 'halcpcnt1s' # defaults to the
computer name
# (change requires restart)
shared_buffers = 3GB # min 128kB
effective_cache_size = 6GB
log_destination = 'stderr' # Valid values are combinations
of
logging_collector = on # Enable capturing of stderr and csvlog
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8' # locale for system
error message
# strings
lc_monetary = 'en_US.UTF-8' # locale for monetary
formatting
lc_numeric = 'en_US.UTF-8' # locale for number
formatting
lc_time = 'en_US.UTF-8' # locale for time
formatting
# default configuration for text search
default_text_search_config = 'pg_catalog.english'
max_connections = 100
temp_buffers = 100MB
work_mem = 100MB
maintenance_work_mem = 500MB
max_files_per_process = 10000
seq_page_cost = 1.0
random_page_cost = 1.1
cpu_tuple_cost = 0.1
cpu_index_tuple_cost = 0.05
cpu_operator_cost = 0.01
default_statistics_target = 1000
autovacuum_max_workers = 1
constraint_exclusion = on
checkpoint_completion_target = 0.9
wal_buffers = 8MB
checkpoint_segments = 100
#log_min_messages = DEBUG1
#log_min_duration_statement = 1000
#log_statement = all
#log_temp_files = 128
#log_lock_waits = on
#log_line_prefix = '%m %u %d %h %p %i %c %l %s'
#log_duration = on
#debug_print_plan = on
# Replication Settings
hot_standby = on
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
archive_mode = on
archive_command = 'cp %p /usr/local/pgsql/data/pg_xlog/archive/'
Pam Ozer
Data Architect
po...@automotive.com <mailto:po...@automotive.com>
tel. 949.705.3468
Source Interlink Media
1733 Alton Pkwy Suite 100, Irvine, CA 92606
www.simautomotive.com <http://www.simautomotive.com>
Confidentiality Notice- This electronic communication, and all
information herein, including files attached hereto, is private, and is
the property of the sender. This communication is intended only for the
use of the individual or entity named above. If you are not the intended
recipient, you are hereby notified that any disclosure of; dissemination
of; distribution of; copying of; or, taking any action in reliance upon
this communication, is strictly prohibited. If you have received this
communication in error, please immediately notify us by telephone,
(949)-705-3000, and destroy all copies of this communication. Thank you.
<<image001.jpg>>
<<image004.jpg>>
