From: Joe <svn@freedomcircle.net>
To: Andreas Pflug <pgadmin@pse-consulting.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Comparative performance
Date: Thu, 29 Sep 2005 16:39:36 -0400
Message-ID: <433C5108.6070202@freedomcircle.net> (raw)
In-Reply-To: <433BEA7B.1050405@pse-consulting.de>
References: <433B4AA4.9080008@freedomcircle.net>
<op.sxu1g5bnth1vuj@localhost>
<433BDFF3.3090402@freedomcircle.net>
<433BEA7B.1050405@pse-consulting.de>
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
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: svn@freedomcircle.net, pgadmin@pse-consulting.de
Subject: Re: Comparative performance
In-Reply-To: <433C5108.6070202@freedomcircle.net>
* 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