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

On 15 Jan 2002 15:11:44 -0600
Jeremiah Jahn <jeremiah(at)goodinassociates(dot)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