Re: Query optimization

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
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 acceptable.

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

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

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

- Richard Huxton