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. CREATE FUNCTION selectifexists () RETURNS TABLE (id integer, foo text) AS BEGIN IF EXISTS (SELECT FROM informationschema. This should add dblink as a valid extension. You could (and likely have to) wrap that in a function. 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. selfintersects (pargeom) returns X rows with 4 columns/attributes, - where X corresponds to number. ╚══════════════════════════════════════════════════════════════════════════════════╝ create or replace function removevertices (pargeom geometry) returns geometry as DECLARE cnt int BEGIN drop table if exists invalidpnts create temp table invalidpnts as select from selfintersects (pargeom) - calls other function. 1 Answer Sorted by: 2 delete/drop a table if another table exists That would be: do begin if exists (select 1 from informationschema. ║ -> Nested Loop Anti Join (cost=.91 rows=1 width=12) ║ ║ Delete on nd.a (cost=.91 rows=1 width=12) ║ ║ Output: b.x explain (verbose) delete from a where not exists (select * from b where a.x=b.x) Tests: explain (verbose) delete from a where a.x not in (select b.x from b) 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. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |