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)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
