From: Bealach-na Bo <bealach_na_bo@hotmail.com>
To: tfo@sitening.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Very slow queries - please help
Date: Mon, 12 Dec 2005 16:24:40 +0000
Message-ID: <BAY101-F3653633E9198EAC5BDA20BAD460@phx.gbl> (raw)
In-Reply-To: <91002EEA-58BC-46F0-9F27-AEC11A50A02C@sitening.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: bealach_na_bo@hotmail.com, tfo@sitening.com
Subject: Re: Very slow queries - please help
In-Reply-To: <BAY101-F3653633E9198EAC5BDA20BAD460@phx.gbl>
References: <91002EEA-58BC-46F0-9F27-AEC11A50A02C@sitening.com> <BAY101-F3653633E9198EAC5BDA20BAD460@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox