[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: [SQL] Different plan for one query problem
From: Tatarnikov Alexander <cankrus () gmail ! com>
Date: 2010-10-27 1:50:16
Message-ID: AANLkTi=zzSmk3AL1K4geEq-0sLi8tu6r3cALD=R8UHiH () mail ! gmail ! com
[Download RAW message or body]
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
[Attachment #3 (text/html)]
Hello!<br><br>I have following query:<br>SELECT \
<br>db_oks_zu."tbl_location"."full_address",<br>db_class."kla \
drCache"."region",<br>db_class."kladrCache"."mo",<br>
db_class."kladrCache"."city",<br>db_class."kladrCache".& \
quot;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_r \
ights"."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>
Left JOIN "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>Left JOIN "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>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):<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=76919 width=430)<br> " Hash Cond: \
(""invFiles"".id = tbl_location.parameter_id)"<br> \
-> Hash Left Join (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.09 rows=908 \
width=86)<br>" Hash Cond: \
(tbl_rights.object_id = \
""fileObjects"".""objectId"")"<br>
-> Seq Scan on tbl_rights \
(cost=0.00..12990.66 rows=479366 width=35)<br> \
-> Hash (cost=21654.66..21654.66 rows=486 width=51)<br> \
-> Hash Join (cost=18174.09..21654.66 rows=486 width=51)<br> Hash Cond: \
(tbl_inventory.register_id = tbl_register.register_id)<br> \
-> Bitmap Heap 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.35 rows=14136 \
width=0)<br> Index Cond: ((organization_id)::text = '1'::text)<br> \
-> Hash (cost=17387.93..17387.93 rows=15382 width=67)<br>
-> Hash Join \
(cost=1358.24..17387.93 rows=15382 width=67)<br>" \
Hash Cond: (tbl_register.object_id = \
""fileObjects"".""objectId"")"<br>
-> Seq Scan on \
tbl_register (cost=0.00..12520.21 rows=447421 width=35)<br> \
-> Hash (cost=1165.97..1165.97 rows=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.41 rows=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.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> \
-> 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.85 rows=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 changed to the folowing (and real execution \
time is 70 seconds for 200 rows)<br> <br clear="all">QUERY PLAN<br>Nested Loop Left \
Join (cost=2397.45..45626.60 rows=87 width=429)<br> -> Nested Loop Left Join \
(cost=2397.45..45599.74 rows=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=1 width=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.15 rows=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> \
Index Cond: (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_key on lnk_owners (cost=0.00..0.41 rows=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.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> \
" Index Cond: ((""kladrCache"".code)::text = \
(tbl_location.kladr_id)::text)"<br><br><br>What caused changing plan? Can i \
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>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic