failure to always use index on similar databases with eual queries - Mailing list pgsql-general

I currently have 3 db instances running.
under two of the instances my index on a complex query works
on the 3rd it doesn't. On the two that work the record count is artound
150k entries for the one that doesn't it's about 70k.

one of the db that work has no matches, and one about 300.

while the one that doesn't has about 100.

the query is the same for all db's:

//START////////////////////////////////////////////////////////////////
select

initial_close_date,reopen_date,case_title,reclose_date,actor_relationship.related_actor_id,litigant.actor_id,full_name,relationship_type,case_data.court_ori,case_data.case_id,type_subtype_text,extraction_datetime,update_date,court_location_text
fromlitigant,identity,actor_relationship,case_data,court_config where court_config.court_ori =
actor_relationship.court_oriand case_data.court_ori = actor_relationship.court_ori and case_data.case_id =
actor_relationship.case_idand identity.actor_id = actor_relationship.related_actor_id and litigant.case_id =
case_data.case_idand litigant.court_ori = case_data.court_ori and (actor_relationship.related_actor_id =
'IL071015JA6215892'or actor_relationship.related_actor_id = 'IL071015JA468002' or actor_relationship.related_actor_id =
'IL071015JA6236872'or actor_relationship.related_actor_id = 'IL071015JA6206775' or actor_relationship.related_actor_id
='IL071015JA473227' or actor_relationship.related_actor_id = 'IL071015JA90712' or actor_relationship.related_actor_id =
'IL071015JA6180132'or actor_relationship.related_actor_id = 'IL071015JA6205643' or actor_relationship.related_actor_id
='IL071015JA90951' or actor_relationship.related_actor_id = 'IL071015JA7426100' or actor_relationship.related_actor_id
='IL071015JA5442525' or actor_relationship.related_actor_id = 'IL071015JA495344' or actor_relationship.related_actor_id
='IL071015JA6185211' or actor_relationship.related_actor_id = 'IL071015JA3126994' or
actor_relationship.related_actor_id= 'IL071015JA6248620' or actor_relationship.related_actor_id = 'IL071015JA9626611'
oractor_relationship.related_actor_id = 'IL071015JA6183453' or actor_relationship.related_actor_id =
'IL071015JA4274880'or actor_relationship.related_actor_id = 'IL071015JA8442240' or actor_relationship.related_actor_id
='IL071015JA6187227' or actor_relationship.related_actor_id = 'IL071015JA6210576' or
actor_relationship.related_actor_id= 'IL071015JA7727441' or actor_relationship.related_actor_id = 'IL071015JA518018' or
actor_relationship.related_actor_id= 'IL071015JA7586616' or actor_relationship.related_actor_id = 'IL071015JA6190099'
oractor_relationship.related_actor_id = 'IL071015JA529346' or actor_relationship.related_actor_id = 'IL071015JA6224722'
oractor_relationship.related_actor_id = 'IL071015JA2352511' or actor_relationship.related_actor_id = 'IL043015JA468002'
oractor_relationship.related_actor_id = 'IL043015JA6206775' or actor_relationship.related_actor_id =
'IL043015JA6269568'or actor_relationship.related_actor_id = 'IL043015JA532142' or actor_relationship.related_actor_id =
'IL052025JA24');
//END/////////////////////////////////////////////////////////////

but one explain returns:
NOTICE:  QUERY PLAN:
//START//////////////////////////////////////////////////////////
Nested Loop  (cost=0.00..12063.43 rows=1 width=208)
  ->  Nested Loop  (cost=0.00..12059.25 rows=1 width=184)
        ->  Nested Loop  (cost=0.00..12058.22 rows=1 width=160)
              ->  Nested Loop  (cost=0.00..7687.16 rows=996 width=112)
                    ->  Index Scan using actor_relationship_speed,
actor_relationship_speed, actor_relationship_speed,
actor_relationship_speed, actor_relationship_speed,
actor_relationship_speed, actor_relationship_speed,
actor_relationship_speed, actor_relationship_speed,
actor_relationship_speed, actor_relationship_speed,
actor_relationship_speed, actor_relationship_speed,
actor_relationship_speed, actor_relationship_speed,
actor_relationship_speed, actor_relationship_speed,
actor_relationship_speed, actor_relationship_speed,
actor_relationship_speed, actor_relationship_speed,
actor_relationship_speed, actor_relationship_speed,
actor_relationship_speed, actor_relationship_speed,
actor_relationship_speed, actor_relationship_speed,
actor_relationship_speed, actor_relationship_speed,
actor_relationship_speed, actor_relationship_speed,
actor_relationship_speed, actor_relationship_speed on
actor_relationship  (cost=0.00..3463.98 rows=996 width=48)
                    ->  Index Scan using case_id_speed on case_data
(cost=0.00..4.23 rows=1 width=64)
              ->  Index Scan using litigant_speed on litigant
(cost=0.00..4.37 rows=1 width=48)
        ->  Seq Scan on court_config  (cost=0.00..1.01 rows=1 width=24)
  ->  Index Scan using ident_speed on identity  (cost=0.00..4.17 rows=1
width=24)

EXPLAIN
//END////////////////////////////////////////////////////////////

which is good, and the other returns:

//START////////////////////////////////////////////////////////////
Nested Loop  (cost=11410.02..16736.43 rows=1 width=208)
  ->  Merge Join  (cost=11410.02..16733.17 rows=1 width=184)
        ->  Nested Loop  (cost=0.00..3196.05 rows=77824 width=72)
              ->  Index Scan using court_config_pkey on court_config
(cost=0.00..3.01 rows=1 width=24)
              ->  Seq Scan on litigant  (cost=0.00..2220.24 rows=77824
width=48)
        ->  Sort  (cost=11410.02..11410.02 rows=7260 width=112)
              ->  Hash Join  (cost=5939.30..10700.68 rows=7260
width=112)
                    ->  Seq Scan on case_data  (cost=0.00..1830.09
rows=66409 width=64)
                    ->  Hash  (cost=5921.15..5921.15 rows=7260 width=48)
                          ->  Seq Scan on actor_relationship
(cost=0.00..5921.15 rows=7260 width=48)
  ->  Index Scan using ident_speed on identity  (cost=0.00..3.24 rows=1
width=24)

EXPLAIN
//END////////////////////////////////////////////////////////////

I've run vaccum, rebuilt my indexes everything...
If I force the db to not use seq_scan it will use the index, and give me
a huge speed improvement..

I'm currently making a new db and moving all the data to it. in a last
ditch effort..

thanx for any help you can give,
-jj-