eTdsstaa

1DELETE FROM LargeTable
2WHERE NOT EXISTS (
3  SELECT *
4  FROM EvenLargerTable
5  WHERE EvenLargerTable.foreign_id = LargeTable.id);
6	
7-- drop table if exists a;
8-- drop table if exists b;
9create table a as select (random()*1000)::int as x from generate_series(1,10000);
10create index idx_a on a(x);
11
12create table b as select (random()*1000)::int*10 as x from generate_series(1,1000000);
13create index idx_b on b(x);
14
15analyse a;
16analyse b;
17	
18nd@postgres=# explain (verbose) delete from a where a.x not in (select b.x from b);
19╔══════════════════════════════════════════════════════════════════════════════════╗
20â•‘                                    QUERY PLAN                                    â•‘
21╠══════════════════════════════════════════════════════════════════════════════════╣
22â•‘ Delete on nd.a  (cost=0.00..129160170.00 rows=5000 width=6)                      â•‘
23â•‘   ->  Seq Scan on nd.a  (cost=0.00..129160170.00 rows=5000 width=6)              â•‘
24â•‘         Output: a.ctid                                                           â•‘
25â•‘         Filter: (NOT (SubPlan 1))                                                â•‘
26â•‘         SubPlan 1                                                                â•‘
27â•‘           ->  Materialize  (cost=0.00..23332.00 rows=1000000 width=4)            â•‘
28â•‘                 Output: b.x                                                      â•‘
29â•‘                 ->  Seq Scan on nd.b  (cost=0.00..14425.00 rows=1000000 width=4) â•‘
30â•‘                       Output: b.x                                                â•‘
31╚══════════════════════════════════════════════════════════════════════════════════╝
32
33nd@postgres=# explain (verbose) delete from a where not exists (select * from b where a.x=b.x);
34╔══════════════════════════════════════════════════════════════════════════════════╗
35â•‘                                    QUERY PLAN                                    â•‘
36╠══════════════════════════════════════════════════════════════════════════════════╣
37â•‘ Delete on nd.a  (cost=0.42..5005.91 rows=1 width=12)                             â•‘
38â•‘   ->  Nested Loop Anti Join  (cost=0.42..5005.91 rows=1 width=12)                â•‘
39â•‘         Output: a.ctid, b.ctid                                                   â•‘
40â•‘         ->  Seq Scan on nd.a  (cost=0.00..145.00 rows=10000 width=10)            â•‘
41â•‘               Output: a.ctid, a.x                                                â•‘
42â•‘         ->  Index Scan using idx_b on nd.b  (cost=0.42..20.78 rows=999 width=10) â•‘
43â•‘               Output: b.ctid, b.x                                                â•‘
44â•‘               Index Cond: (a.x = b.x)                                            â•‘
45╚══════════════════════════════════════════════════════════════════════════════════╝
46	
47CREATE EXTENSION dblink;
48	
49do $$
50<<test1>>
51DECLARE v_itteration INTEGER := 1 ; 
52DECLARE v_groups    INTEGER := 0 ; 
53BEGIN
54
55     SELECT count(*)/10000 INTO v_groups FROM temp_table_what_to_delete ;
56     WHILE v_itteration <= v_groups + 1 LOOP
57            PERFORM dblink_connect('host=localhost user=your_user password=your_password dbname=your_database');
58             perform dblink_exec ( ' DELETE FROM clients WHERE id IN ( SELECT id FROM temp_table_what_to_delete limit 10000); ' );
59          v_itteration := v_itteration + 1 ;
60          PERFORM dblink_disconnect();  
61         PERFORM pg_sleep ( 1 ) ;
62     END LOOP ;
63 end test1 $$;