Two servers - One Replicated - Same query

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

pozer(at)automotive(dot)com <mailto:pozer(at)automotive(dot)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.