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 * fromtableY where tableY.id=tableX.id);
and see if it runs better.