Stephan Szabo wrote:

On Wed, 27 Aug 2003, Michael Guerin wrote:

I'm running into some performance problems trying to execute simple

postgresql version 7.3.3
.conf params changed from defaults.
shared_buffers = 64000
sort_mem = 64000
fsync = false
effective_cache_size = 400000

ex. query: select * from x where id in (select id from y);

There's an index on each table for id. SQL Server takes <1s to return,
postgresql doesn't return at all, neither does explain analyze.

IN(subquery) is known to run poorly in 7.3.x and earlier. 7.4 is generally much better (for reasonably sized subqueries) but in earlier versions you'll probably want to convert into an EXISTS or join form.

Something else seems to be going on, even switching to an exists clause gives much better but poor performance.
count(*) where exists clause: Postgresql 19s, SQL Server <1s
count(*) where not exists: 23.3s SQL Server 1.5s

SQL Server runs on a dual 1.4 with 4gigs, win2k
Postgresql runs on a quad 900 with 8 gigs, sunos 5.8

---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster

Reply via email to