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@sitening.com>
>To: Bealach-na Bo <bealach_na_bo@hotmail.com>
>CC: PgSQL - Performance <pgsql-performance@postgresql.org>
>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