strange view performance - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | strange view performance |
| Date | |
| Msg-id | BANLkTikuToMD4UyV7fMLMw=4xde2tVtT-g@mail.gmail.com Whole thread Raw |
| Responses |
Re: strange view performance
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-hackers |
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.107rows=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"
pgsql-hackers by date: