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 />