Coming from a Rails background, I have a large production database that I have a copy of where I only need records from 4 of the 2000+ companies. I'm trying to delete all the rows except the ones belonging to the 4, and I know the way I have it is not optimal.

DELETE FROM appointments 
WHERE (appointments.company_id NOT IN (6, 753, 785, 1611))

another example is when I have to delete records on a table where the company_id is on an associated table:

DELETE FROM mappings 
WHERE mappings.id IN (SELECT mappings.id 
                      FROM code_mappings 
                      INNER JOIN codes ON codes.remote_id = mappings.code_remote_id 
                      WHERE (codes.company_id NOT IN (6, 753, 785, 1611)))

In relation to the first table, appointments, make sure that you have an index on company_id column.

In relation to the mappings table, using EXISTS rather than IN may yield better performance. You can re-write your query as following:

DELETE FROM mappings AS m
                FROM code_mappings AS cm
                  INNER JOIN codes AS c
                    ON c.remote_id = cm.code_remote_id
                c.company_id NOT IN (6, 753, 785, 1611)
                AND cm.id = m.id

In the above query, you will also benefit from indexes on the mappings and code tables.

Documentation for creating indexes is @ https://www.postgresql.org/docs/current/static/sql-createindex.html. In your case, you can create indexes on the relevant tables as following:

CREATE INDEX company_id_idx ON appointments (company_id);

CREATE INDEX remote_id_company_id_idx ON codes (remote_id, company_id);

CREATE INDEX code_remote_id_id_idx ON code_mappings (code_remote_id, id);

-- If you don't already have a primary key OR index on `id` column in the `mappings` table, then create one:

-- Choose primary key, or index: CREATE INDEX id_idx ON mappings (id);
  • An index on company_id is unlikely to be helpful. This query touches all but 4 records in the table, so the first delete query will cause a table scan no matter what, and in fact the resulting index reorganization in this case might actually be worse than if there was no index. – Craig Oct 1 '16 at 16:15
  • @Craig, thanks though to be clear, company_id in the appointments table is a key to the company table so, it doesn't touch all but records in the appointments table, not sure if that's what you meant. – Tonys Oct 1 '16 at 21:15
  • @Tonys: Well, of course company_id is a foreign key. The actual field and the actual values also exist literally in the appointments table, since that's how these things work. Telling the database engine to delete all but four records in the appointments table means the database is going to visit every row in the table regardless of any indexes (how else to delete them, or even just to mark them as deleted), and if there's an index, then the index has to be patched up after the delete (or during the delete, extra work either way). ;-) – Craig Oct 1 '16 at 21:34
  • The alternative, if there is an index and the database chooses to use it (it won't), is that because of the extremely low selectivity of the query, the database engine would scan the index. An index scan isn't necessarily faster than a table scan (it's essentially the same operation). You're still in a straight incrementing loop visiting n records, plus the added overhead of visiting almost all of the rows in the table out of physical order to mark them for deletion. If the db used the index, the operation would probably be slower. – Craig Oct 1 '16 at 21:36
  • Scanning the index might result in fewer IO's than a table scan if the entire index fits in the cache, but you would more than make up for that in relatively slow disk (or SSD) IO volume going after the individual rows in the table. If you flip this on its head and want to delete just 4 rows from the table, the selectivity is very high and the index will make a huge difference. – Craig Oct 1 '16 at 21:41

I think you're fine, honestly. You could always dig in and analyze the query plans for various options suggested here.

The performance benefit of the EXISTS predicate (or NOT EXISTS), for instance, comes from letting the query optimizer choose how to use indexes and it works best when the subquery is simplest:

EXISTS (select * from blah where outertable.key = key)

In this instance, you are dealing with very low selectivity. You're essentially saying delete everything! Well, except these four, leave them alone.

The query optimizer isn't going to choose to utilize your indexes, because there isn't really any point. It's going to do a table scan. If it did choose an index (it won't), it would actually perform worse (probably).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.