Re: Comparative performance

From: Joe <svn(at)freedomcircle(dot)net>
To: Andreas Pflug <pgadmin(at)pse-consulting(dot)de>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Comparative performance
Date: 2005-09-29 20:39:36
Message-ID: 433C5108.6070202@freedomcircle.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Andreas Pflug wrote:
> Hm, if you only have 4 tables, why do you need 12 queries?
> To reduce queries, join them in the query; no need to merge them
> physically. If you have only two main tables, I'd bet you only need 1-2
> queries for the whole page.

There are more than four tables and the queries are not functionally
overlapping. As an example, allow me to refer to the page
www.freedomcircle.com/topic.php/Economists.

The top row of navigation buttons (Life, Liberty, etc.) is created from a query
of the 'topic' table. It could've been hard-coded as a PHP array, but with less
flexibility. The alphabetical links are from a SELECT DISTINCT substring from
topic. It could've been generated by a PHP for loop (originally implemented
that way) but again with less flexibility. The listing of economists is another
SELECT from topic. The subheadings (Articles, Books) come from a SELECT of an
entry_type table --which currently has 70 rows-- and is read into a PHP array
since we don't know what headings will be used in a given page. The detail of
the entries comes from that query that I posted earlier, but there are three
additional queries that are used for specialized entry types (relationships
between topics --e.g., Prof. Williams teaches at George Mason, events, and
multi-author or multi-subject articles and books). And there's yet another
table for the specific book information. Once the data is retrieved it's sorted
internally with PHP, at the heading level, before display.

Maybe there is some way to merge all the queries (some already fairly complex)
that fetch the data for the entries box but I believe it would be a monstrosity
with over 100 lines of SQL.

Thanks,

Joe

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Joe 2005-09-29 20:50:54 Re: Comparative performance
Previous Message Tony Wasson 2005-09-29 20:02:03 Re: Monitoring Postgresql performance