On Thu, 28 Aug 2003, Michael Guerin wrote: > Stephan Szabo wrote: > > >On Wed, 27 Aug 2003, Michael Guerin wrote: > > > > > > > >>I'm running into some performance problems trying to execute simple > >>queries. > >> > >>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
What does explain analyze show for the two queries? ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend