Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Siva Kumar <tech@leatherlink.net>, pgsql-general@postgresql.org
Subject: Re: Query optimization
Date: Fri, 4 Oct 2002 11:14:17 +0100
User-Agent: KMail/1.4.3
References: <200210041347.55055.tech@leatherlink.net>
In-Reply-To: <200210041347.55055.tech@leatherlink.net>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200210041114.17241.dev@archonet.com>

On Friday 04 Oct 2002 9:17 am, Siva Kumar wrote:
> We have a query as below:
[snip query with many joins]
>
> The decision to keep the fields in different tables was taken in view of
> the overall need of the system (there might be scope for improvement here
> too).

If that's the way the design makes sense, stick with it. It's better to get=
=20
Postgresql to handle a clean design rather than mangle a design.

> This query normally select about 10-20 rows. The problem is, the page
> load takes about 4-5 seconds in the local network. The query run in psql
> terminal takes about 2 second to execute (outputing 3 rows).
>
> When hosted on the internet with most of our users using dialup
> connections, and the query returning 10+ rows, this will not be acceptabl=
e.

Start by running EXPLAIN SELECT ... and looking at how the parser is handli=
ng=20
the query. One thing you might find useful is to use explicit JOINs to tell=
=20
Postgresql what order to connect the tables. You might prefere EXPLAIN=20
ANALYSE SELECT ... which will calculate actual times for each stage. See th=
e=20
online manuals for details.

If you are missing indexes, you can add them. If Postgresql is not using=20
indexes you already have then we can look at why. Finally, if the plan look=
s=20
OK, we can look at tuning sort memory or similar.

First stage though, run an EXPLAIN and if you need help understanding it po=
st=20
the output back to the list.

- Richard Huxton