where not exists
От | Llew |
---|---|
Тема | where not exists |
Дата | |
Msg-id | a65qji$2i6k$1@jupiter.hub.org обсуждение исходный текст |
Ответы |
Re: where not exists
|
Список | pgsql-sql |
Dear everyone, What is the best way of removing rows which are not in another table? I have these two tables each with millions of rows/tuples. They are joined on two fields: CREATE TABLE a ( join1 OID, join2 OID, --a fair number of other fields ..... ) CREATE TABLE b ( join1 OID, join2 OID, --a fair number of other fields ..... ) There are indices on both of them (on "join1, join2"). At the moment, I am doing 1) get items which are orphaned in a. CREATE TEMP TABLE orphans as SELECT join1, join2 FROM a WHERE NOT EXISTS ( SELECT * FROM b WHERE a.join1 = b.join1 AND a.join2 = b.join2 ) 2) DELETE FROM a where orphans.join1 = a.join1 and orphans.join2=a.join2 3) DROP TABLE orphans This is very slow. Is there a better way? Should I first copy all join1. join2 from a and b into temporary tables first? Do I need to index the temporary tables? Surely this is a general enough a problem that optimal sets of solutions exists in people's experience. Thanks a lot. Llew
В списке pgsql-sql по дате отправления: