Re: [NEWBIE] need help optimizing this query

On Wed, 2004-03-10 at 22:42, Bill Moran wrote:
> 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.

Hi,

1) and 2). Both I use occasionally.

3) I think you can't index xmax since its a reserved field. Same with
pg_locks.transaction view as it's built-in. As for the tables, i believe
they're indexed properly.

4) I think postgres.conf is tweak to match our requirements for kernel,
memory, etc.

5) EXPLAIN results posted. :D

Thanks!
Cheers!

Dexter Tad-y