Vous n'êtes pas identifié(e).
Bonjour à tous,
J'ai une vue qui est très longue à interroger (environ 30 secondes) alors qu'en utilisant directement la requête de construction de la vue, la réponse est instantanée.
C'est un row_number() over(...) qui rend la lecture de la vue très longue (quand j'a fait une 2ème vue avec le row_number en moins, sa lecture est très rapide), alors qu'en requête direct c'est très rapide.
Savez-vous d'où peut venir ce problème ?
Merci
Dernière modification par thomasp (14/01/2016 16:53:34)
Hors ligne
Bonjour,
C'est une vue matérialisée ou une vue simple ?
Cordialement.
Dernière modification par ruizsebastien (14/01/2016 16:10:23)
Cordialement,
Sébastien.
Hors ligne
Non ce n'est pas une vue matérialisée.
Voici grosso modo ce qui est fait :
-- création de la vue :
create view mavue as
select user,
row_number() over(partition by iduser order by importance)
from (
select user,
case when year > 2010 then 1
when year between 2005 and 2010 then 2
when year < 2005 then 3
end as importance
from ma table) as t;
-- interrogation de la vue, TRES LONG :
select * from mavue where user = '123456';
-- interrogation via la requête de la vue, TRES RAPIDE :
select user,
row_number() over(parittion by iduser order by importance)
from (
select user,
case when year > 2010 then 1
when year between 2005 and 2010 then 2
when year < 2005 then 3 else 4 end as importance
from ma table) as t
where user = '123456';Dernière modification par thomasp (15/01/2016 17:16:01)
Hors ligne
Pouvez-vous montrer la définition de la vue, un EXPLAIN (ANALYZE, BUFFERS) de la vue ainsi que de la requête en direct ?
Julien.
https://rjuju.github.io/
Hors ligne
@ThomasP : Vous dites que les 2 requêtes ne sont pas identiques ?
Dernière modification par ruizsebastien (14/01/2016 17:00:10)
Cordialement,
Sébastien.
Hors ligne
@ruizsebastien : Les résultats sont indentiques, c'est le temps d'interrogation de la vue qui est à 30s contre résultat instantané via la requête.
J'ai l'impression que pour pouvoir effectuer la window function row_number(), la vue a besoin d'effectuer l'intégralité des résultats et ne filtre qu'ensuite sur le user contrairement à la requête qui filtre tout de suite.
Voici l'explain via la vue :
-- requête :
select * from v_test where iduser = '123456';
-- explain:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on v_test (cost=11676104.30..13758879.44 rows=122516 width=88) (actual time=121199.140..121199.140 rows=0 loops=1)
Filter: (v_test.iduser = '123456'::uuid)
Rows Removed by Filter: 934344
Buffers: shared hit=124590, temp read=524249 written=524249
-> WindowAgg (cost=11676104.30..13452588.98 rows=24503237 width=48) (actual time=120453.051..121084.875 rows=934344 loops=1)
Buffers: shared hit=124590, temp read=524249 written=524249
-> Sort (cost=11676104.30..11737362.39 rows=24503237 width=48) (actual time=120453.043..120628.260 rows=934344 loops=1)
Sort Key: k.iduser, (CASE WHEN ((0::double precision <= k.similarity) AND (k.similarity < 0.1::double precision) AND (k.nb_link >= 4)) THEN 0.90 WHEN ((0.1::double precision <= k.similarity) AND (k.similarity < 0.5::double
precision) AND (k.nb_link >= 4)) THEN 0.80 WHEN ((0.5::double precision <= k.similarity) AND (k.similarity < 1::double precision) AND (k.nb_link >= 4)) THEN 0.70 WHEN ((0::double precision <= k.similarity) AND (k.similarity < 0.1::double prec
ision) AND (k.nb_link = 3)) THEN 0.60 WHEN ((0.1::double precision <= k.similarity) AND (k.similarity < 0.5::double precision) AND (k.nb_link = 3)) THEN 0.55 WHEN ((0.5::double precision <= k.similarity) AND (k.similarity < 1::double precisio
n) AND (k.nb_link = 3)) THEN 0.50 WHEN ((0::double precision <= k.similarity) AND (k.similarity <= 1::double precision) AND (k.nb_link = 1)) THEN 0.50 ELSE 0.0 END)
Sort Method: external sort Disk: 60288kB
Buffers: shared hit=124590, temp read=524249 written=524249
-> Subquery Scan on k (cost=5243570.30..6407474.06 rows=24503237 width=48) (actual time=85447.404..118161.244 rows=934344 loops=1)
Buffers: shared hit=124590, temp read=516713 written=516713
-> GroupAggregate (cost=5243570.30..6162441.69 rows=24503237 width=36) (actual time=85447.390..117024.041 rows=934344 loops=1)
Group Key: p1.iduser, p2.iduser
Filter: (count(p1.iduser) >= 3)
Rows Removed by Filter: 20710574
Buffers: shared hit=124590, temp read=516713 written=516713
-> Sort (cost=5243570.30..5304828.40 rows=24503237 width=36) (actual time=85447.331..99172.607 rows=26463224 loops=1)
Sort Key: p1.iduser, p2.iduser
Sort Method: external merge Disk: 1189976kB
Buffers: shared hit=124590, temp read=516713 written=516713
-> Merge Join (cost=458597.79..896206.56 rows=24503237 width=36) (actual time=4440.491..17662.837 rows=26463224 loops=1)
Merge Cond: (p1.idplace = p2.idplace)
Join Filter: (p2.iduser <> p1.iduser)
Rows Removed by Join Filter: 1165656
Buffers: shared hit=124590, temp read=15494 written=15494
-> Sort (cost=229298.90..232223.81 rows=1169965 width=34) (actual time=2224.805..3029.659 rows=1165656 loops=1)
Sort Key: p1.idplace
Sort Method: external merge Disk: 50064kB
Buffers: shared hit=62295, temp read=7747 written=7747
-> Seq Scan on MaTable p1 (cost=0.00..79384.43 rows=1169965 width=34) (actual time=0.116..533.186 rows=1165656 loops=1)
Filter: (note <> 0)
Rows Removed by Filter: 195531
Buffers: shared hit=62295
-> Materialize (cost=229298.90..235148.72 rows=1169965 width=34) (actual time=2215.665..5624.362 rows=27628880 loops=1)
Buffers: shared hit=62295, temp read=7747 written=7747
-> Sort (cost=229298.90..232223.81 rows=1169965 width=34) (actual time=2215.659..2972.700 rows=1165656 loops=1)
Sort Key: p2.idplace
Sort Method: external merge Disk: 50064kB
Buffers: shared hit=62295, temp read=7747 written=7747
-> Seq Scan on MaTable p2 (cost=0.00..79384.43 rows=1169965 width=34) (actual time=0.081..526.648 rows=1165656 loops=1)
Filter: (note <> 0)
Rows Removed by Filter: 195531
Buffers: shared hit=62295
Planning time: 1.199 ms
Execution time: 121506.464 mset voici l'explain de la requête directe :
-- requête :
select *, row_number() over(parition by iduser order by trust desc)
from (
SELECT
iduser, iduser_same, similarity, nb_link,
CASE
WHEN 0.0 <= k.similarity AND k.similarity < 0.1 AND k.nb_link >= 4 THEN 0.90
WHEN 0.1 <= k.similarity AND k.similarity < 0.5 AND k.nb_link >= 4 THEN 0.80
WHEN 0.5 <= k.similarity AND k.similarity < 1.0 AND k.nb_link >= 4 then 0.70
WHEN 0.0 <= k.similarity AND k.similarity < 0.1 AND k.nb_link = 3 THEN 0.60
WHEN 0.1 <= k.similarity AND k.similarity < 0.5 AND k.nb_link = 3 THEN 0.55
WHEN 0.5 <= k.similarity AND k.similarity < 1.0 AND k.nb_link = 3 THEN 0.50
WHEN 0.0 <= k.similarity AND k.similarity <= 1.0 AND k.nb_link = 1 THEN 0.50
ELSE 0.0 END AS trust
FROM (
SELECT
p1.iduser AS iduser, p2.iduser AS iduser_same,
AVG(ABS(p1.note - p2.note)::float) AS similarity,
COUNT(p1.iduser) AS nb_link
FROM MaTable as p1
INNER JOIN MaTable as p2 ON p1.idplace = p2.idplace
WHERE p1.note != 0
AND p2.note != 0
AND p2.iduser != p1.iduser
GROUP BY p1.iduser, p2.iduser
HAVING COUNT(p1.iduser) >= 3) k) as t
where iduser = '123456';
-- explain :
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=1812.92..1852.47 rows=565 width=48) (actual time=0.050..0.050 rows=0 loops=1)
Buffers: shared hit=3
-> Sort (cost=1812.92..1814.33 rows=565 width=48) (actual time=0.048..0.048 rows=0 loops=1)
Sort Key: (CASE WHEN ((0::double precision <= k.similarity) AND (k.similarity < 0.1::double precision) AND (k.nb_links >= 4)) THEN 0.90 WHEN ((0.1::double precision <= k.similarity) AND (k.similarity < 0.5::double precision) AND (k.
nb_links >= 4)) THEN 0.80 WHEN ((0.5::double precision <= k.similarity) AND (k.similarity < 1::double precision) AND (k.nb_links >= 4)) THEN 0.70 WHEN ((0::double precision <= k.similarity) AND (k.similarity < 0.1::double precision) AND (k.nb_link
s = 3)) THEN 0.60 WHEN ((0.1::double precision <= k.similarity) AND (k.similarity < 0.5::double precision) AND (k.nb_links = 3)) THEN 0.55 WHEN ((0.5::double precision <= k.similarity) AND (k.similarity < 1::double precision) AND (k.nb_links =
3)) THEN 0.50 WHEN ((0::double precision <= k.similarity) AND (k.similarity <= 1::double precision) AND (k.nb_links = 1)) THEN 0.50 ELSE 0.0 END)
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=3
-> Subquery Scan on k (cost=1772.97..1787.09 rows=565 width=48) (actual time=0.032..0.032 rows=0 loops=1)
Buffers: shared hit=3
-> HashAggregate (cost=1772.97..1781.44 rows=565 width=36) (actual time=0.031..0.031 rows=0 loops=1)
Group Key: p1.iduser, p2.iduser
Filter: (count(p1.iduser) >= 3)
Buffers: shared hit=3
-> Nested Loop (cost=9.33..1761.67 rows=565 width=36) (actual time=0.027..0.027 rows=0 loops=1)
Buffers: shared hit=3
-> Bitmap Heap Scan on MaTable p1 (cost=4.67..130.98 rows=27 width=34) (actual time=0.026..0.026 rows=0 loops=1)
Recheck Cond: (iduser = '123456'::uuid)
Filter: (note <> 0)
Buffers: shared hit=3
-> Bitmap Index Scan on ix_poi_note_engine_iduser (cost=0.00..4.67 rows=32 width=0) (actual time=0.019..0.019 rows=0 loops=1)
Index Cond: (iduser = '123456'::uuid)
Buffers: shared hit=3
-> Bitmap Heap Scan on MaTable p2 (cost=4.66..60.28 rows=12 width=34) (never executed)
Recheck Cond: (idplace = p1.idplace)
Filter: ((note <> 0) AND (iduser <> p1.iduser))
-> Bitmap Index Scan on pk_MaTable (cost=0.00..4.66 rows=14 width=0) (never executed)
Index Cond: (idplace = p1.idplace)
Planning time: 1.139 ms
Execution time: 0.285 msDernière modification par thomasp (15/01/2016 17:05:02)
Hors ligne
Pouvez-vous fournir la définition de la vue ? À priori quand vous utilisez la vue le WHERE iduser = '123456' n'est pas poussé dans la requête.
Julien.
https://rjuju.github.io/
Hors ligne
La définition de la vue est exactement comme la requête (sans bien sûr la clause sur le user) :
create view v_test as
select *, row_number() over(parition by iduser order by trust desc)
from (
SELECT
iduser, iduser_same, similarity, nb_link,
CASE
WHEN 0.0 <= k.similarity AND k.similarity < 0.1 AND k.nb_link >= 4 THEN 0.90
WHEN 0.1 <= k.similarity AND k.similarity < 0.5 AND k.nb_link >= 4 THEN 0.80
WHEN 0.5 <= k.similarity AND k.similarity < 1.0 AND k.nb_link >= 4 then 0.70
WHEN 0.0 <= k.similarity AND k.similarity < 0.1 AND k.nb_link = 3 THEN 0.60
WHEN 0.1 <= k.similarity AND k.similarity < 0.5 AND k.nb_link = 3 THEN 0.55
WHEN 0.5 <= k.similarity AND k.similarity < 1.0 AND k.nb_link = 3 THEN 0.50
WHEN 0.0 <= k.similarity AND k.similarity <= 1.0 AND k.nb_link = 1 THEN 0.50
ELSE 0.0 END AS trust
FROM (
SELECT
p1.iduser AS iduser, p2.iduser AS iduser_same,
AVG(ABS(p1.note - p2.note)::float) AS similarity,
COUNT(p1.iduser) AS nb_link
FROM MaTable as p1
INNER JOIN MaTable as p2 ON p1.idplace = p2.idplace
WHERE p1.note != 0
AND p2.note != 0
AND p2.iduser != p1.iduser
GROUP BY p1.iduser, p2.iduser
HAVING COUNT(p1.iduser) >= 3) k) as t);Dernière modification par thomasp (15/01/2016 17:04:19)
Hors ligne
Je suis sceptique sur le "row_number" sans parenthèse et le "parition by", de même que vous sélectionnez des colonnes qui n'existent pas (iduser_same n'est pas présent dans le sous select). Étant donné que les requêtes que vous fournissez depuis le début ne sont pas syntaxiquement correctes, je vois mal comment vous pouvez donner vos chiffres.
Julien.
https://rjuju.github.io/
Hors ligne
oui vous avez raison, c'est parce que j'ai changé (un peu trop vite) les noms pour que ce soit plus lisible. J'ai édité le code précédent pour avoir les noms qui collent bien. Merci
Hors ligne
Et votre requête n'est toujours pas correcte.
Julien.
https://rjuju.github.io/
Hors ligne