Netsted views working on same set of data on 8.1.21 but not on 8.4.4
От
Тема
Netsted views working on same set of data on 8.1.21 but not on 8.4.4
Дата
Msg-id
20100802120722.CWB89027@mailbox1.gnet.tn
Список
Дерево обсуждения
Netsted views working on same set of data on 8.1.21 but not on 8.4.4 <fathi.engineer@gnet.tn>
Re: Netsted views working on same set of data on 8.1.21 but not on 8.4.4 Tom Lane <tgl@sss.pgh.pa.us>
Hello,
I have migrated a 5 years old database from 8.1.21 running on top of centos 5.5 on an i686 to 8.4.4 running on top of centos on an x86_64 architecture.
I have some nested views and i am still able to get the results on 8.1.21 but when it comes to 8.4.4, I can get the results only from views that references other tables but the views references other views, I get an empty result set.
Here is the output of explain on the two different machines. Can someone help me please:
on 8.1.21:
Subquery Scan v_tableau_de_bord_nevralgique_dcg (cost=6726.72..6727.11 rows=31 width=328)
-> Sort (cost=6726.72..6726.80 rows=31 width=297)
Sort Key: v_articles_sous_min_reappro_a_central_sfa.code_reapprovisionnement, v_articles_sous_min_reappro_a_central_sfa.article
-> Nested Loop Left Join (cost=6394.14..6725.96 rows=31 width=297)
-> Nested Loop (cost=6394.14..6560.90 rows=28 width=224)
-> Sort (cost=6394.14..6394.21 rows=28 width=234)
Sort Key: v_situation_stock_sfa.nevralgique, v_situation_stock_sfa.total_ressources, v_situation_stock_sfa.code_reapprovisionnement
, v_situation_stock_sfa.article
-> HashAggregate (cost=6393.12..6393.47 rows=28 width=234)
-> Nested Loop Left Join (cost=6291.29..6392.42 rows=28 width=234)
-> Sort (cost=6291.29..6291.32 rows=13 width=82)
Sort Key: t_articles_stock.code_reapprovisionnement, t_articles_stock.article
-> HashAggregate (cost=6290.82..6291.05 rows=13 width=82)
Filter: (GREATEST(sum(quantite_stock), 0::real) < min_reappro)
-> Nested Loop (cost=3627.64..6290.49 rows=13 width=82)
-> Merge Join (cost=3627.64..6191.86 rows=18 width=74)
Merge Cond: ("outer".article = "inner".article)
-> Merge Left Join (cost=3624.06..6119.89 rows=27248 width=64)
Merge Cond: ("outer".article = "inner".article)
-> Index Scan using "Articles_SNCFT_pkey" on t_articles_stock (cost=0.00..2353.27 rows=
27248 width=60)
-> Sort (cost=3624.06..3636.47 rows=4963 width=8)
Sort Key: v_ressources_par_article.article
-> Subquery Scan v_ressources_par_article (cost=3158.11..3319.40 rows=4963 width=
8)
-> GroupAggregate (cost=3158.11..3269.77 rows=4963 width=44)
-> Sort (cost=3158.11..3170.51 rows=4963 width=44)
Sort Key: t_ressources.article, t_articles_stock.nom
-> Hash Left Join (cost=1140.60..2853.45 rows=4963 width=44)
Hash Cond: ("outer".article = "inner".article)
-> Seq Scan on t_ressources (cost=0.00..161.63 rows=4963
width=8)
-> Hash (cost=845.48..845.48 rows=27248 width=40)
-> Seq Scan on t_articles_stock (cost=0.00..845.48
rows=27248 width=40)
-> Sort (cost=3.58..3.63 rows=18 width=10)
Sort Key: t_valeurs_stock.article
-> Index Scan using "idx_NEVRALGIQUE_T_VALEURS_STOCK" on t_valeurs_stock (cost=0.00..3.
21 rows=18 width=10)
Index Cond: (nevralgique = true)
Filter: (nevralgique IS TRUE)
-> Index Scan using t_stocks_pkey on t_stocks (cost=0.00..5.47 rows=1 width=16)
Index Cond: ((t_stocks.article = "outer".article) AND ((t_stocks.magasin)::text = 'Central de S
FA'::text))
-> Index Scan using "idx_ARTICLE_T_STOCKS" on t_stocks (cost=0.00..7.74 rows=2 width=8)
Index Cond: ("outer".article = t_stocks.article)
-> Index Scan using "Valeurs_Stock_pkey" on t_valeurs_stock (cost=0.00..5.93 rows=1 width=8)
Index Cond: (t_valeurs_stock.article = "outer".article)
-> Index Scan using "idx_ARTICLE_T_RESSOURCES" on t_ressources (cost=0.00..5.88 rows=1 width=77)
Index Cond: (t_ressources.article = "outer".article)
on 8.4.4:
Subquery Scan v_tableau_de_bord_nevralgique_dcg (cost=2463.43..2463.45 rows=2 width=886)
-> Sort (cost=2463.43..2463.43 rows=2 width=775)
Sort Key: t_articles_stock.code_reapprovisionnement, t_articles_stock.article
-> Nested Loop Left Join (cost=2430.17..2463.42 rows=2 width=775)
-> Nested Loop (cost=2430.17..2446.84 rows=2 width=714)
-> GroupAggregate (cost=2430.17..2430.25 rows=2 width=724)
-> Sort (cost=2430.17..2430.17 rows=2 width=724)
Sort Key: t_valeurs_stock.nevralgique, (GREATEST((sum(t_ressources.quantite)), 0::real)), t_articles_stock.code_reapprovision
nement, t_articles_stock.article, t_articles_stock.nom, t_valeurs_stock.min_reappro, (GREATEST(sum(t_stocks.quantite_stock), 0::real)), t_stocks.unite_stock,
t_valeurs_stock.achat_a_la_demande
-> Nested Loop Left Join (cost=2419.08..2430.16 rows=2 width=724)
-> GroupAggregate (cost=2419.08..2419.13 rows=1 width=71)
Filter: (GREATEST(sum(t_stocks.quantite_stock), 0::real) < t_valeurs_stock.min_reappro)
-> Sort (cost=2419.08..2419.08 rows=1 width=71)
Sort Key: t_articles_stock.code_reapprovisionnement, t_articles_stock.article, t_valeurs_stock.min_reappro,
t_stocks.unite_stock, (sum(t_ressources.quantite)), t_valeurs_stock.nevralgique, t_valeurs_stock.achat_a_la_demande, t_articles_stock.nom
-> Nested Loop (cost=2223.57..2419.07 rows=1 width=71)
-> Merge Right Join (cost=2223.57..2410.77 rows=1 width=66)
Merge Cond: (t_ressources.article = t_articles_stock.article)
-> GroupAggregate (cost=2207.00..2327.33 rows=5348 width=40)
-> Sort (cost=2207.00..2220.37 rows=5348 width=40)
Sort Key: t_ressources.article, t_articles_stock.nom
-> Hash Left Join (cost=1350.70..1875.83 rows=5348 width=40)
Hash Cond: (t_ressources.article = t_articles_stock.article)
-> Seq Scan on t_ressources (cost=0.00..154.48 rows=5348 width=8)
-> Hash (cost=791.98..791.98 rows=27498 width=36)
-> Seq Scan on t_articles_stock (cost=0.00..791.98 rows=27498 width=3
6)
-> Sort (cost=16.57..16.58 rows=1 width=62)
Sort Key: t_articles_stock.article
-> Nested Loop (cost=0.00..16.56 rows=1 width=62)
-> Index Scan using "idx_NEVRALGIQUE_T_VALEURS_STOCK" on t_valeurs_stock (cost=0.
00..8.27 rows=1 width=10)
Index Cond: (nevralgique = true)
Filter: (nevralgique IS TRUE)
-> Index Scan using "idx_ARTICLE_T_ARTICLES_SNCFT" on t_articles_stock (cost=0.00
..8.28 rows=1 width=52)
Index Cond: (t_articles_stock.article = t_valeurs_stock.article)
-> Index Scan using t_stocks_pkey on t_stocks (cost=0.00..8.29 rows=1 width=13)
Index Cond: ((t_stocks.article = t_articles_stock.article) AND ((t_stocks.magasin)::text = 'Cen
tral de SFA'::text))
-> Index Scan using "idx_ARTICLE_T_STOCKS" on t_stocks (cost=0.00..11.00 rows=2 width=8)
Index Cond: (t_articles_stock.article = t_stocks.article)
-> Index Scan using "idx_ARTICLE_T_VALEURS_STOCK" on t_valeurs_stock (cost=0.00..8.28 rows=1 width=8)
Index Cond: (t_valeurs_stock.article = t_articles_stock.article)
-> Index Scan using "idx_ARTICLE_T_RESSOURCES" on t_ressources (cost=0.00..8.27 rows=1 width=65)
Index Cond: (t_ressources.article = t_articles_stock.article)
В списке pgsql-general по дате отправления