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

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