From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | John Pauley <karstdiver(at)yahoo(dot)com> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: SELECT statement never completes. |
Date: | 2002-10-09 17:26:31 |
Message-ID: | 20021009102456.H4728-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2002-10-09 17:31:12 | Re: [SQL] problem with the Index |
Previous Message | John Pauley | 2002-10-09 17:10:21 | SELECT statement never completes. |