Thread: failure to always use index on similar databases with eual queries

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-

On 15 Jan 2002 15:11:44 -0600
Jeremiah Jahn <jeremiah@goodinassociates.com> wrote:


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

<cut>

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


AFAICS the result of EXPLAIN, it seems effective to me that first of all
the number of rows of actor_relationship is limited in order to reduce
the cost of the sort.  My rough expectation could be the following
result:

 -> Hash/Merge Join (cost= .. rows=  width=112)  (or Nested Loop)
    -> Index Scan using case_id_speed on case_data
         (cost= ..  rows=1 width=64)
    -> Sort (cost= ..  rows=997 width=112)
        -> Index Scan using actor_relationship_speed (or Seq Scan)
             (cost=  ..  rows=996  width=48)

Maybe its cost will shrink considerably. And, what result of EXPLAIN
will this query return in your situation ?

select initial_close_date,
       reopen_date,
       case_title,
       reclose_date,
       ar.related_actor_id,
       lt.actor_id,
       full_name,
       relationship_type,   -- or ar.relationship_type
       cd.court_ori,
       cd.case_id,
       type_subtype_text,
       extraction_datetime,
       update_date,
       court_location_text
  from (select * from actor_relationship -- 7260 rows
         where related_actor_id = 'IL071015JA6215892'
            or related_actor_id = 'IL071015JA468002'
            or related_actor_id = 'IL071015JA6236872'
            or related_actor_id = 'IL071015JA6206775'
            or related_actor_id = 'IL071015JA473227'
            or related_actor_id = 'IL071015JA90712'
            or related_actor_id = 'IL071015JA6180132'
            or related_actor_id = 'IL071015JA6205643'
            or related_actor_id = 'IL071015JA90951'
            or related_actor_id = 'IL071015JA7426100'
            or related_actor_id = 'IL071015JA5442525'
            or related_actor_id = 'IL071015JA495344'
            or related_actor_id = 'IL071015JA6185211'
            or related_actor_id = 'IL071015JA3126994'
            or related_actor_id = 'IL071015JA6248620'
            or related_actor_id = 'IL071015JA9626611'
            or related_actor_id = 'IL071015JA6183453'
            or related_actor_id = 'IL071015JA4274880'
            or related_actor_id = 'IL071015JA8442240'
            or related_actor_id = 'IL071015JA6187227'
            or related_actor_id = 'IL071015JA6210576'
            or related_actor_id = 'IL071015JA7727441'
            or related_actor_id = 'IL071015JA518018'
            or related_actor_id = 'IL071015JA7586616'
            or related_actor_id = 'IL071015JA6190099'
            or related_actor_id = 'IL071015JA529346'
            or related_actor_id = 'IL071015JA6224722'
            or related_actor_id = 'IL071015JA2352511'
            or related_actor_id = 'IL043015JA468002'
            or related_actor_id = 'IL043015JA6206775'
            or related_actor_id = 'IL043015JA6269568'
            or related_actor_id = 'IL043015JA532142'
            or related_actor_id = 'IL052025JA24')
         order by related_actor_id
       ) as ar -- 996 rows
       litigant as lt,  -- 77824 rows
       identity as id,  -- ? rows
       case_data as cd,  -- 66409 rows
       court_config as cc  -- ? rows
 where cc.court_ori = ar.court_ori
   and cd.court_ori = ar.court_ori
   and cd.case_id = ar.case_id
   and id.actor_id = ar.related_actor_id
   and lt.case_id = cd.case_id
   and lt.court_ori = cd.court_ori
;



Regards,
Masaru Sugawara