Forums PostgreSQL.fr

Bonjour à tous,

Version : PostgreSQL 12.4 sur CentOS Linux release 7.7.1908.

J'ai un problème sur une requête, sans limit elle s'exécute très rapidement, si j'ajoute un limit les perfs sont totalement dégradés (et le plan d'exécution n'est plus du tout le même).
J'ai lancé un analyze sur les tables concernés (en essayant d'augmenter le statistics sur certaines colonnes), j'ai essayé pas mal de chose et je ne sais pas trop quoi faire de plus.
Est-ce que vous auriez une idée ?

Chaque table a une PK sur l'ID (Ex : pk_SF).
Chaque table à une FK vers la table qui suit dans les join, donc SF a une FK vers FI, FI vers FA, et FA vers US. La colonne Id_Cu est indexée (fk_US_ID_CU).

J'ai collé le plan d'exécution ici pour une meilleure visibilité : https://explain.dalibo.com/
Je ne sais pas comment le formater dans ce message, je vais regarder.

explain(analyze,buffers) select SF.ID
from SF
inner join FI on SF.ID_FI=FI.ID
inner join FA on FI.ID_FA=FA.ID
inner join US on FA.ID_US=US.ID
where  US.ID_Cu=3589
order by SF.ID asc limit 500;

Limit  (cost=1001.72..7982.26 rows=500 width=8) (actual time=11286.838..11367.893 rows=500 loops=1)
   Buffers: shared hit=50681527 read=300668
   ->  Gather Merge  (cost=1001.72..4725621.47 rows=338414 width=8) (actual time=11286.837..11367.852 rows=500 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=50681527 read=300668
         ->  Nested Loop  (cost=1.70..4685560.07 rows=141006 width=8) (actual time=11281.667..11284.336 rows=376 loops=3)
               Buffers: shared hit=50681527 read=300668
               ->  Nested Loop  (cost=1.28..3662972.00 rows=2101335 width=8) (actual time=0.128..8512.638 rows=1317068 loops=3)
                     Buffers: shared hit=34876711 read=300666
                     ->  Nested Loop  (cost=0.86..2726320.62 rows=2101335 width=8) (actual time=0.095..5764.520 rows=1317068 loops=3)
                           Buffers: shared hit=19071965 read=300594
                           ->  Parallel Index Scan using pk_SF on SF  (cost=0.43..1446549.99 rows=2101335 width=8) (actual time=0.049..2213.763 rows=1317068 loops=3)
                                 Buffers: shared hit=3296007 read=271734
                           ->  Index Scan using pk_FI on FI  (cost=0.43..0.61 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=3951204)
                                 Index Cond: (FI.ID = SF.ID_FI)
                                 Buffers: shared hit=15775958 read=28860
                     ->  Index Scan using pk_FA on FA  (cost=0.42..0.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=3951204)
                           Index Cond: (FA.ID = FI.ID_FA)
                           Buffers: shared hit=15804746 read=72
               ->  Index Scan using pk_US on US  (cost=0.42..0.49 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=3951204)
                     Index Cond: (US.ID = FA.ID_US)
                     Filter: (ID_Cu = 3589)
                     Rows Removed by Filter: 1
                     Buffers: shared hit=15804816 read=2
Planning Time: 0.948 ms
Execution Time: 11367.991 ms

explain(analyze,buffers) select SF.ID
from SF
inner join FI on SF.ID_FI=FI.ID
inner join FA on FI.ID_FA=FA.ID
inner join US on FA.ID_US=US.ID
where  US.ID_Cu=3589
order by SF.ID asc;

Gather Merge  (cost=221551.06..244443.77 rows=199067 width=8) (actual time=242.353..261.827 rows=64225 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   Buffers: shared hit=320499 read=10667
   ->  Sort  (cost=220551.05..221048.72 rows=199067 width=8) (actual time=237.831..239.886 rows=32112 loops=2)
         Sort Key: SF.ID
         Sort Method: quicksort  Memory: 3484kB
         Worker 0:  Sort Method: quicksort  Memory: 1831kB
         Buffers: shared hit=320499 read=10667
         ->  Nested Loop  (cost=3018.77..203030.28 rows=199067 width=8) (actual time=2.695..227.630 rows=32112 loops=2)
               Buffers: shared hit=320492 read=10667
               ->  Nested Loop  (cost=3018.34..64734.51 rows=80414 width=4) (actual time=2.626..105.165 rows=32176 loops=2)
                     Buffers: shared hit=68890 read=4990
                     ->  Parallel Hash Join  (cost=3017.91..7059.10 rows=10456 width=4) (actual time=2.573..31.903 rows=4622 loops=2)
                           Hash Cond: (FA.ID_US = US.ID)
                           Buffers: shared hit=3297 read=28
                           ->  Parallel Seq Scan on FA  (cost=0.00..3632.16 rows=155816 width=8) (actual time=0.008..10.456 rows=132444 loops=2)
                                 Buffers: shared hit=2046 read=28
                           ->  Parallel Hash  (cost=2958.81..2958.81 rows=4728 width=4) (actual time=2.256..2.257 rows=3905 loops=2)
                                 Buckets: 8192  Batches: 1  Memory Usage: 384kB
                                 Buffers: shared hit=1207
                                 ->  Parallel Bitmap Heap Scan on US  (cost=170.71..2958.81 rows=4728 width=4) (actual time=0.857..3.411 rows=7810 loops=1)
                                       Recheck Cond: (ID_Cu = 3589)
                                       Heap Blocks: exact=1171
                                       Buffers: shared hit=1207
                                       ->  Bitmap Index Scan on fk_US_ID_CU  (cost=0.00..168.70 rows=8038 width=0) (actual time=0.635..0.636 rows=7810 loops=1)
                                             Index Cond: (ID_Cu = 3589)
                                             Buffers: shared hit=36
                     ->  Index Scan using fk_FI_FA on FI  (cost=0.43..4.80 rows=72 width=8) (actual time=0.004..0.015 rows=7 loops=9244)
                           Index Cond: (FA.ID = FI.ID_FA)
                           Buffers: shared hit=65593 read=4962
               ->  Index Scan using fk_SF_FI on SF  (cost=0.43..1.67 rows=5 width=8) (actual time=0.003..0.003 rows=1 loops=64351)
                     Index Cond: (SF.ID_FI = FI.ID)
                     Buffers: shared hit=251602 read=5677
Planning Time: 0.764 ms
Execution Time: 265.550 ms

Bonjour à tous,

Petite prise de tête du vendredi
Je suis en Postgres v11.5 sous CentOS.

Je veux écrire un trigger qui lors d'un update sur une table stockerait dans une autre table (dans un jsonb) seulement les valeurs réellement modifiées.
J'ai pour cela créé un trigger (for each statement) et ma fonction trigger. J'essaye de trouver la méthode la moins lourde à mettre en place (sachant que potentiellement je vais devoir faire cet audit sur pas mal d'autres tables). Pour l'instant, à part comparer les colonnes 1 à 1 entre anciennes et nouvelles lignes je ne vois rien de plus rapide à écrire. L'idéal serait une façon plus générique ou dynamique de faire ça mais je bloque un peu là. Si quelqu'un a une idée je suis preneur ! Pour info la table contient des colonnes de tous types (integer, varchar, date, double , geometry). Pour les colonnes nullable je pourrais utiliser COALESCE mais le problème c'est que je ne maitrise pas forcément ce qui peut être rentrée dans certaines colonnes donc ça me semblait plus sûr avec cette méthode.

CREATE OR REPLACE FUNCTION MySchema.MyFunction() RETURNS TRIGGER AS $BODY$
DECLARE
	oldRow RECORD;
	newRow RECORD;
BEGIN
	
	-- For each row, we will compare each attribute and store in the event only ones that have changed
	FOR oldRow IN SELECT * FROM old_rows
	LOOP
		SELECT * INTO newRow FROM new_rows WHERE id = oldRow.id;
		
		-- For not null columns
		IF oldRow.col1 <> newRow.col1 THEN
			raise notice 'col1 changed';
		END IF;
		
		-- For null columns
		IF oldRow.col2 <> newRow.col2
			OR oldRow.col2 is null and newRow.col2 is not null
			OR oldRow.col2 is not null and newRow.col2 is null THEN
			raise notice 'col2 changed';
		END IF;
		
		-- Etc...
	
	END LOOP;
	
	RETURN NULL;
END;
$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER MyTrigger
    AFTER UPDATE ON MyTable
    REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows
    FOR EACH STATEMENT EXECUTE FUNCTION MySchema.MyFunction();

Merci d'avance !
Julien.

Bonjour,

J'ai une question concernant la sauvegarde à chaud avec Postgres (v9.5 et plus). Une fois que la commande pg_start_backup() a été exécutée, on peut copier tous les fichiers de l'instance nécessaires à une restauration. Sachant que pendant la sauvegarde les écritures se font toujours dans la répertoire data, comment Postgres fait-il pour que les fichiers copiés soient utilisables alors que potentiellement ils peuvent être modifiés pendant la copie ? J'ai lu que pg_start_backup force des "full page writes" (activé par défaut) jusqu'à l'exécution de pg_stop_backup(), c'est grâce à cela que lors de la restauration il peut remettre les fichiers (notamment ceux des tables par exemple) comme il faut ? En réécrivant en entier les blocks à restaurer ?

Merci.
Coridalement.

Bonjour à tous,

Je suis en train de faire une migration Oracle (11gr2) vers PostgreSQL (9.5.4) et j'ai commencé à réécrire un certain nombre de trigger en PL/pgSQL, et je rencontre un problème avec un trigger en particulier.

Supposons que j'ai un schéma A avec une table T1, et un schéma B avec une table T2 ayant la même structure que T1. T2 est une table qui va contenir les lignes supprimées de T1. J'ai donc un trigger after delete sur T1 qui fait juste un insert select OLD.* dans T2.

Le user qui exécute le delete dans T1 a uniquement des droits sur T1, et pas sur T2. Ça marchait comme ceci sous Oracle, et sous PG je suis obligé de donner au user des droits en écriture sur T2 pour que le trigger s'exécute. Y-a-t-il moyen d'empêcher ça ? Parce que je ne veux pas que le user puisse écrire directement dans T2.

Merci.
Juju.