Re: Very slow queries - please help - Mailing list pgsql-performance

From Bealach-na Bo
Subject Re: Very slow queries - please help
Date
Msg-id BAY101-F3653633E9198EAC5BDA20BAD460@phx.gbl
Whole thread Raw
In response to Re: Very slow queries - please help  ("Thomas F. O'Connell" <tfo@sitening.com>)
List pgsql-performance
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



pgsql-performance by date:

Previous
From: "Steinar H. Gunderson"
Date:
Subject: Re: 7.4.7 vs. 8.1
Next
From: Alvaro Herrera
Date:
Subject: Re: How much expensive are row level statistics?