Question: Deleting rows which do not have entries in another table as foreign keys takes ages to run

Question

Deleting rows which do not have entries in another table as foreign keys takes ages to run

Answers 0
Added at 2016-11-25 11:11
Tags
Question

There are two tables - image and box

Table image has a primary key - id - which has a reference in the box table as image_id

I have archived some rows from database 1 into database 2. Now I want to delete these rows in database 1 as they have been copied to database 2. I am using postgres_fdw approach. I am using foreign tables as wrappers to delete entries in database 1 and running these queries in database 2.

My query takes forever to run -

Delete from foreigntableImage using 
  image i  where 
  foreigntableImage.id=i.id and 
  and not exists (select 1 from foreigntableBox bb where bb.image_id= i.id)

Create table for the table - image

CREATE TABLE image
(
  id bigserial NOT NULL,
  someotherid character varying(255) NOT NULL,
  image_timestamp bigint,
  point geography(Point,4326) NOT NULL,
  created_at timestamp without time zone DEFAULT now(),
  updated_at timestamp without time zone DEFAULT now(),
  image_id integer,
  sometype character varying(255),
  CONSTRAINT image_pkey PRIMARY KEY (id),
  CONSTRAINT image_someotherid_img_timestamp_image_id_key UNIQUE (someotherid , image_timestamp , image_id),
  CONSTRAINT image_ts_or_id_null CHECK (image_timestamp IS NOT NULL OR image_id IS NOT NULL)
)

Create table for the foreign table:

CREATE FOREIGN TABLE IF NOT EXISTS foreigntableImage ( 
  id int , 
  someotheridcharacter varying(255) , 
  image_timestamp bigint, 
  point geography(Point,4326) , 
  created_at timestamp without time zone , 
  updated_at timestamp without time zone, 
  image_id integer, 
  sometype character varying(255), 
) SERVER my_server OPTIONS (schema_name 'mydb', table_name 'image')

Important note, which I missed out earlier

foreigntableBox is also a foreign table over a table box - the box table has a foreign key over image table's id.

This takes hours and hours. My tables are actually pretty huge. What can I do to optimize this query?

Here's the output of Explain on the Delete query above:

   "Delete on mydb.foreigntableimage  (cost=241.50..20235.52 rows=1204 width=96)"
"  Remote SQL: DELETE FROM mydb.image WHERE ctid = $1"
"  ->  Hash Anti Join  (cost=241.50..20235.52 rows=1204 width=96)"
"        Output: foreigntableimage.ctid, i.ctid, bb.*"
"        Hash Cond: (i.id = bb.image_id)"
"        ->  Nested Loop  (cost=100.43..19966.37 rows=2409 width=20)"
"              Output: foreigntableimage.ctid, i.ctid, i.id"
"              ->  Foreign Scan on mydb.foreigntableimage  (cost=100.00..182.27 rows=2409 width=10)"
"                    Output: foreigntableimage.ctid, foreigntableimage.id"
"                    Remote SQL: SELECT id, ctid FROM mydb.image FOR UPDATE"
"              ->  Index Scan using image_pkey on mydb.image i  (cost=0.43..8.20 rows=1 width=14)"
"                    Output: i.ctid, i.id"
"                    Index Cond: (i.id = foreigntableimage.id)"
"        ->  Hash  (cost=131.93..131.93 rows=731 width=88)"
"              Output: bb.*, bb.image_id"
"              ->  Foreign Scan on mydb.foreigntablebox bb  (cost=100.00..131.93 rows=731 width=88)"
"                    Output: bb.*, bb.image_id"
"                    Remote SQL: SELECT id, box_id, created_at, updated_at FROM mydb.box"

Can foreign tables have primary keys?

Answers to

Deleting rows which do not have entries in another table as foreign keys takes ages to run

Source Show
◀ Wstecz