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)