FAQ
Hello

I am solving a strange situation, where using a view is slower than
using same tables directly.

The view is defined as

CREATE VIEW v1 AS
SELECT *
FROM A
LEFT JOIN B
LEFT JOIN C
LEFT JOIN D

and query is
SELECT *
FROM T
LEFT JOIN v

this query is slower than:

SELECT *
FROM T
LEFT JOIN A
LEFT JOIN B
LEFT JOIN C
LEFT JOIN D

Is there a some reason for this behave?

set enable_hashjoin to off;
set work_mem to '10MB';
set JOIN_COLLAPSE_LIMIT to 12;
set geqo_threshold to 12;
explain analyze select * from v_vypis_parcel where par_id = 1396907206

/******************************
"Nested Loop Left Join (cost=0.00..50.73 rows=1 width=399) (actual
time=0.655..0.914 rows=1 loops=1)"
" Join Filter: (katastr_uzemi.kod = parcely.katuze_kod)"
" -> Nested Loop Left Join (cost=0.00..43.79 rows=1 width=349)
(actual time=0.627..0.655 rows=1 loops=1)"
" Join Filter: (casti_obci.obce_kod = obce.kod)"
" -> Nested Loop Left Join (cost=0.00..39.29 rows=1
width=304) (actual time=0.461..0.487 rows=1 loops=1)"
" Join Filter: (casti_obci.kod = budovy.caobce_kod)"
" -> Nested Loop Left Join (cost=0.00..31.83 rows=1
width=254) (actual time=0.183..0.208 rows=1 loops=1)"
" Join Filter: (parcely.zdpaze_kod = zdroje_parcel_ze.kod)"
" -> Nested Loop Left Join (cost=0.00..30.77
rows=1 width=191) (actual time=0.175..0.199 rows=1 loops=1)"
" Join Filter: (zp_vyuziti_poz.kod =
parcely.zpvypa_kod)"
" -> Nested Loop Left Join
(cost=0.00..29.14 rows=1 width=135) (actual time=0.130..0.153 rows=1
loops=1)"
" -> Nested Loop Left Join
(cost=0.00..28.76 rows=1 width=142) (actual time=0.119..0.139 rows=1
loops=1)"
" Join Filter: (t_budov.kod =
budovy.typbud_kod)"
" -> Nested Loop Left Join
(cost=0.00..27.62 rows=1 width=139) (actual time=0.111..0.124 rows=1
loops=1)"
" Join Filter:
(t_bud_ii.kod = casti_budov.typbud_kod)"
" -> Nested Loop Left Join
(cost=0.00..26.49 rows=1 width=136) (actual time=0.096..0.107 rows=1
loops=1)"
" Join Filter:
(d_pozemku.kod = parcely.drupoz_kod)"
" -> Nested Loop
Left Join (cost=0.00..25.24 rows=1 width=131) (actual
time=0.071..0.079 rows=1 loops=1)"
" -> Nested
Loop Left Join (cost=0.00..16.95 rows=1 width=127) (actual
time=0.057..0.061 rows=1 loops=1)"
" ->
Nested Loop Left Join (cost=0.00..16.61 rows=1 width=113) (actual
time=0.049..0.053 rows=1 loops=1)"
"
-> Index Scan using par_pk on parcely (cost=0.00..8.31 rows=1
width=84) (actual time=0.028..0.029 rows=1 loops=1)"
"
Index Cond: (id = 1396907206::numeric)"
"
-> Index Scan using bud_pk on budovy (cost=0.00..8.28 rows=1
width=40) (actual time=0.014..0.015 rows=1 loops=1)"
"
Index Cond: (budovy.id = parcely.bud_id)"
" ->
Index Scan using i_casti_budov_budid on casti_budov (cost=0.00..0.30
rows=3 width=25) (actual time=0.005..0.005 rows=0 loops=1)"
"
Index Cond: (casti_budov.bud_id = budovy.id)"
" -> Index
Scan using tel_pk on telesa (cost=0.00..8.28 rows=1 width=15) (actual
time=0.011..0.012 rows=1 loops=1)"
" Index
Cond: (parcely.tel_id = telesa.id)"
" -> Seq Scan on
d_pozemku (cost=0.00..1.11 rows=11 width=19) (actual
time=0.004..0.012 rows=11 loops=1)"
" -> Seq Scan on t_budov
t_bud_ii (cost=0.00..1.06 rows=6 width=17) (actual time=0.002..0.005
rows=6 loops=1)"
" -> Seq Scan on t_budov
(cost=0.00..1.06 rows=6 width=17) (actual time=0.001..0.005 rows=6
loops=1)"
" -> Index Scan using tel_pk on telesa
tel_bud (cost=0.00..0.37 rows=1 width=15) (actual time=0.009..0.010
rows=1 loops=1)"
" Index Cond: (budovy.tel_id = tel_bud.id)"
" -> Seq Scan on zp_vyuziti_poz
(cost=0.00..1.28 rows=28 width=70) (actual time=0.002..0.020 rows=28
loops=1)"
" -> Seq Scan on zdroje_parcel_ze
(cost=0.00..1.03 rows=3 width=70) (actual time=0.002..0.004 rows=3
loops=1)"
" -> Seq Scan on casti_obci (cost=0.00..4.98 rows=198
width=58) (actual time=0.002..0.128 rows=198 loops=1)"
" -> Seq Scan on obce (cost=0.00..3.11 rows=111 width=53)
(actual time=0.002..0.076 rows=111 loops=1)"
" -> Seq Scan on katastr_uzemi (cost=0.00..4.72 rows=172 width=54)
(actual time=0.002..0.111 rows=172 loops=1)"
"Total runtime: 1.341 ms"
*************************************************/

