Re: Simple queries take forever to run

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?