![]() ![]() ![]() It would be faster to delete from the temp_table_what_to_delete. Test and adjust the grouping size as you need. We even sleep for 1 sec to let the server do other stuff. Perform dblink_exec ( ' DELETE FROM clients WHERE id IN ( SELECT id FROM temp_table_what_to_delete limit 10000) ' ) Īs you see, we delete 10K rows in every iteration. PERFORM dblink_connect('host=localhost user=your_user password=your_password dbname=your_database') SELECT count(*)/10000 INTO v_groups FROM temp_table_what_to_delete Most postgres servers have this extension available. ![]() This should add dblink as a valid extension. First we need to install db_link : CREATE EXTENSION dblink For this you need to use a trick, to connect in every iteration in the loop. The major problem is PostgreSQL does not know to handle transactions in a procedure. CREATE FUNCTION selectifexists () RETURNS TABLE (id integer, foo text) AS BEGIN IF EXISTS (SELECT FROM informationschema. ║ -> Seq Scan on nd.a (cost=0.00.145.00 rows=10000 width=10) ║ You could (and likely have to) wrap that in a function. ║ -> Nested Loop Anti Join (cost=.91 rows=1 width=12) ║ ║ Delete on nd.a (cost=.91 rows=1 width=12) ║ Only the table owner, the schema owner, and superuser can drop a table. ║ Output: b.x explain (verbose) delete from a where not exists (select * from b where a.x=b.x) Description DROP TABLE removes tables from the database. Tests: explain (verbose) delete from a where a.x not in (select b.x from b) You should probably use a stored procedure to to do this: DELIMITER DROP PROCEDURE IF EXISTS test.DeleteByID CREATE PROCEDURE test.DeleteByID (db VARCHAR(64),tb VARCHAR(64),idtodelete INT) BEGIN DECLARE FoundCount INT SELECT COUNT(1) INTO FoundCount FROM informationschema.tables WHERE tableschema db AND tablename tb IF FoundCount 1 THEN SET sql CONCAT('DELETE FROM. But with simple playground we can see that NOT IN case does not used indexes to perform the operation:Ĭreate table a as select (random()*1000)::int as x from generate_series(1,10000) Ĭreate table b as select (random()*1000)::int*10 as x from generate_series(1,1000000) It is hard to explain why it's happening without having actual data. WHERE EvenLargerTable.foreign_id = LargeTable.id) For large tables used for test of existence NOT EXISTS frequently works faster then NOT IN. ![]()
0 Comments
Leave a Reply. |