Re: [NEWBIE] need help optimizing this query
От | Dexter Tad-y |
---|---|
Тема | Re: [NEWBIE] need help optimizing this query |
Дата | |
Msg-id | 1078933905.2833.66.camel@localhost обсуждение исходный текст |
Ответ на | Re: [NEWBIE] need help optimizing this query (Bill Moran <wmoran@potentialtech.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: