Vous n'êtes pas identifié(e).
- Contributions : Récentes | Sans réponse
Pages : 1
#1 Re : Général » Recherche fulltext : quel lexeme a été trouvé? » 27/05/2016 13:29:37
J'avais regardé aussi du côté de ts_debug, mais ça ne convient pas, cette fonction me donne les infos de dictionnaire, mais ne me dira pas si mon document a été trouvé grâce à "restaurant" ou "chinois".
Merci tout de même!
#2 Re : Général » Recherche fulltext : quel lexeme a été trouvé? » 27/05/2016 11:37:18
C'est en effet la solution de remplacement que j'ai trouvée, je fait une boucle sur tous les mots de ma phrase pour voir lequel correspond, mais c'est dommage, ça fait faire autant de recherches qu'il n'y a de mot dans ma phrase au lieu de n'en faire qu'une comme indiqué dans le premier post
#3 Général » Recherche fulltext : quel lexeme a été trouvé? » 26/05/2016 11:20:04
- thomasp
- Réponses : 4
Bonjour à tous,
Je fais de la recherche full texte sur des chaînes de plusieurs mots, j'aimerais savoir lequel des lexèmes que je recherche a matché avec le résultat qui m'est renvoyé. Un exemple si ce n'est pas clair:
J'ai une table avec un champs tsvector, dans lequel je recherche la chaîne "restaurant chinois":
ma_table
|ma_colonne TEXT | mon_tsvector TSVECTOR|
-----------------------------------------
|restauration | restaur |
-----------------------------------------
select ma_colonne
from ma_table
where mon_tsvector @@ to_tsquery('restaurant|chinois');
--résultats:
ma_colonne
---------------
restaurationJ'ai bien trouvé grâce à la recherche full text la ligne qui contenait "restauration", mais comment savoir si "restauration" a matché avec "restaurant" ou "chinois"?
J'ai cherché un peu du côté de ts_headline, mais ça ne fait pas tout à fait ce que je veux, et ça n'utilise pas le tsvector donc c'est beaucoup plus long!
Merci!
#4 Re : Optimisation » Premier update très long, mais les suivants sont instantannés » 09/05/2016 11:39:50
Bonjour,
Tout à fait, c'est une table créée par un script puis remplie avec l'instruction COPY depuis des fichiers csv. C'est fait quotidiennement dans le cadre d'une migration de données depuis SQL Server vers Postgresql.
- La version du serveur DB est : PostgreSQL 9.4.7 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
- L'autovaccum est activé, vous tourverez ci-dessous les différentes options,
- J'ai un fillfactor=70 indiqué pour la table mytable.
- J'ai fait le vacuum analyze sur mytable, les résultats des 2 explains sont en fin de message.
Merci encore pour votre aide !
-- VALEURS AUTOVACUUM :
-- select name, setting, context, vartype,source,min_val,max_val,enumvals,boot_val,reset_val
-- from pg_settings
-- where name like 'autovacuum%';
name | setting | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val
-------------------------------------+-----------+------------+---------+---------+---------+------------+----------+-----------+-----------
autovacuum | on | sighup | bool | default | [null] | [null] | [null] | on | on
autovacuum_analyze_scale_factor | 0.1 | sighup | real | default | 0 | 100 | [null] | 0.1 | 0.1
autovacuum_analyze_threshold | 50 | sighup | integer | default | 0 | 2147483647 | [null] | 50 | 50
autovacuum_freeze_max_age | 200000000 | postmaster | integer | default | 100000 | 2000000000 | [null] | 200000000 | 200000000
autovacuum_max_workers | 3 | postmaster | integer | default | 1 | 8388607 | [null] | 3 | 3
autovacuum_multixact_freeze_max_age | 400000000 | postmaster | integer | default | 10000 | 2000000000 | [null] | 400000000 | 400000000
autovacuum_naptime | 60 | sighup | integer | default | 1 | 2147483 | [null] | 60 | 60
autovacuum_vacuum_cost_delay | 20 | sighup | integer | default | -1 | 100 | [null] | 20 | 20
autovacuum_vacuum_cost_limit | -1 | sighup | integer | default | -1 | 10000 | [null] | -1 | -1
autovacuum_vacuum_scale_factor | 0.2 | sighup | real | default | 0 | 100 | [null] | 0.2 | 0.2
autovacuum_vacuum_threshold | 50 | sighup | integer | default | 0 | 2147483647 | [null] | 50 | 50
autovacuum_work_mem | -1 | sighup | integer | default | -1 | 2147483647 | [null] | -1 | -1-- PREMIER UPDATE
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Update on mytable (cost=5.54..501.43 rows=127 width=84) (actual time=197.794..197.794 rows=0 loops=1)
Buffers: shared hit=10917 read=2238 dirtied=2627
-> Bitmap Heap Scan on mytable (cost=5.54..501.43 rows=127 width=84) (actual time=1.062..26.874 rows=874 loops=1)
Recheck Cond: (id_object = 'd9700727-bf42-4891-9541-2e3c8cb9dc8e'::uuid)
Heap Blocks: exact=873
Buffers: shared hit=821 read=62
-> Bitmap Index Scan on ix_mytable_id_object_id_user (cost=0.00..5.51 rows=127 width=0) (actual time=0.701..0.701 rows=874 loops=1)
Index Cond: (id_object = 'd9700727-bf42-4891-9541-2e3c8cb9dc8e'::uuid)
Buffers: shared hit=1 read=9
Planning time: 0.581 ms
Execution time: 197.858 ms
(11 rows)
Time: 211,457 ms-- DEUXIEME UPDATE
explain (analyze,buffers) update mytable set weight=weight+1 where id_object = 'd9700727-bf42-4891-9541-2e3c8cb9dc8e';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Update on mytable (cost=5.54..501.43 rows=127 width=84) (actual time=151.729..151.729 rows=0 loops=1)
Buffers: shared hit=13145 read=6 dirtied=7
-> Bitmap Heap Scan on mytable (cost=5.54..501.43 rows=127 width=84) (actual time=0.693..4.974 rows=874 loops=1)
Recheck Cond: (id_object = 'd9700727-bf42-4891-9541-2e3c8cb9dc8e'::uuid)
Heap Blocks: exact=873
Buffers: shared hit=893 dirtied=1
-> Bitmap Index Scan on ix_mytable_id_object_id_user (cost=0.00..5.51 rows=127 width=0) (actual time=0.427..0.427 rows=1748 loops=1)
Index Cond: (id_object = 'd9700727-bf42-4891-9541-2e3c8cb9dc8e'::uuid)
Buffers: shared hit=20
Planning time: 0.154 ms
Execution time: 151.824 ms
(11 rows)
Time: 157,755 msNB : ce n'est pas le même index que précédemment qui est utilisé, cette fois c'est ix_mytable_id_object_id_user, qui est le même que la clé primaire (id_user, id_object) mais avec les colonnes inversées (on accède à cette table aussi bien par le user que par l'object, mais je ne sais pas si cet index est indispensable).
#5 Re : Optimisation » Premier update très long, mais les suivants sont instantannés » 02/05/2016 15:54:02
oups, donc ok, le planning time n'a rien à voir...
Voici 2 explain faits d'affilée sur le même id_object:
-- PREMIER UPDATE LENT
explain (analyze,buffers) update mytable set value = value+1 where id_object = '8b41d75c-4371-4414-9c85-e84d43bb319a';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Update on mytable (cost=5.38..487.08 rows=122 width=67) (actual time=2402.480..2402.480 rows=0 loops=1)
Buffers: shared hit=24186 read=1395 dirtied=3267
-> Bitmap Heap Scan on mytable (cost=5.38..487.08 rows=122 width=67) (actual time=1.009..81.692 rows=1215 loops=1)
Recheck Cond: (id_object = '8b41d75c-4371-4414-9c85-e84d43bb319a'::uuid)
Heap Blocks: exact=1249
Buffers: shared hit=1273 dirtied=1006
-> Bitmap Index Scan on ix_mytable_id_object (cost=0.00..5.35 rows=122 width=0) (actual time=0.673..0.673 rows=4864 loops=1)
Index Cond: (id_object = '8b41d75c-4371-4414-9c85-e84d43bb319a'::uuid)
Buffers: shared hit=24
Planning time: 0.149 ms
Execution time: 2402.540 ms
(11 rows)
Time: 2414,219 ms-- DEUXIEME UPDATE RAPIDE
explain (analyze,buffers) update mytable set value = value-1 where id_object = '8b41d75c-4371-4414-9c85-e84d43bb319a';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Update on mytable (cost=5.38..487.08 rows=122 width=67) (actual time=42.607..42.607 rows=0 loops=1)
Buffers: shared hit=22592 read=8 dirtied=11
-> Bitmap Heap Scan on mytable (cost=5.38..487.08 rows=122 width=67) (actual time=1.253..6.887 rows=1215 loops=1)
Recheck Cond: (id_object = '8b41d75c-4371-4414-9c85-e84d43bb319a'::uuid)
Heap Blocks: exact=1256
Buffers: shared hit=1284
-> Bitmap Index Scan on ix_mytable_id_object (cost=0.00..5.35 rows=122 width=0) (actual time=0.943..0.943 rows=6079 loops=1)
Index Cond: (id_object = '8b41d75c-4371-4414-9c85-e84d43bb319a'::uuid)
Buffers: shared hit=28
Planning time: 0.172 ms
Execution time: 42.663 ms
(11 rows)
Time: 44,534 msà noter que le "ix_mytable_id_object" n'est pas la clé primaire, c'est l'index dédié à l'id_object.
Merci pour votre aide!
#6 Re : Optimisation » Premier update très long, mais les suivants sont instantannés » 02/05/2016 15:38:20
Vous pouvez également vérifier si les accès se font ou non dans le cache
En effet entre le 1er explain et les suivants, le share read passe de plusieurs milliers à quelques unités, tout le reste est identique. Merci, c'est donc bien le cache qui fait la différence. J'ai essayé le pg_prewarm pour mettre la table en mémoire (j'ai essayé aussi avec l'index sur l'id_object) mais ça n'a rien changé, savez-vous s'il y a une autre solution?
à noter que le planning time indique toujours quelques millisecondes, même pour les 1ères exécutions, il ne détecte pas que la 1ère requête va être très longue.
#7 Re : Optimisation » Premier update très long, mais les suivants sont instantannés » 02/05/2016 15:32:25
Bonjour,
C'est normal le premier coup les blocs ne sont pas dans le cache. Suite au where qui va rechercher le bon id_object, cet id_object est dans le cache. D'où la rapidité du deuxième update.
Si vous êtes en version 9.4 ou plus vous pouvez tenter d'utiliser pg_prewarm.
http://docs.postgresql.fr/9.4/pgprewarm.htmlCordialement.
En effet j'ai déjà essayé le pg_prewarm, sur la table et sur l'index directement, mais ça n'a rien changé
#8 Optimisation » Premier update très long, mais les suivants sont instantannés » 02/05/2016 14:48:00
- thomasp
- Réponses : 8
Bonjour à tous,
Dans une procédure stockée, je fais un update très basique sur une table :
mytable (
id_user UUID,
id_object UUID,
value FLOAT,
CONSTRAINT pk_user_poi PRIMARY KEY (id_user, id_object))
-- requête :
update mytable set
value = [nouvelle valeur]
where id_object = [object à traiter]Mon problème est que, pour un même id_object, le premier update est très long (varie entre 3 et 8 secondes!), alors que les suivants, sur le même id_object, ne prend que quelques millisecondes. Dès que je change d'id_object, le 1er prends toujours quelques secondes et pas les suivants.
- J'ai essayé de rajouter un index dédié à l'id_object (en plus de la clé primaire sur le couple user/object), mais ça n'a rien changé.
- Quand je reconstruis l'index, le 1er update est cette fois très rapide, mais la reconstruction d'index est trop longue pour que je l'utilise à chaque fois.
Savez-vous pourquoi il y a une telle différence entre le 1er update et les suivants pour un même id_object? Y a t'il une solution à ce problème de 1er update?
- mytable contient environ 7.000.000 de lignes
- le nombre d’occurrences d'id_object varie entre 1 et 5.000, pour environ 500.000 id_object différents
De plus, j'ai exactement le même système sur sql server 2008 (je suis entrain de migrer les données et procédures stockées de sql server vers postgres): sur sql server, tous les updates, premier ou non pour un même objet, sont tous de quelques millisecondes.
Merci et bonne journée, Thomas
#9 Re : Général » ignorer un trigger » 12/02/2016 22:53:36
mince c'est bien ce que je pensais...
oui en effet votre deuxième proposition est ce que j'ai sous la main si je ne trouve pas de solution via le trigger.
merci !
#10 Général » ignorer un trigger » 12/02/2016 18:18:03
- thomasp
- Réponses : 2
Bonjour à tous !
Je voulais savoir s'il est possible de faire une reqûete en mentionnant d'ignorer un trigger. Je m'explique :
J'ai une table avec plusieurs centaines de milliers d'enregistrements, ce sont des commentaires faits sur des objets (un commentaire = clé primaire).
create table commentaires (
id_commentaire integer primary key
id_objet integer,
commentaire text,
date_creation timestamp,
is_last booleanJ'ai besoin de savoir quel est le commentaire le plus récent de chaque objet. Lorsque je supprime un commentaire, je dois donc trouver le nouveau commentaire le plus récent sur le même objet.
J'ai fait un trigger "AFTER DELETE" qui va systématiquement chercher le nouveau commentaire le plus récent, que je marque en indiquant is_last = true.
-- extrait trigger :
if (TG_OP = 'DELETE') then
update commentaire
set is_last = true
where id_commentaire = public.Find_Last_Comment(OLD.id_object); -- cette fonction retourne l'id du dernier commentaire pour l'objet donné
return old;
end if;Tout fonctionne très bien lorsque je supprime les commentaires un à un. En revanche, lorsque je supprime un objet, je supprime ensuite tous les commentaires qui lui sont attribués :
-- suppression d'un seul commentaire :
delete from commentaires
where id_commentaire = 12;
-- suppression d'un objet puis de tous ses commentaires :
delete from objets
where id_objet = 45660;
delete from commentaires
where id_objet = 45660;Le problème est que je peux avoir plusieurs centaines de commentaires sur un même objet. Le trigger sur commentaires est donc fait autant de fois, alors que sa fonction n'a plus de sens vu que le but est de trouver le dernier commentaire d'un objet, objet qu'on vient de supprimer... j'ai essayé d'adapter le trigger pour qu'il en fasse le moins possible :
-- extrait trigger :
if (TG_OP = 'DELETE') then
if not exists (select true from objets where id_objet = OLD.id_objet) then
return null;
end if;
update commentaire
set is_last = true
where id_commentaire = Find_Last_Comment(OLD.id_object);
return old;
end if;mais même comme cela, le test d’existence de l'objet est fait plusieurs centaines de fois et du coup la requête initiale "delete from commentaires where id_objet = 45660" est très très longue....
D'où ma question : est-il possible de passer une commande en lui indiquant d'ignorer un trigger :
-- suppression d'un seul commentaire, pas de chanagement:
delete from commentaires
where id_commentaire = 12
-- suppression d'un objet puis de tous ses commentaires en ignorant le trigger
delete from objets
where id_objet = 45660;
delete from commentaires
where id_objet = 45660
with trigger my_trigger not executed;J'ai également pensé à mettre le trigger à disable, mais je n'ai pas trouvé comment le faire que pour la session active. Si quelqu'un d'autre au même moment supprime un commentaire, pour lui le trigger doit rester actif !
Et sinon, voyez-vous un autre moyen ?
Merci beaucoup !
#11 Re : Général » mode quiet ne foncitonne plus sous unix » 04/02/2016 13:37:13
mon .psqlrc contenait le "set quiet 0"...
bien vu merci !
#12 Général » mode quiet ne foncitonne plus sous unix » 04/02/2016 11:23:50
- thomasp
- Réponses : 2
bonjour à tous,
J'exécute le même script sur un serveur distant postgres 9.4 depuis 2 machines de dev, une windows une unix, le mode --quiet ne fonctionne pas sous unix, savez-vous comment faire pour qu'il fonctionne depuis les 2 machines ?
-- appel sous ms-dos :
> psql.exe -h dbserver -d dbname -U postgres -p 5432 --quiet -f ./script.sql
-- appel bash (zsh) :
$ psql -h dbserver -d dbname -U postgres -p 5432 --quiet -f ./script.sql>> sous dos je n'ai plus que les messages warning ou error, alors que sous bash (zsh) j'ai encore tous les "DO", "CREATE TABLE", "COMMIT" et autres informartions que le --quiet devrait ne pas afficher.
#13 Re : Général » COPY FROM local file » 03/02/2016 20:11:15
en effet pas du tout ! je vais regarder de ce côté merci.
pour le quote, je ne sais pas, j'ai récupéré les scripts d'imports en l'état faits par mon prédécesseur mais ça a toujours bien marché, je n'y ai donc jamais touché...
#14 Général » COPY FROM local file » 03/02/2016 17:09:04
- thomasp
- Réponses : 2
Bonjour à tous,
J'ai un script import.sql qui importe beaucoup de fichier csv qui sont sur le même serveur que la base, cela marche très bien :
-- fichier import.sql
create table1 (id int, value text);
copy table1(id, value) from '/home/dumps/dump_table1.csv' with csv delimiter as E'\t' quote E'\b'
create table2 (id int, value text, deleted boolean);
copy table2(id, value) from '/home/dumps/dump_table2.csv' with csv delimiter as E'\t' quote E'\b'
...Mais j'ai dû déplacer les fichiers csv qui sont sur un serveur distant d'où j'exécute le script import.sql. Malheureusement je n'arrive plus à faire l'import. J'ai essayé différentes choses avec \copy au liieu de copy mais j'ai toujours la même erreur.
-- fichier remote_import.sql
create table1 (id int, value text);
\copy table1(id, value) from './dumps/dump_table1.csv' with csv delimiter as E'\t' quote E'\b'
create table2 (id int, value text, deleted boolean);
\copy table2(id, value) from './dumps/dump_table2.csv' with csv delimiter as E'\t' quote E'\b'
...j'ai toujours l'erreur :
\copy: parse error at end of line#15 Re : Général » Exécution pas à pas » 25/01/2016 18:40:11
bonjour à tous, j'ai trouvé l'option -s de psql qui fait du pas à pas, mais malheureusement pas à l'intérieur d'un DO$$, dommage !
#16 Re : Général » Exécution pas à pas » 19/01/2016 16:48:35
ok super merci, savez-vous si ça existe en ligne de commande, directement en psql ? En attendant je vais jeter un oeil à votre lien !
merci
#17 Général » Exécution pas à pas » 19/01/2016 14:09:50
- thomasp
- Réponses : 7
Bonjour,
Savez-vous s'il est possible de faire du pas à pas dans l'exécution d'un script plpgsql ? C'est pour faire du debug
Merci !
#18 Re : Général » Row_number() très long en vue, très rapide en requête direct » 15/01/2016 17:15:14
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
#19 Re : Général » Row_number() très long en vue, très rapide en requête direct » 15/01/2016 15:26:44
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);#20 Re : Général » Row_number() très long en vue, très rapide en requête direct » 15/01/2016 11:27:18
@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 ms#21 Re : Général » Row_number() très long en vue, très rapide en requête direct » 14/01/2016 16:20:28
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';#22 Général » Row_number() très long en vue, très rapide en requête direct » 14/01/2016 15:57:39
- thomasp
- Réponses : 10
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
#23 Re : PL/pgSQL » retour de multiples valeurs insérées » 10/11/2015 16:27:26
en effet.... merci
#24 PL/pgSQL » retour de multiples valeurs insérées » 10/11/2015 13:42:08
- thomasp
- Réponses : 3
Bonjour à tous !
Est-il possible de faire un returning après un insert mais pour des valeurs multiples ? Actuellement, je dois déclarer autant de variables que je fais d'insert pour accueillir les valeurs créées :
declare
id1 integer;
id2 integer;
id3 integer;
begin
create table myTable (id serial, value text);
insert into myTable (value) select "first insert" returning id into id1;
insert into myTable (value) select "second insert" returning id into id2;
insert into myTable (value) select "third insert" returning id into id2;
endEst-il possible de faire la même chose mais en retournant l'ensemble des valeurs crées dans une table, ce dont j'ai besoin car parfois je ne sais pas le nombre d'insert que je fais s'ils proviennent du résultat d'une requête :
insert into mytable(value)
select "first insert"
union select "second insert"
union select "third insert"
returning id into myReturnTable;Merci beaucoup !
#25 Général » droits d'accès depuis un serveur lié SQL Server » 28/02/2013 18:36:30
- thomasp
- Réponses : 1
Bonjour,
J'ai fait une connexion à mon serveur postgreSQL depuis une instance SQL Server, et cela marche très bien pour la manipulation des données (select, insert, update, delete). En revanche pour toutes les manipulations de structure (create table, drop table, truncate table,...) j'ai la même erreur:
"The OLE DB provider "MSDASQL" for linked server "POSTGRESQL" indicates that the current user does not have permissions on that object."
J'utilise pourtant le superuser de mon serveur postgre pour la connexion...
Est-ce que quelqu'un sait où je dois autoriser mon superuser à faire des manipulations depuis un serveur lié SQL Server?
Merci et bonne soirée, thomas.
Pages : 1