pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

SELECT statement never completes.
2+ messages / 2 participants
[nested] [flat]

* SELECT statement never completes.
@ 2002-10-09 17:10  John Pauley <karstdiver@yahoo.com>
  0 siblings, 1 reply; 2+ messages in thread

From: John Pauley @ 2002-10-09 17:10 UTC (permalink / raw)
  To: pgsql-sql

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?
Thanks

__________________________________________________
Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos & More
http://faith.yahoo.com



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: SELECT statement never completes.
@ 2002-10-09 17:26  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: John Pauley <karstdiver@yahoo.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Stephan Szabo @ 2002-10-09 17:26 UTC (permalink / raw)
  To: John Pauley <karstdiver@yahoo.com>; +Cc: 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.




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2002-10-09 17:26 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-09 17:10 SELECT statement never completes. John Pauley <karstdiver@yahoo.com>
2002-10-09 17:26 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox