SUPPRIMER les enregistrements qui n'ont pas de correspondance dans une autre table

Il y a deux tables liées par un id:

item_tbl (id)
link_tbl (item_id)

Il y a quelques enregistrements dans item_tbl qui n'ont pas de lignes correspondantes dans link_tbl. Une sélection qui serait prise en compte de leur montant serait:

SELECT COUNT(*)
FROM link_tbl lnk LEFT JOIN item_tbl itm ON lnk.item_id=itm.id
WHERE itm.id IS NULL

Je voudrais supprimer ces enregistrements orphelins (ceux qui n'ont pas de correspondance dans l'autre table) de link_tbl mais la seule façon que je pouvais penser était:

DELETE FROM link_tbl lnk
WHERE lnk.item_id NOT IN (SELECT itm.id FROM item_tbl itm)

Il y a
262,086,253 enregistrements dans link_tbl
3,033,811 dans item_tbl
16,844,347 des enregistrements orphelins dans link_tbl.
Le serveur dispose de 4 go de RAM et 8 core CPU.

EXPLAIN DELETE FROM link_tbl lnk
WHERE lnk.item_id NOT IN (SELECT itm.id FROM item_tbl itm)

Retourne:

Delete on link lnk  (cost=0.00..11395249378057.98 rows=131045918 width=6)
->  Seq Scan on link lnk  (cost=0.00..11395249378057.98 rows=131045918 width=6)
     Filter: (NOT (SubPlan 1))
     SubPlan 1
       ->  Materialize  (cost=0.00..79298.10 rows=3063207 width=4)
             ->  Seq Scan on item itm  (cost=0.00..52016.07 rows=3063207 width=4)

Les questions sont:

Est-il une meilleure façon de supprimer des enregistrements orphelins de link_tbl?
Quelle est la précision de l'expliquer ci-dessus, ou combien de temps il pourrait prendre pour supprimer ces enregistrements?
- Edit: fixe selon Erwin Brandstetter commentaire.
- Edit: la version de PostgreSql 9.1 est
- Edit: certaines parties de postgresql.config
  1. shared_buffers = 368MB
  2. temp_buffers = 32 MO
  3. work_mem = 32 MO
  4. maintenance_work_mem = 64 MO
  5. max_stack_depth = 6 MO
  6. fsync = off
  7. synchronous_commit = off
  8. full_page_writes = off
  9. wal_buffers = 16MB
  10. wal_writer_delay = 5000ms
  11. commit_delay = 10
  12. commit_siblings = 10
  13. effective_cache_size = 1600MB

Résolution:

Merci à vous tous pour vos conseils, il a été très utile. J'ai finalement utilisé le supprimer conseillé par Erwin Brandstetter https://stackoverflow.com/a/15959896/1331340 mais j'ai modifié un peu:

DELETE FROM link_tbl lnk
WHERE lnk.item_id BETWEEN 0 AND 10000
  AND lnk.item_id NOT IN (SELECT itm.id FROM item itm
                          WHERE itm.id BETWEEN 0 AND 10000)

J'ai comparé les résultats pour ne PAS et n'EXISTE PAS et que la sortie est ci-dessous, bien que j'ai utilisé le COMTE, au lieu de SUPPRIMER ce qui je pense devrait être la même (je veux dire dans un souci de comparaison):

EXPLAIN ANALYZE SELECT COUNT(*) 
FROM link_tbl lnk
WHERE lnk.item_id BETWEEN 0 AND 20000
AND lnk.item_id NOT IN (SELECT itm.id
FROM item_tbl itm
WHERE itm.id BETWEEN 0 AND 20000);
QUERY PLAN
Aggregate  (cost=6002667.56..6002667.57 rows=1 width=0) (actual time=226817.086..226817.088 rows=1 loops=1)
->  Seq Scan on link_tbl lnk  (cost=1592.50..5747898.65 rows=101907564 width=0) (actual time=206.029..225289.570 rows=566625 loops=1)
Filter: ((item_id >= 0) AND (item_id <= 20000) AND (NOT (hashed SubPlan 1)))
SubPlan 1
->  Index Scan using item_tbl_pkey on item_tbl itm  (cost=0.00..1501.95 rows=36221 width=4) (actual time=0.056..99.266 rows=17560 loops=1)
Index Cond: ((id >= 0) AND (id <= 20000))
Total runtime: 226817.211 ms
EXPLAIN ANALYZE SELECT COUNT(*)
FROM link_tbl lnk WHERE lnk.item_id>0 AND lnk.item_id<20000
AND NOT EXISTS (SELECT 1 FROM item_tbl itm WHERE itm.id=lnk.item_id);
QUERY PLAN
Aggregate  (cost=8835772.00..8835772.01 rows=1 width=0)
(actual time=1209235.133..1209235.135 rows=1 loops=1)
->  Hash Anti Join  (cost=102272.16..8835771.99 rows=1 width=0)
(actual time=19315.170..1207900.612 rows=566534 loops=1)
Hash Cond: (lnk.item_id = itm.id)
->  Seq Scan on link_tbl lnk  (cost=0.00..5091076.55 rows=203815128 width=4) (actual time=0.016..599147.604 rows=200301872 loops=1)
Filter: ((item_id > 0) AND (item_id < 20000))
->  Hash  (cost=52016.07..52016.07 rows=3063207 width=4) (actual time=19313.976..19313.976 rows=3033811 loops=1)
Buckets: 131072  Batches: 4  Memory Usage: 26672kB
->  Seq Scan on item_tbl itm  (cost=0.00..52016.07 rows=3063207 width=4) (actual time=0.013..9274.158 rows=3033811 loops=1)
Total runtime: 1209260.228 ms

N'EXISTE PAS a été 5 fois plus lente.

La suppression réelle des données n'ai pas tant de temps que j'étais inquiet, j'ai été capable de le supprimer en 5 lots (10000-20000,20000-100000,100000-200000,200000-1000000 et 1000000-1755441). Au début, j'ai trouvé max item_id et que je suis allé à travers la moitié de la table.

Quand j'ai essayé de ne PAS ou n'EXISTE sans la plage (avec select count) il n'a même pas fini, j'ai laisser tourner pendant la nuit, et il était encore en cours d'exécution dans la matinée.

Je pense que j'ai été à la recherche pour les SUPPRIMER avec l'AIDE de wildplasser la réponse de https://stackoverflow.com/a/15988033/1331340 mais il était déjà trop tard.

DELETE FROM one o
USING (
SELECT o2.id
FROM one o2
LEFT JOIN two t ON t.one_id = o2.id
WHERE t.one_id IS NULL
) sq
WHERE sq.id = o.id
;

source d'informationauteur miloxe

6

Tout d'abord: votre texte dit:

Je voudrais supprimer ces enregistrements orphelins de item_tbl.

Mais votre code dit:
```
DELETE FROM link_tbl lnk ...
```
Mise à jour:
Sur une relecture de l'Q je trouve qu'il est plus probable que vous souhaitez supprimer orphelins lignes dans link_tbl. La ligne compte un point dans cette direction. @Lucas) requête est correcte dans ce cas. Mais j'ai peur, n'EXISTE PAS est en fait plus lentement que NOT IN dans ce cas.

Pour vérifier, j'ai couru un cas de test, qui est à distance comme votre configuration. Ne pouvais pas faire beaucoup plus, ou SQLfiddle irait dans un délai.

-> SQLfiddle.

NOT EXISTS serait plus rapide pour l'inversion de l'affaire. (J'ai testé que, trop.) EXISTS est mieux adapté pour le test de la "multitude"-côté. Et généralement, il n'y a plus à gagner avec EXISTS qu'avec NOT EXISTS - ce formulaire est à vérifier l'ensemble de la table, de toute façon. Il est beaucoup plus difficile de prouver quelque chose n'existe pas que de prouver que quelque chose existe. Cette vérité universelle s'applique également aux bases de données.

Diviser et conquérir

Cette opération est adapté pour être divisé. Surtout si vous avez des transactions simultanées (mais même sans), je voudrais envisager de diviser le DELETE en plusieurs tranches, afin que la transaction puisse COMMIT après une quantité décente de temps.

Quelque chose comme:
```
DELETE FROM link_tbl l
WHERE  l.item_id < 1000000
AND    l.item_id NOT IN (SELECT i.id FROM item_tbl i)
```
Puis l.item_id BETWEEN 100001 AND 200000etc.

Vous ne pouvez pas automatiser cela avec une fonction. Que serait tout envelopper dans une transaction et de défier le but. Si vous avez le script à partir de n'importe quel client.

Ou vous pourriez utiliser ..

dblink

Ce module additionnel permet d'exécuter des transactions distinctes dans une base de données, y compris celui de fonctionnement. Et que peut être effectué via une connexion persistante, ce qui devrait éliminer la plupart des connexions.
Pour obtenir des instructions pour l'installer:

Comment utiliser (installer) dblink dans PostgreSQL?

DO pour faire le travail (PostgreSQL 9.0 ou version ultérieure). 100 DELETE commandes pour 50000 item_id à un moment:
```
DO
$$
DECLARE
_sql text;
BEGIN
PERFORM dblink_connect('port=5432 dbname=mydb');  -- your connection parameters
FOR i IN 0 .. 100
LOOP
_sql := format('
DELETE FROM link_tbl l
WHERE  l.item_id BETWEEN %s AND %s
AND    l.item_id NOT IN (SELECT i.id FROM item_tbl i)'
, (50000 * i)::text
, (50000 * (i+1))::text);
PERFORM  dblink_exec(_sql);
END LOOP;
PERFORM dblink_disconnect();
END
$$
```
Si le script doit être interrompue: dblink_connect écrit pour le journal de base de données qu'il a exécuté, de sorte que vous voir ce qui est déjà fait.
4

Peut-être ceci:
```
DELETE FROM link_tbl lnk
WHERE NOT EXISTS
( SELECT 1 FROM item_tbl item WHERE item.id = lnk.item_id );
```
Lorsqu'ils traitent avec un grand nombre d'enregistrements, il peut être beaucoup plus efficace de créer une table temporaire, effectuer des INSERT INTO SELECT * FROM ... puis chute de la table d'origine, renommez la table temporaire, puis ajouter votre index de retour...

Vous devez vous connecter pour publier un commentaire.

Diviser et conquérir

dblink