On Wed, 9 Oct 2002, John Pauley wrote: > pgsql-sql, > > We are porting a database from IBM DB2 to PostgreSQL. > In several related scripts, there is a SELECT > statement that never completes in Postgres but > completes in a few seconds using DB2, for example: > > Table row count: > SELECT count(*) FROM tableX; > 112671 > SELECT count(*) from tableY; > 314625 > > This statement does not complete: > SELECT id FROM tableX WHERE id NOT IN (SELECT id FROM > tableY); > > Any suggestions?
Unfortunately IN <subselect> tends to have poor performance in postgresql. Often you can get better performance out of exists, but not always. You might want to try: select id from tableX WHERE NOT EXISTS (select * from tableY where tableY.id=tableX.id); and see if it runs better. ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
