failure to always use index on similar databases with eual queries

From: Jeremiah Jahn <jeremiah(at)goodinassociates(dot)com>
To: postgres list <pgsql-general(at)postgresql(dot)org>
Subject: failure to always use index on similar databases with eual queries
Date: 2002-01-15 21:11:44
Message-ID: 1011129104.1787.111.camel@bluejay.goodinassociates.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 from litigant,identity,actor_relationship,case_data,court_config where court_config.court_ori = actor_relationship.court_ori and case_data.court_ori = actor_relationship.court_ori and case_data.case_id = actor_relationship.case_id and identity.actor_id = actor_relationship.related_actor_id and litigant.case_id = case_data.case_id and 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' or actor_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' or actor_relationship.related_actor_id = 'IL071015JA529346' or actor_relationship.related_actor_id = 'IL071015JA6224722' or actor_relationship.related_actor_id = 'IL071015JA2352511' or actor_relationship.related_actor_id = 'IL043015JA468002' or actor_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-

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jan Wieck 2002-01-15 21:14:58 Re: Problem whith table views.
Previous Message Cindy 2002-01-15 20:38:24 starting up postmaster