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