Hello!
I have following query:
SELECT
db_oks_zu."tbl_location"."full_address",
db_class."kladrCache"."region",
db_class."kladrCache"."mo",
db_class."kladrCache"."city",
db_class."kladrCache"."street",
db_oks_zu."tbl_location"."house",
db_oks_zu."tbl_position"."number_flat",
((((((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,
db_oks_zu."tbl_register"."invent_number",
db_oks_zu."tbl_rights"."share",
db_oks_zu."tbl_objects_main"."date_modifed",
db_oks_zu."tbl_objects_main"."parent_id" FROM
"db_oks_zu"."tbl_powners"
Left JOIN "db_oks_zu"."lnk_owners" ON
"db_oks_zu"."lnk_owners"."powners_id"="db_oks_zu"."tbl_powners"."powners_id"
Left JOIN "db_oks_zu"."tbl_rights" ON
"db_oks_zu"."tbl_rights"."right_id"="db_oks_zu"."lnk_owners"."right_id"
Left JOIN "db_oks_zu"."tbl_objects_main" ON
"db_oks_zu"."tbl_objects_main"."object_id"="db_oks_zu"."tbl_rights"."object_id"
Left JOIN "reestr_base"."fileObjects" ON
"reestr_base"."fileObjects"."objectId"="db_oks_zu"."tbl_objects_main"."object_id"
Left JOIN "reestr_base"."invFiles" ON
"reestr_base"."invFiles"."id"="reestr_base"."fileObjects"."fileId"
Left JOIN "db_oks_zu"."tbl_register" ON
"db_oks_zu"."tbl_register"."object_id"="db_oks_zu"."tbl_objects_main"."object_id"
Left JOIN "db_oks_zu"."tbl_inventory" ON
"db_oks_zu"."tbl_inventory"."register_id"="db_oks_zu"."tbl_register"."register_id"
Left JOIN "db_oks_zu"."tbl_location" ON
"db_oks_zu"."tbl_location"."parameter_id"="reestr_base"."invFiles"."id"
Left JOIN "db_class"."kladrCache" ON
"db_class"."kladrCache"."code"="db_oks_zu"."tbl_location"."kladr_id"
Left JOIN "db_oks_zu"."tbl_position" ON
"db_oks_zu"."tbl_position"."object_id"="db_oks_zu"."tbl_objects_main"."object_id"
WHERE (reestr_base."invFiles"."placeStore" = '1') AND
(db_oks_zu."tbl_inventory"."organization_id" = '1');
When reestr_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):
QUERY PLAN
Hash Left Join (cost=76024.15..169664.40 rows=76919 width=429)
" Hash Cond: ((tbl_location.kladr_id)::text = (""kladrCache"".code)::text)"
-> Hash Left Join (cost=75094.18..165849.97 rows=76919 width=430)
" Hash Cond: (""invFiles"".id = tbl_location.parameter_id)"
-> Hash Left Join (cost=25195.02..42082.87 rows=886 width=106)
Hash Cond: (tbl_objects_main.object_id =
tbl_position.object_id)
-> Nested Loop (cost=21660.73..38535.64 rows=886 width=117)
-> Nested Loop (cost=21660.73..37302.47 rows=886
width=129)
-> Nested Loop (cost=21660.73..36846.12 rows=894
width=86)
-> Hash Join (cost=21660.73..36458.09
rows=908 width=86)
" Hash Cond: (tbl_rights.object_id =
""fileObjects"".""objectId"")"
-> Seq Scan on tbl_rights
(cost=0.00..12990.66 rows=479366 width=35)
-> Hash (cost=21654.66..21654.66
rows=486 width=51)
-> Hash Join
(cost=18174.09..21654.66 rows=486 width=51)
Hash Cond:
(tbl_inventory.register_id = tbl_register.register_id)
-> Bitmap Heap Scan on
tbl_inventory (cost=593.88..4016.58 rows=14136 width=16)
Recheck Cond:
((organization_id)::text = '1'::text)
-> Bitmap Index
Scan on tbl_inventory_idx1 (cost=0.00..590.35 rows=14136 width=0)
Index Cond:
((organization_id)::text = '1'::text)
-> Hash
(cost=17387.93..17387.93 rows=15382 width=67)
-> Hash Join
(cost=1358.24..17387.93 rows=15382 width=67)
" Hash Cond:
(tbl_register.object_id = ""fileObjects"".""objectId"")"
-> Seq Scan
on tbl_register (cost=0.00..12520.21 rows=447421 width=35)
-> Hash
(cost=1165.97..1165.97 rows=15382 width=32)
-> Hash
Join (cost=242.29..1165.97 rows=15382 width=32)
"
Hash Cond: (""fileObjects"".""fileId"" = ""invFiles"".id)"
"
-> Seq Scan on ""fileObjects"" (cost=0.00..610.30 rows=25530 width=32)"
-> Hash (cost=201.95..201.95 rows=3227 width=16)
"
-> Seq Scan on ""invFiles"" (cost=0.00..201.95 rows=3227 width=16)"
"
Filter: ((""placeStore"")::text = '1'::text)"
-> Index Scan using lnk_owners_right_id_key
on lnk_owners (cost=0.00..0.41 rows=1 width=32)
Index Cond: (lnk_owners.right_id =
tbl_rights.right_id)
-> Index Scan using powners_pkey on tbl_powners
(cost=0.00..0.50 rows=1 width=75)
Index Cond: (tbl_powners.powners_id =
lnk_owners.powners_id)
-> Index Scan using objects_main_pkey1 on
tbl_objects_main (cost=0.00..1.38 rows=1 width=36)
Index Cond: (tbl_objects_main.object_id =
tbl_rights.object_id)
-> Hash (cost=2157.46..2157.46 rows=110146 width=21)
-> Seq Scan on tbl_position (cost=0.00..2157.46
rows=110146 width=21)
-> Hash (cost=22516.85..22516.85 rows=464985 width=356)
-> Seq Scan on tbl_location (cost=0.00..22516.85 rows=464985
width=356)
-> Hash (cost=727.21..727.21 rows=16221 width=104)
" -> Seq Scan on ""kladrCache"" (cost=0.00..727.21 rows=16221
width=104)"
But if reestr_base."invFiles"."placeStore" and
db_oks_zu."tbl_inventory"."organization_id" compared with '3','4'... query
plan changed to the folowing (and real execution time is 70 seconds for 200
rows)
QUERY PLAN
Nested Loop Left Join (cost=2397.45..45626.60 rows=87 width=429)
-> Nested Loop Left Join (cost=2397.45..45599.74 rows=87 width=430)
" Join Filter: (tbl_location.parameter_id = ""invFiles"".id)"
-> Nested Loop Left Join (cost=2397.45..17270.57 rows=1 width=106)
-> Nested Loop (cost=2397.45..17262.28 rows=1 width=117)
-> Nested Loop (cost=2397.45..17260.88 rows=1
width=129)
-> Nested Loop (cost=2397.45..17260.37 rows=1
width=86)
-> Nested Loop (cost=2397.45..17259.95
rows=1 width=86)
-> Nested Loop
(cost=2397.45..17253.13 rows=22 width=86)
-> Hash Join
(cost=2397.45..17187.99 rows=225 width=54)
Hash Cond:
(tbl_rights.object_id = tbl_register.object_id)
-> Seq Scan on
tbl_rights (cost=0.00..12990.66 rows=479366 width=35)
-> Hash
(cost=2394.83..2394.83 rows=210 width=19)
-> Nested Loop
(cost=9.95..2394.83 rows=210 width=19)
-> Bitmap
Heap Scan on tbl_inventory (cost=9.95..675.15 rows=210 width=16)
Recheck
Cond: ((organization_id)::text = '12'::text)
->
Bitmap Index Scan on tbl_inventory_idx1 (cost=0.00..9.90 rows=210 width=0)
Index Cond: ((organization_id)::text = '12'::text)
-> Index Scan
using register_pkey on tbl_register (cost=0.00..8.18 rows=1 width=35)
Index
Cond: (tbl_register.register_id = tbl_inventory.register_id)
" -> Index Scan using
table3_pkey on ""fileObjects"" (cost=0.00..0.28 rows=1 width=32)"
" Index Cond:
(""fileObjects"".""objectId"" = tbl_rights.object_id)"
" -> Index Scan using
""invFiles_new_pkey"" on ""invFiles"" (cost=0.00..0.30 rows=1 width=16)"
" Index Cond: (""invFiles"".id =
""fileObjects"".""fileId"")"
" Filter:
((""invFiles"".""placeStore"")::text = '12'::text)"
-> Index Scan using lnk_owners_right_id_key
on lnk_owners (cost=0.00..0.41 rows=1 width=32)
Index Cond: (lnk_owners.right_id =
tbl_rights.right_id)
-> Index Scan using powners_pkey on tbl_powners
(cost=0.00..0.50 rows=1 width=75)
Index Cond: (tbl_powners.powners_id =
lnk_owners.powners_id)
-> Index Scan using objects_main_pkey1 on
tbl_objects_main (cost=0.00..1.38 rows=1 width=36)
Index Cond: (tbl_objects_main.object_id =
tbl_rights.object_id)
-> Index Scan using tbl_position_idx on tbl_position
(cost=0.00..8.28 rows=1 width=21)
Index Cond: (tbl_position.object_id =
tbl_objects_main.object_id)
-> Seq Scan on tbl_location (cost=0.00..22516.85 rows=464985
width=356)
" -> Index Scan using ""kladrCache_pkey"" on ""kladrCache""
(cost=0.00..0.28 rows=1 width=104)"
" Index Cond: ((""kladrCache"".code)::text =
(tbl_location.kladr_id)::text)"
What caused changing plan? Can i force to use one (first in expamples) plan
for any values in where clause?
Thanks
--
------
Regards,
Tatarnikov Alexander
