Re: [NEWBIE] need help optimizing this query

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.

The query, in and of itself, is as optimized as it's going to get.

The real question is whether or not your database is optimized.

1) How often do you vacuum?
2) How often do you analyze? (I recenlty saw a 300% speedup on a query
    after running analyze!  I didn't realize just how important it was
    until then!)
3) Do you have indexes on a.xmax and b.transaction?  (I was wondering
    why a test database was running so slow (about 100x slower than usual)
    and I realized I had forgotten to create the indexes)
4) Have you tweaked postgres.conf apropriately?
5) If none of these helps, you should post the output of EXPLAIN on
    this query, which will give the people on the list enough details to
    give you more specific advice.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com