Changing optimizations - Mailing list pgsql-general

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@datafoundry.net