title: How to efficiently delete rows from a Postgresql 8.1 table?
body: <p>I'm working on a PostgreSQL 8.1 SQL script which needs to delete a large number of rows from a table.</p>
  
  <p>Let's say the table I need to delete from is Employees (~260K rows). 
  It has primary key named id.</p>
  
  <p>The rows I need to delete from this table are stored in a separate temporary table called EmployeesToDelete (~10K records) with a foreign key reference to Employees.id called employee_id.</p>
  
  <p>Is there an efficient way to do this?</p>
  
  <p>At first, I thought of the following:</p>
  
  <pre><code>DELETE
  FROM    Employees
  WHERE   id IN
          (
          SELECT  employee_id
          FROM    EmployeesToDelete
          )
  </code></pre>
  
  <p>But I heard that using the "IN" clause and subqueries can be inefficient, especially with larger tables.</p>
  
  <p>I've looked at the PostgreSQL 8.1 documentation, and there's mention of
  DELETE FROM ... USING but it doesn't have examples so I'm not sure how to use it.</p>
  
  <p>I'm wondering if the following works and is more efficient?</p>
  
  <pre><code>DELETE
  FROM    Employees
  USING   Employees e
  INNER JOIN
          EmployeesToDelete ed
  ON      e.id = ed.employee_id
  </code></pre>
  
  <p>Your comments are greatly appreciated.</p>
  
  <p>Edit:
  I ran EXPLAIN ANALYZE and the weird thing is that the first DELETE ran pretty quickly (within seconds), while the second DELETE took so long (over 20 min) I eventually cancelled it.</p>
  
  <p>Adding an index to the temp table helped the performance quite a bit.</p>
  
  <p>Here's a query plan of the first DELETE for anyone interested:</p>
  
  <pre><code> Hash Join  (cost=184.64..7854.69 rows=256482 width=6) (actual time=54.089..660.788 rows=27295 loops=1)
     Hash Cond: ("outer".id = "inner".employee_id)
     -&gt;  Seq Scan on Employees  (cost=0.00..3822.82 rows=256482 width=10) (actual time=15.218..351.978 rows=256482 loops=1)
     -&gt;  Hash  (cost=184.14..184.14 rows=200 width=4) (actual time=38.807..38.807 rows=10731 loops=1)
           -&gt;  HashAggregate  (cost=182.14..184.14 rows=200 width=4) (actual time=19.801..28.773 rows=10731 loops=1)
                 -&gt;  Seq Scan on EmployeesToDelete  (cost=0.00..155.31 rows=10731 width=4) (actual time=0.005..9.062 rows=10731 loops=1)
  
   Total runtime: 935.316 ms
  (7 rows)
  </code></pre>
  
  <p>At this point, I'll stick with the first DELETE unless I can find a better way of writing it.</p>