Thanks very much - there are a lot of good articles there... Reading as
fast as I can :)
Best,
Bealach
From: "Thomas F. O'Connell" <tfo@xxxxxxxxxxxx>
To: Bealach-na Bo <bealach_na_bo@xxxxxxxxxxx>
CC: PgSQL - Performance <pgsql-performance@xxxxxxxxxxxxxx>
Subject: Re: [PERFORM] Very slow queries - please help
Date: Sun, 4 Dec 2005 00:40:01 -0600
On Nov 24, 2005, at 12:14 PM, Bealach-na Bo wrote:
The consensus seems to be that I need more indexes and I also need to
look into the NOT IN statement as a possible bottleneck. I've
introduced the indexes which has led to a DRAMATIC change in response
time. Now I have to experiment with INNER JOIN -> OUTER JOIN
variations, SET ENABLE_SEQSCAN=OFF.
Forgive me for not mentioning each person individually and by name.
You have all contributed to confirming what I had suspected (and
hoped): that *I* have a lot to learn!
I'm attaching table descriptions, the first few lines of top output
while the queries were running, index lists, sample queries and
EXPLAIN ANALYSE output BEFORE and AFTER the introduction of the
indexes. As I said, DRAMATIC :) I notice that the CPU usage does not
vary very much, it's nearly 100% anyway, but the memory usage drops
markedly, which is another very nice result of the index introduction.
Any more comments and tips would be very welcome.
You might find the following resources from techdocs instructive:
http://techdocs.postgresql.org/redir.php?link=/techdocs/
pgsqladventuresep2.php
http://techdocs.postgresql.org/redir.php?link=/techdocs/
pgsqladventuresep3.php
These documents provide some guidance into the process of index selection.
It seems like you could still stand to benefit from more indexes based on
your queries, table definitions, and current indexes.
--
Thomas F. O'Connell
Database Architecture and Programming
Co-Founder
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005 (cell)
615-469-5150 (office)
615-469-5151 (fax)
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
