Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

Next:From: JoeDate: 2005-09-29 20:50:54
Subject: Re: Comparative performance
Previous:From: Tony WassonDate: 2005-09-29 20:02:03
Subject: Re: Monitoring Postgresql performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group