Different plan for one query problem - Mailing list pgsql-sql
From | Tatarnikov Alexander |
---|---|
Subject | Different plan for one query problem |
Date | |
Msg-id | AANLkTi=zzSmk3AL1K4geEq-0sLi8tu6r3cALD=R8UHiH@mail.gmail.com Whole thread Raw |
List | pgsql-sql |
Hello!<br /><br />I have following query:<br />SELECT <br />db_oks_zu."tbl_location"."full_address",<br />db_class."kladrCache"."region",<br/>db_class."kladrCache"."mo",<br /> db_class."kladrCache"."city",<br />db_class."kladrCache"."street",<br/>db_oks_zu."tbl_location"."house",<br />db_oks_zu."tbl_position"."number_flat",<br />((((((COALESCE(db_oks_zu."tbl_powners"."psurname",''))||(' '))||(COALESCE(db_oks_zu."tbl_powners"."pname",'')))||(' '))||(COALESCE(db_oks_zu."tbl_powners"."ppatronimic",'')))||(''))||(COALESCE(db_oks_zu."tbl_powners"."pnumdoc",'')) as c8,<br/> db_oks_zu."tbl_register"."invent_number",<br />db_oks_zu."tbl_rights"."share",<br />db_oks_zu."tbl_objects_main"."date_modifed",<br/>db_oks_zu."tbl_objects_main"."parent_id" FROM <br /> "db_oks_zu"."tbl_powners"<br />Left JOIN "db_oks_zu"."lnk_owners" ON "db_oks_zu"."lnk_owners"."powners_id"="db_oks_zu"."tbl_powners"."powners_id"<br /> Left JOIN "db_oks_zu"."tbl_rights" ON"db_oks_zu"."tbl_rights"."right_id"="db_oks_zu"."lnk_owners"."right_id" <br />Left JOIN "db_oks_zu"."tbl_objects_main"ON "db_oks_zu"."tbl_objects_main"."object_id"="db_oks_zu"."tbl_rights"."object_id" <br /> LeftJOIN "reestr_base"."fileObjects" ON "reestr_base"."fileObjects"."objectId"="db_oks_zu"."tbl_objects_main"."object_id"<br />Left JOIN "reestr_base"."invFiles"ON "reestr_base"."invFiles"."id"="reestr_base"."fileObjects"."fileId" <br /> Left JOIN "db_oks_zu"."tbl_register"ON "db_oks_zu"."tbl_register"."object_id"="db_oks_zu"."tbl_objects_main"."object_id" <br />LeftJOIN "db_oks_zu"."tbl_inventory" ON "db_oks_zu"."tbl_inventory"."register_id"="db_oks_zu"."tbl_register"."register_id"<br /> Left JOIN "db_oks_zu"."tbl_location"ON "db_oks_zu"."tbl_location"."parameter_id"="reestr_base"."invFiles"."id" <br />Left JOIN "db_class"."kladrCache"ON "db_class"."kladrCache"."code"="db_oks_zu"."tbl_location"."kladr_id" <br /> Left JOIN "db_oks_zu"."tbl_position"ON "db_oks_zu"."tbl_position"."object_id"="db_oks_zu"."tbl_objects_main"."object_id" <br />WHERE(reestr_base."invFiles"."placeStore" = '1') AND (db_oks_zu."tbl_inventory"."organization_id" = '1');<br /><br />Whenreestr_base."invFiles"."placeStore" and db_oks_zu."tbl_inventory"."organization_id" in where clause compared with '1'or '2' i'm get following query plan (and real execution time is about 10 seconds for 10,000 rows):<br /><br />QUERY PLAN<br/>Hash Left Join (cost=76024.15..169664.40 rows=76919 width=429)<br />" Hash Cond: ((tbl_location.kladr_id)::text= (""kladrCache"".code)::text)"<br /> -> Hash Left Join (cost=75094.18..165849.97 rows=76919width=430)<br /> " Hash Cond: (""invFiles"".id = tbl_location.parameter_id)"<br /> -> Hash LeftJoin (cost=25195.02..42082.87 rows=886 width=106)<br /> Hash Cond: (tbl_objects_main.object_id = tbl_position.object_id)<br/> -> Nested Loop (cost=21660.73..38535.64 rows=886 width=117)<br /> -> Nested Loop (cost=21660.73..37302.47 rows=886 width=129)<br /> -> Nested Loop (cost=21660.73..36846.12 rows=894 width=86)<br /> -> Hash Join (cost=21660.73..36458.09rows=908 width=86)<br />" Hash Cond: (tbl_rights.object_id =""fileObjects"".""objectId"")"<br /> -> Seq Scan on tbl_rights (cost=0.00..12990.66rows=479366 width=35)<br /> -> Hash (cost=21654.66..21654.66rows=486 width=51)<br /> -> Hash Join (cost=18174.09..21654.66rows=486 width=51)<br /> Hash Cond: (tbl_inventory.register_id= tbl_register.register_id)<br /> -> BitmapHeap Scan on tbl_inventory (cost=593.88..4016.58 rows=14136 width=16)<br /> Recheck Cond: ((organization_id)::text = '1'::text)<br /> -> Bitmap Index Scan on tbl_inventory_idx1 (cost=0.00..590.35rows=14136 width=0)<br /> Index Cond: ((organization_id)::text= '1'::text)<br /> -> Hash (cost=17387.93..17387.93rows=15382 width=67)<br /> -> Hash Join (cost=1358.24..17387.93 rows=15382 width=67)<br />" HashCond: (tbl_register.object_id = ""fileObjects"".""objectId"")"<br /> -> Seq Scan on tbl_register (cost=0.00..12520.21 rows=447421width=35)<br /> -> Hash (cost=1165.97..1165.97rows=15382 width=32)<br /> -> Hash Join (cost=242.29..1165.97 rows=15382 width=32)<br />" Hash Cond: (""fileObjects"".""fileId"" = ""invFiles"".id)"<br/> " -> Seq Scan on ""fileObjects"" (cost=0.00..610.30 rows=25530 width=32)"<br /> -> Hash (cost=201.95..201.95 rows=3227 width=16)<br/> " -> Seq Scan on ""invFiles"" (cost=0.00..201.95 rows=3227 width=16)"<br />" Filter: ((""placeStore"")::text ='1'::text)"<br /> -> Index Scan using lnk_owners_right_id_key on lnk_owners (cost=0.00..0.41rows=1 width=32)<br /> Index Cond: (lnk_owners.right_id = tbl_rights.right_id)<br/> -> Index Scan using powners_pkey on tbl_powners (cost=0.00..0.50rows=1 width=75)<br /> Index Cond: (tbl_powners.powners_id = lnk_owners.powners_id)<br/> -> Index Scan using objects_main_pkey1 on tbl_objects_main (cost=0.00..1.38rows=1 width=36)<br /> Index Cond: (tbl_objects_main.object_id = tbl_rights.object_id)<br/> -> Hash (cost=2157.46..2157.46 rows=110146 width=21)<br /> -> Seq Scan on tbl_position (cost=0.00..2157.46 rows=110146 width=21)<br /> -> Hash (cost=22516.85..22516.85 rows=464985 width=356)<br /> -> Seq Scan on tbl_location (cost=0.00..22516.85rows=464985 width=356)<br /> -> Hash (cost=727.21..727.21 rows=16221 width=104)<br /> " -> Seq Scan on ""kladrCache"" (cost=0.00..727.21 rows=16221 width=104)"<br /><br />But if reestr_base."invFiles"."placeStore"and db_oks_zu."tbl_inventory"."organization_id" compared with '3','4'... query plan changedto the folowing (and real execution time is 70 seconds for 200 rows)<br /><br clear="all" />QUERY PLAN<br />NestedLoop Left Join (cost=2397.45..45626.60 rows=87 width=429)<br /> -> Nested Loop Left Join (cost=2397.45..45599.74rows=87 width=430)<br />" Join Filter: (tbl_location.parameter_id = ""invFiles"".id)"<br /> -> Nested Loop Left Join (cost=2397.45..17270.57 rows=1 width=106)<br /> -> Nested Loop (cost=2397.45..17262.28 rows=1 width=117)<br /> -> Nested Loop (cost=2397.45..17260.88 rows=1width=129)<br /> -> Nested Loop (cost=2397.45..17260.37 rows=1 width=86)<br /> -> Nested Loop (cost=2397.45..17259.95 rows=1 width=86)<br /> -> Nested Loop (cost=2397.45..17253.13 rows=22 width=86)<br /> -> Hash Join (cost=2397.45..17187.99 rows=225 width=54)<br /> Hash Cond: (tbl_rights.object_id = tbl_register.object_id)<br /> -> Seq Scan on tbl_rights (cost=0.00..12990.66 rows=479366 width=35)<br/> -> Hash (cost=2394.83..2394.83 rows=210 width=19)<br/> -> Nested Loop (cost=9.95..2394.83 rows=210 width=19)<br/> -> Bitmap Heap Scan on tbl_inventory (cost=9.95..675.15rows=210 width=16)<br /> Recheck Cond:((organization_id)::text = '12'::text)<br /> -> Bitmap Index Scan on tbl_inventory_idx1 (cost=0.00..9.90 rows=210 width=0)<br /> Index Cond: ((organization_id)::text = '12'::text)<br/> -> Index Scan using register_pkey on tbl_register (cost=0.00..8.18 rows=1 width=35)<br /> IndexCond: (tbl_register.register_id = tbl_inventory.register_id)<br /> " -> Index Scan using table3_pkey on ""fileObjects"" (cost=0.00..0.28 rows=1 width=32)"<br />" Index Cond: (""fileObjects"".""objectId"" = tbl_rights.object_id)"<br/> " -> Index Scan using ""invFiles_new_pkey"" on ""invFiles"" (cost=0.00..0.30 rows=1 width=16)"<br />" Index Cond: (""invFiles"".id= ""fileObjects"".""fileId"")"<br /> " Filter: ((""invFiles"".""placeStore"")::text= '12'::text)"<br /> -> Index Scan using lnk_owners_right_id_keyon lnk_owners (cost=0.00..0.41 rows=1 width=32)<br /> IndexCond: (lnk_owners.right_id = tbl_rights.right_id)<br /> -> Index Scan using powners_pkeyon tbl_powners (cost=0.00..0.50 rows=1 width=75)<br /> Index Cond: (tbl_powners.powners_id= lnk_owners.powners_id)<br /> -> Index Scan using objects_main_pkey1 on tbl_objects_main (cost=0.00..1.38 rows=1 width=36)<br /> Index Cond: (tbl_objects_main.object_id= tbl_rights.object_id)<br /> -> Index Scan using tbl_position_idx on tbl_position (cost=0.00..8.28 rows=1 width=21)<br /> Index Cond: (tbl_position.object_id = tbl_objects_main.object_id)<br/> -> Seq Scan on tbl_location (cost=0.00..22516.85 rows=464985 width=356)<br />" -> Index Scan using ""kladrCache_pkey"" on ""kladrCache"" (cost=0.00..0.28 rows=1 width=104)"<br /> " IndexCond: ((""kladrCache"".code)::text = (tbl_location.kladr_id)::text)"<br /><br /><br />What caused changing plan? Cani force to use one (first in expamples) plan for any values in where clause?<br /><br />Thanks<br />-- <br />------<br/>Regards,<br />Tatarnikov Alexander<br />