strange view performance

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"