hello
I have a question. I am using cloud context security in one of hour
websites, and i run into the problem that the dbms (postgresql) makes an
unfortunate queryplan. In stead of starting with finding the right film
node, it first finds all the film nodes that comply with the security
constraint in the where clouse.
Is there a way to influence this behavour?
Ernst
query:
SELECT film.number,
posrel.number,
aflevering.number,
timerel.number,
programma.number,
related.number,
websitesectie.number,
websitesectie.naam
FROM
install_film film,
install_posrel posrel,
install_aflevering aflevering,
install_timerel timerel,
install_programma programma,
install_insrel related,
install_websitesectie websitesectie
WHERE
film.number=48801
AND (film.number=posrel.dnumber
AND aflevering.number=posrel.snumber
AND posrel.dir<>1 AND posrel.rnumber=78)
AND (aflevering.number=timerel.dnumber
AND programma.number=timerel.snumber
AND timerel.dir<>1 AND timerel.rnumber=79)
AND (programma.number=related.dnumber
AND websitesectie.number=related.snumber
AND related.dir<>1 AND related.rnumber=77)
AND (film.owner<>'security'
AND posrel.owner<>'security'
AND aflevering.owner<>'security'
AND timerel.owner<>'security'
AND programma.owner<>'security'
AND related.owner<>'security'
AND websitesectie.owner<>'security')
query plan:
Hash Join (cost=168.57..196.09 rows=5 width=60) (actual
time=3544.948..3544.954 rows=1 loops=1)
Hash Cond: ("outer".number = "inner".snumber)
-> Seq Scan on install_websitesectie websitesectie
(cost=0.00..22.50 rows=995 width=36) (actual time=0.037..0.050 rows=3
loops=1)
Filter: ("owner" <> 'security'::text)
-> Hash (cost=168.57..168.57 rows=1 width=28) (actual
time=3544.207..3544.207 rows=0 loops=1)
-> Nested Loop (cost=17.11..168.57 rows=1 width=28) (actual
time=342.682..3544.183 rows=1 loops=1)
-> Nested Loop (cost=17.11..163.73 rows=1 width=32)
(actual time=342.608..3544.099 rows=1 loops=1)
-> Nested Loop (cost=17.11..158.89 rows=1
width=32) (actual time=342.508..3543.992 rows=1 loops=1)
Join Filter: ("outer".dnumber =
"inner".snumber)
-> Nested Loop (cost=17.11..141.78 rows=1
width=28) (actual time=166.445..732.146 rows=275 loops=1)
-> Hash Join (cost=17.11..136.94
rows=1 width=24) (actual time=140.690..589.031 rows=9656 loops=1)
Hash Cond: ("outer".dnumber =
"inner".snumber)
-> Append (cost=0.00..119.65
rows=35 width=12) (actual time=0.106..247.292 rows=19711 loops=1)
-> Index Scan using
insrel_index_rnumber on install_insrel related (cost=0.00..17.09 rows=5
width=12) (actual time=0.102..197.854 rows=19711 loops=1)
Index Cond: (rnumber
= 77)
Filter: ((dir <> 1)
AND ("owner" <> 'security'::text))
-> Index Scan using
publishrel_index_rnumber on install_publishrel related
(cost=0.00..17.09 rows=5 width=12) (actual time=0.036..0.036 rows=0
loops=1)
Index Cond: (rnumber
= 77)
Filter: ((dir <> 1)
AND ("owner" <> 'security'::text))
-> Index Scan using
rolerel_index_rnumber on install_rolerel related (cost=0.00..17.09
rows=5 width=12) (actual time=0.031..0.031 rows=0 loops=1)
Index Cond: (rnumber
= 77)
Filter: ((dir <> 1)
AND ("owner" <> 'security'::text))
-> Index Scan using
mediarel_index_rnumber on install_mediarel related (cost=0.00..17.09
rows=5 width=12) (actual time=0.038..0.038 rows=0 loops=1)
Index Cond: (rnumber
= 77)
Filter: ((dir <> 1)
AND ("owner" <> 'security'::text))
-> Index Scan using
posrel_index_rnumber on install_posrel related (cost=0.00..17.09 rows=5
width=12) (actual time=0.019..0.019 rows=0 loops=1)
Index Cond: (rnumber
= 77)
Filter: ((dir <> 1)
AND ("owner" <> 'security'::text))
-> Index Scan using
install_linksrechtsrel_rnumber_idx on install_linksrechtsrel related
(cost=0.00..17.09 rows=5 width=12) (actual time=0.020..0.020 rows=0
loops=1)
Index Cond: (rnumber
= 77)
Filter: ((dir <> 1)
AND ("owner" <> 'security'::text))
-> Index Scan using
install_rightsrel_rnumber_idx on install_rightsrel related
(cost=0.00..17.09 rows=5 width=12) (actual time=0.021..0.021 rows=0
loops=1)
Index Cond: (rnumber
= 77)
Filter: ((dir <> 1)
AND ("owner" <> 'security'::text))
-> Hash (cost=17.09..17.09
rows=5 width=12) (actual time=122.905..122.905 rows=0 loops=1)
-> Index Scan using
timerel_index_rnumber on install_timerel timerel (cost=0.00..17.09
rows=5 width=12) (actual time=0.180..96.475 rows=9407 loops=1)
Index Cond: (rnumber
= 79)
Filter: ((dir <> 1)
AND ("owner" <> 'security'::text))
-> Index Scan using
install_aflevering_pkey on install_aflevering aflevering
(cost=0.00..4.83 rows=1 width=4) (actual time=0.009..0.010 rows=0
loops=9656)
Index Cond: (aflevering.number =
"outer".dnumber)
Filter: ("owner" <>
'security'::text)
-> Index Scan using posrel_index_rnumber on
install_posrel posrel (cost=0.00..17.11 rows=1 width=12) (actual
time=9.075..10.215 rows=1 loops=275)
Index Cond: (rnumber = 78)
Filter: ((dir <> 1) AND ("owner" <>
'security'::text) AND (dnumber = 48801))
-> Index Scan using install_film_pkey on
install_film film (cost=0.00..4.83 rows=1 width=4) (actual
time=0.078..0.082 rows=1 loops=1)
Index Cond: (number = 48801)
Filter: ("owner" <> 'security'::text)
-> Index Scan using install_programma_pkey on
install_programma programma (cost=0.00..4.83 rows=1 width=4) (actual
time=0.045..0.049 rows=1 loops=1)
Index Cond: (programma.number = "outer".dnumber)
Filter: ("owner" <> 'security'::text)
Total runtime: 3546.626 ms
if i remove the security constraints the query plan becoms much better:
Hash Join (cost=168.47..193.53 rows=12 width=60) (actual
time=269.201..269.208 rows=1 loops=1)
Hash Cond: ("outer".number = "inner".snumber)
-> Seq Scan on install_websitesectie websitesectie
(cost=0.00..20.00 rows=1000 width=36) (actual time=0.012..0.020 rows=3
loops=1)
-> Hash (cost=168.47..168.47 rows=1 width=28) (actual
time=268.888..268.888 rows=0 loops=1)
-> Hash Join (cost=48.72..168.47 rows=1 width=28) (actual
time=25.352..268.880 rows=1 loops=1)
Hash Cond: ("outer".dnumber = "inner".snumber)
-> Append (cost=0.00..119.56 rows=35 width=12) (actual
time=0.070..221.955 rows=19711 loops=1)
-> Index Scan using insrel_index_rnumber on
install_insrel related (cost=0.00..17.08 rows=5 width=12) (actual
time=0.066..173.754 rows=19711 loops=1)
Index Cond: (rnumber = 77)
Filter: (dir <> 1)
-> Index Scan using publishrel_index_rnumber on
install_publishrel related (cost=0.00..17.08 rows=5 width=12) (actual
time=0.045..0.045 rows=0 loops=1)
Index Cond: (rnumber = 77)
Filter: (dir <> 1)
-> Index Scan using rolerel_index_rnumber on
install_rolerel related (cost=0.00..17.08 rows=5 width=12) (actual
time=0.033..0.033 rows=0 loops=1)
Index Cond: (rnumber = 77)
Filter: (dir <> 1)
-> Index Scan using mediarel_index_rnumber on
install_mediarel related (cost=0.00..17.08 rows=5 width=12) (actual
time=0.041..0.041 rows=0 loops=1)
Index Cond: (rnumber = 77)
Filter: (dir <> 1)
-> Index Scan using posrel_index_rnumber on
install_posrel related (cost=0.00..17.08 rows=5 width=12) (actual
time=0.020..0.020 rows=0 loops=1)
Index Cond: (rnumber = 77)
Filter: (dir <> 1)
-> Index Scan using
install_linksrechtsrel_rnumber_idx on install_linksrechtsrel related
(cost=0.00..17.08 rows=5 width=12) (actual time=0.024..0.024 rows=0
loops=1)
Index Cond: (rnumber = 77)
Filter: (dir <> 1)
-> Index Scan using install_rightsrel_rnumber_idx
on install_rightsrel related (cost=0.00..17.08 rows=5 width=12) (actual
time=0.024..0.024 rows=0 loops=1)
Index Cond: (rnumber = 77)
Filter: (dir <> 1)
-> Hash (cost=48.71..48.71 rows=4 width=24) (actual
time=8.251..8.251 rows=0 loops=1)
-> Nested Loop (cost=0.00..48.71 rows=4 width=24)
(actual time=7.336..8.243 rows=1 loops=1)
-> Nested Loop (cost=0.00..43.88 rows=1
width=20) (actual time=7.280..8.178 rows=1 loops=1)
-> Nested Loop (cost=0.00..39.03
rows=1 width=20) (actual time=7.216..8.107 rows=1 loops=1)
-> Nested Loop
(cost=0.00..34.20 rows=1 width=24) (actual time=7.162..8.047 rows=1
loops=1)
-> Index Scan using
posrel_index_rnumber on install_posrel posrel (cost=0.00..17.09 rows=1
width=12) (actual time=7.056..7.932 rows=1 loops=1)
Index Cond: (rnumber
= 78)
Filter: ((dir <> 1)
AND (dnumber = 48801))
-> Index Scan using
timerel_index_dnumber on install_timerel timerel (cost=0.00..17.09
rows=1 width=12) (actual time=0.076..0.081 rows=1 loops=1)
Index Cond:
(timerel.dnumber = "outer".snumber)
Filter: ((dir <> 1)
AND (rnumber = 79))
-> Index Scan using
install_aflevering_pkey on install_aflevering aflevering
(cost=0.00..4.82 rows=1 width=4) (actual time=0.038..0.041 rows=1
loops=1)
Index Cond:
(aflevering.number = "outer".dnumber)
-> Index Scan using install_film_pkey
on install_film film (cost=0.00..4.82 rows=2 width=4) (actual
time=0.047..0.050 rows=1 loops=1)
Index Cond: (number = 48801)
-> Index Scan using install_programma_pkey
on install_programma programma (cost=0.00..4.82 rows=1 width=4) (actual
time=0.035..0.039 rows=1 loops=1)
Index Cond: (programma.number =
"outer".snumber)
Total runtime: 270.612 ms
-----------
Ernst Bunders
tel: 035 6711653
_______________________________________________
Developers mailing list
[email protected]
http://lists.mmbase.org/mailman/listinfo/developers
