Changing optimizations

If someone could, please explain the following. Here is an EXPLAIN of
a query taken when database performance was less than optimal just
after a VACUUM ANALYZE had been performed on all tables in the
database:

Aggregate (cost=16941.27..17273.88 rows=370 width=350)
-> Group (cost=16941.27..17246.16 rows=3696 width=350)
-> Sort (cost=16941.27..16941.27 rows=3696 width=350)
-> Hash Join (cost=15021.96..16722.27 rows=3696 width=350)
-> Hash Join (cost=15017.87..16561.04 rows=3696 width=314)
-> Hash Join (cost=15012.78..16408.03 rows=3696 width=286)
-> Hash Join (cost=14593.72..15663.68 rows=3696 width=250)
-> Merge Join (cost=14128.72..14933.33 rows=3696 width=244)
-> Merge Join (cost=14128.72..14787.09 rows=3696 width=236)
-> Merge Join (cost=14128.72..14181.88 rows=3696 width=222)
-> Sort (cost=14128.72..14128.72 rows=3696 width=194)
-> Hash Join (cost=5.95..13909.72 rows=3696 width=194)
-> Nested Loop (cost=0.00..13720.48 rows=4576 width=98)
-> Index Scan using percepttype_pkey on percepttype pt
(cost=0.00..6.42 rows=3 width=66)
** -> Index Scan using ptid_p_index on percept p
(cost=0.00..4716.13 rows=2484 width=32)
-> Hash (cost=5.90..5.90 rows=21 width=96)
-> Index Scan using active_h_index on hosts h
(cost=0.00..5.90 rows=21 width=96)
-> Index Scan using perceptthreshold_pkey on perceptthreshold pth
(cost=0.00..6.45 rows=41 width=28)
>> -> Index Scan using statesummary_pkey on statesummary sl
(cost=0.00..441.51 rows=9401 width=14)
-> Index Scan using perceptdepcache_pkey on perceptdepcache pdc
(cost=0.00..84.60 rows=1236 width=8)
-> Hash (cost=441.51..441.51 rows=9401 width=6)
-> Index Scan using statesummary_pkey on statesummary sd
(cost=0.00..441.51 rows=9401 width=6)
-> Hash (cost=402.04..402.04 rows=6806 width=36)
-> Index Scan using perceptlogfield_pkey on perceptlogfield plf
(cost=0.00..402.04 rows=6806 width=36)
-> Hash (cost=5.08..5.08 rows=7 width=28)
-> Index Scan using percepttypethreshold_pkey on percepttypethreshold ptt
(cost=0.00..5.08 rows=7 width=28)
-> Hash (cost=4.07..4.07 rows=6 width=36)
-> Index Scan using warehousefieldtype_pkey on warehousefieldtype wft
(cost=0.00..4.07 rows=6 width=36)

In this case, I'm specifically interested why the index scan on the
indicated row ('>>') needs to search through 9401 rows when that table
only has 9386 rows (and, hopefully, only 9386 index entries) in it.

Here is a second EXPLAIN on the same query. Just before this query,
though, I dropped and recreated the indices on the 'percept' table (in
this query, apparently, only the 'ptid_p_index' index is being used).
Now, the system is performing at the level I want:

Aggregate (cost=949.14..958.93 rows=11 width=350)
-> Group (cost=949.14..958.11 rows=109 width=350)
-> Sort (cost=949.14..949.14 rows=109 width=350)
-> Hash Join (cost=164.10..945.46 rows=109 width=350)
-> Hash Join (cost=160.01..936.66 rows=109 width=314)
-> Nested Loop (cost=154.92..927.13 rows=109 width=286)
-> Nested Loop (cost=154.92..706.28 rows=109 width=250)
-> Merge Join (cost=154.92..485.46 rows=109 width=244)
-> Nested Loop (cost=154.92..384.06 rows=109 width=236)
-> Merge Join (cost=154.92..163.24 rows=109 width=222)
-> Sort (cost=154.92..154.92 rows=109 width=194)
-> Hash Join (cost=5.95..151.24 rows=109 width=194)
-> Nested Loop (cost=0.00..139.64 rows=135 width=98)
-> Index Scan using percepttype_pkey on percepttype pt
(cost=0.00..6.42 rows=3 width=66)
** -> Index Scan using ptid_p_index on percept p
(cost=0.00..45.88 rows=19 width=32)
-> Hash (cost=5.90..5.90 rows=21 width=96)
-> Index Scan using active_h_index on hosts h
(cost=0.00..5.90 rows=21 width=96)
-> Index Scan using perceptthreshold_pkey on perceptthreshold pth
(cost=0.00..6.45 rows=41 width=28)
>> -> Index Scan using statesummary_pkey on statesummary sl
(cost=0.00..2.02 rows=1 width=14)
-> Index Scan using perceptdepcache_pkey on perceptdepcache pdc
(cost=0.00..84.60 rows=1236 width=8)
-> Index Scan using statesummary_pkey on statesummary sd
(cost=0.00..2.02 rows=1 width=6)
-> Index Scan using perceptlogfield_pkey on perceptlogfield plf
(cost=0.00..2.02 rows=1 width=36)
-> Hash (cost=5.08..5.08 rows=7 width=28)
-> Index Scan using percepttypethreshold_pkey on percepttypethreshold ptt
(cost=0.00..5.08 rows=7 width=28)
-> Hash (cost=4.07..4.07 rows=6 width=36)
-> Index Scan using warehousefieldtype_pkey on warehousefieldtype wft
(cost=0.00..4.07 rows=6 width=36)

Here, I can see the index is being used ('**'), and because of that,
the stateSummary primary key is being used correctly two (only one row
should need to be matched). Why does the ptid_p_index
stop being used, and why do I need to stop action in my database,
recreate the index, and restart the database action for it to begin
working again? It gets to be a real pain in the ass to have a stop a
system simply because the database appears to stop recognizing its
indices. While yes, this is a deep join, it's not the complicated
(everything is pretty much on 1-to-1 indices) and that particular index
isn't changing *at all* (UPDATEs, DELETEs, INSERTs, nothing).

* Philip Molter
* DataFoundry.net
* http://www.datafoundry.net/
* philip(at)datafoundry(dot)net