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