set enable_hashjoin to off;
set work_mem to '10MB';
set JOIN_COLLAPSE_LIMIT to 12;
set geqo_threshold to 12;
explain analyze select * from v_vypis_parcel_puvodni where par_id = 1396907206

/*********************************
"Nested Loop Left Join (cost=10001.97..12147.14 rows=1 width=415)
(actual time=469.389..519.108 rows=1 loops=1)"
" Join Filter: (katastr_uzemi.kod = parcely.katuze_kod)"
" -> Nested Loop Left Join (cost=10001.97..12140.19 rows=1
width=365) (actual time=469.338..518.813 rows=1 loops=1)"
" -> Nested Loop Left Join (cost=10001.97..12139.82 rows=1
width=372) (actual time=469.319..518.790 rows=1 loops=1)"
" Join Filter: (d_pozemku.kod = parcely.drupoz_kod)"
" -> Nested Loop Left Join (cost=10001.97..12138.57
rows=1 width=367) (actual time=469.288..518.754 rows=1 loops=1)"
" Join Filter: (parcely.zdpaze_kod = zdroje_parcel_ze.kod)"
" -> Nested Loop Left Join
(cost=10001.97..12137.50 rows=1 width=304) (actual
time=469.274..518.738 rows=1 loops=1)"
" -> Nested Loop Left Join
(cost=10001.97..12137.14 rows=1 width=259) (actual
time=469.263..518.726 rows=1 loops=1)"
" Join Filter: (zp_vyuziti_poz.kod =
parcely.zpvypa_kod)"
" -> Nested Loop Left Join
(cost=10001.97..12135.51 rows=1 width=203) (actual
time=469.193..518.654 rows=1 loops=1)"
" -> Nested Loop Left Join
(cost=10001.97..12135.23 rows=1 width=153) (actual
time=469.188..518.647 rows=1 loops=1)"
" -> Nested Loop Left Join
(cost=10001.97..12126.93 rows=1 width=149) (actual
time=469.142..518.598 rows=1 loops=1)"
" Join Filter:
(budovy.id = parcely.bud_id)"
" -> Index Scan
using par_pk on parcely (cost=0.00..8.31 rows=1 width=84) (actual
time=0.018..0.026 rows=1 loops=1)"
" Index Cond:
(id = 1396907206::numeric)"
" -> Merge Right
Join (cost=10001.97..11156.52 rows=76968 width=76) (actual
time=295.292..461.640 rows=77117 loops=1)"
" Merge Cond:
(t_budov.kod = budovy.typbud_kod)"
" -> Sort
(cost=1.14..1.15 rows=6 width=17) (actual time=0.042..0.046 rows=6
loops=1)"
" Sort
Key: t_budov.kod"
" Sort
Method: quicksort Memory: 25kB"
" -> Seq
Scan on t_budov (cost=0.00..1.06 rows=6 width=17) (actual
time=0.006..0.011 rows=6 loops=1)"
" -> Sort
(cost=10000.83..10193.25 rows=76968 width=53) (actual
time=295.224..346.550 rows=77117 loops=1)"
" Sort
Key: budovy.typbud_kod"
" Sort
Method: quicksort Memory: 9112kB"
" ->
Merge Left Join (cost=1.07..3754.12 rows=76968 width=53) (actual
time=0.099..204.628 rows=77117 loops=1)"
"
Merge Cond: (budovy.id = casti_budov.bud_id)"
"
-> Index Scan using bud_pk on budovy (cost=0.00..3500.36 rows=76968
width=40) (actual time=0.068..78.373 rows=76968 loops=1)"
"
-> Materialize (cost=1.07..58.37 rows=238 width=28) (actual
time=0.025..3.243 rows=238 loops=1)"
"
-> Nested Loop Left Join (cost=1.07..55.99 rows=238 width=28)
(actual time=0.021..2.897 rows=238 loops=1)"
"
Join Filter: (t_bud_ii.kod = casti_budov.typbud_kod)"
"
-> Index Scan using i_casti_budov_budid on casti_budov
(cost=0.00..22.79 rows=238 width=25) (actual time=0.009..0.269
rows=238 loops=1)"
"
-> Materialize (cost=1.07..1.13 rows=6 width=17) (actual
time=0.001..0.004 rows=6 loops=238)"
"
-> Seq Scan on t_budov t_bud_ii (cost=0.00..1.06
rows=6 width=17) (actual time=0.002..0.012 rows=6 loops=1)"
" -> Index Scan using
tel_pk on telesa (cost=0.00..8.28 rows=1 width=15) (actual
time=0.031..0.032 rows=1 loops=1)"
" Index Cond:
(parcely.tel_id = public.telesa.id)"
" -> Index Scan using caob_pk on
casti_obci (cost=0.00..0.27 rows=1 width=58) (actual
time=0.002..0.002 rows=0 loops=1)"
" Index Cond:
(casti_obci.kod = budovy.caobce_kod)"
" -> Seq Scan on zp_vyuziti_poz
(cost=0.00..1.28 rows=28 width=70) (actual time=0.004..0.026 rows=28
loops=1)"
" -> Index Scan using ob_pk on obce
(cost=0.00..0.35 rows=1 width=53) (actual time=0.002..0.002 rows=0
loops=1)"
" Index Cond: (casti_obci.obce_kod = obce.kod)"
" -> Seq Scan on zdroje_parcel_ze
(cost=0.00..1.03 rows=3 width=70) (actual time=0.002..0.004 rows=3
loops=1)"
" -> Seq Scan on d_pozemku (cost=0.00..1.11 rows=11
width=19) (actual time=0.001..0.009 rows=11 loops=1)"
" -> Index Scan using tel_pk on telesa (cost=0.00..0.37
rows=1 width=15) (actual time=0.014..0.016 rows=1 loops=1)"
" Index Cond: (budovy.tel_id = public.telesa.id)"
" -> Seq Scan on katastr_uzemi (cost=0.00..4.72 rows=172 width=54)
(actual time=0.002..0.112 rows=172 loops=1)"
"Total runtime: 521.921 ms"

Search Discussions

  • Tom Lane at May 1, 2011 at 10:30 pm

    Pavel Stehule writes:
    I am solving a strange situation, where using a view is slower than
    using same tables directly.
    The view is defined as
    CREATE VIEW v1 AS
    SELECT *
    FROM A
    LEFT JOIN B
    LEFT JOIN C
    LEFT JOIN D
    and query is
    SELECT *
    FROM T
    LEFT JOIN v
    this query is slower than:
    SELECT *
    FROM T
    LEFT JOIN A
    LEFT JOIN B
    LEFT JOIN C
    LEFT JOIN D
    Is there a some reason for this behave?
    Well, they don't necessarily mean the same thing --- these are only
    logically equivalent if the left joins all commute, which would depend
    on the ON conditions.
    [ EXPLAIN outputs ]
    But I also notice that you are using collapse/geqo limits of 12 for
    queries that involve 13 base relations, so that'd mean that syntactic
    differences could lead to plan differences too.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMay 1, '11 at 9:37a
activeMay 1, '11 at 10:30p
posts2
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Tom Lane: 1 post Pavel Stehule: 1 post

People

Translate

site design / logo © 2022 Grokbase