Re: [NEWBIE] need help optimizing this query

On Wed, 10 Mar 2004, Dexter Tad-y wrote:

> On Wed, 2004-03-10 at 23:08, Stephan Szabo wrote:
> > On Wed, 10 Mar 2004, Dexter Tad-y wrote:
> >
> > > Greetings,
> > > I need help in optimizing this query:
> > >
> > > select a.id, b.transaction from test as a left join pg_locks as b on
> > > a.xmax = b.transaction where b.transaction is null;
> > >
> > > im using the query in obtaining records not locked by other
> > > transactions. any help is appreciated.
> >
> > It's hard to say without knowing more about the size of a and explain
> > analyze output. On my 7.4 machine, using NOT IN rather than the left join
> > gives about a 2x speed increase on a 400k row table.
>
>
> 2) using NOT IN
>
> csp=> explain select * from test where id not in (select test.id from
> test, pg_locks where pg_locks.transaction=test.xmax);

I think you'd want:
select * from text where xmax not in (select transaction from pg_locks);

Also, use explain analyze which will actually run the query and show you
the real time for the steps.