Re: [NEWBIE] need help optimizing this query

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.