Re: failure to always use index on similar databases with eual queries
От | Masaru Sugawara |
---|---|
Тема | Re: failure to always use index on similar databases with eual queries |
Дата | |
Msg-id | 20020120172747.A7D2.RK73@echna.ne.jp обсуждение исходный текст |
Ответ на | failure to always use index on similar databases with eual queries (Jeremiah Jahn <jeremiah@goodinassociates.com>) |
Список | 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
В списке pgsql-general по дате отправления: