Two servers - One Replicated - Same query - Mailing list pgsql-performance

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/'