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.PostalCode)) 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/'
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. |
