Slow Query- Bad Row Estimate

Unfortunately I have not received a response on this question. Is more
information needed? Does anyone have any ideas why the estimates may be
bad? Or what I might be able to do to speed this up?

thanks

From: Ozer, Pam
Sent: Tuesday, October 26, 2010 4:27 PM
To: 'pgsql-performance(at)postgresql(dot)org'
Subject: Slow Query- Bad Row Estimate

I have the following query:

select distinct Region.RegionShort as RegionShort

,County.County as County

from Region

join PostalCodeRegionCountyCity on
(PostalCodeRegionCountyCity.RegionId=Region.RegionId)

join DealerGroupGeoCache on
(DealerGroupGeoCache.RegionId=PostalCodeRegionCountyCity.RegionId)

and
(DealerGroupGeoCache.CountyId=PostalCodeRegionCountyCity.CountyId)

and
(DealerGroupGeoCache.CityId=PostalCodeRegionCountyCity.CityId)

join County on (PostalCodeRegionCountyCity.CountyId=County.CountyId)

where (DealerGroupGeoCache.DealerGroupId=13) and
(PostalCodeRegionCountyCity.RegionId=5)

With the following Explain:

"HashAggregate (cost=6743.96..6747.36 rows=34 width=11) (actual
time=854.407..854.425 rows=57 loops=1)"

" -> Nested Loop (cost=0.00..6743.28 rows=34 width=11) (actual
time=0.062..762.698 rows=163491 loops=1)"

" -> Nested Loop (cost=0.00..6679.19 rows=34 width=11) (actual
time=0.053..260.001 rows=163491 loops=1)"

" -> Index Scan using region_i00 on region
(cost=0.00..3.36 rows=1 width=5) (actual time=0.009..0.011 rows=1
loops=1)"

" Index Cond: (regionid = 5)"

" -> Merge Join (cost=0.00..6672.43 rows=34 width=10)
(actual time=0.040..189.654 rows=163491 loops=1)"

" Merge Cond: ((postalcoderegioncountycity.countyid =
dealergroupgeocache.countyid) AND (postalcoderegioncountycity.cityid =
dealergroupgeocache.cityid))"

" -> Index Scan using postalcoderegioncountycity_i06
on postalcoderegioncountycity (cost=0.00..716.05 rows=2616 width=10)
(actual time=0.018..1.591 rows=2615 loops=1)"

" Index Cond: (regionid = 5)"

" -> Index Scan using dealergroupgeocache_i01 on
dealergroupgeocache (cost=0.00..5719.56 rows=9055 width=10) (actual
time=0.015..87.689 rows=163491 loops=1)"

" Index Cond:
((dealergroupgeocache.dealergroupid = 13) AND
(dealergroupgeocache.regionid = 5))"

" -> Index Scan using county_i00 on county (cost=0.00..1.77
rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=163491)"

" Index Cond: (county.countyid =
dealergroupgeocache.countyid)"

"Total runtime: 854.513 ms"

The statistics have been recently updated and it does not change the bad
estimates.

The DealerGroupGeoCache Table has 765392 Rows, And the query returns 57
rows.

I am not at all involved in the way the server is set up so being able
to change the settings is not very likely unless it will make a huge
difference.

Is there any way for me to speed up this query without changing the
settings?

If not what would you think the changes that would be needed?

We are currently running Postgres8.4 with the following settings.

shared_buffers = 500MB #
min 128kB

effective_cache_size = 1000MB

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

#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

Any help is appreciated,

Pam