Victor, > [explain] select * from BIGMA where string not in (select * from DIRTY); > QUERY PLAN > ------------------------------------------------------------------------ > Seq Scan on bigma (cost=0.00..24582291.25 rows=500 width=145) > Filter: (NOT (subplan)) > SubPlan > -> Seq Scan on dirty (cost=0.00..42904.63 rows=2503963 width=82)
This is what you call an "evil query". I'm not surprised it takes forever; you're telling the database "Compare every value in 2.7 million rows of text against 2.5 million rows of text and give me those that don't match." There is simply no way, on ANY RDBMS, for this query to execute and not eat all of your RAM and CPU for a long time. You're simply going to have to allocate shared_buffers and sort_mem (about 2GB of sort_mem would be good) to the query, and turn the computer over to the task until it's done. And, for the sake of sanity, when you find the 200,000 rows that don't match, flag them so that you don't have to do this again. -- Josh Berkus Aglio Database Solutions San Francisco ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match