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
centoson 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
resultsonly 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.40rows=4963 width=
8)
-> GroupAggregate
(cost=3158.11..3269.77rows=4963 width=44)
-> Sort (cost=3158.11..3170.51
rows=4963width=44)
Sort Key:
t_ressources.article,t_articles_stock.nom
-> Hash Left Join
(cost=1140.60..2853.45rows=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.48rows=27248 width=40)
-> Seq Scan
ont_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.47rows=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=5348width=40)
Hash Cond: (t_ressources.article =
t_articles_stock.article)
-> Seq Scan on t_ressources
(cost=0.00..154.48rows=5348 width=8)
-> Hash (cost=791.98..791.98
rows=27498width=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.29rows=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=2width=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)