pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <richardh@archonet.com>
To: Dav Coleman <dav@danger-island.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Query Optimization
Date: Thu, 18 Apr 2002 18:38:36 +0100
Message-ID: <200204181838.36240.richardh@archonet.com> (raw)
In-Reply-To: <20020418093527.I24932@danger-island.com>
References: <20020417223051.C24932@danger-island.com>
	<web-1375952@davinci.ethosmedia.com>
	<20020418093527.I24932@danger-island.com>

On Thursday 18 April 2002 17:35, Dav Coleman wrote:
> I should be more clear, the problem is that the application user can
> basically construct the SQL query dynamically

> But I can see where I was heading in the wrong direction already. I was
> thinking that what I needed was to find theories/algorithms on how to
> rewrite the SQL before submitting it to postgresql, and I maybe still
> need to do that

Sort clauses alphabetically (or whatever makes sense to you) so you always get 
SELECT * FROM a,b WHERE c AND d rather than "b,a" or "d AND c". That way at 
least you're not getting variations.

> but I guess I also need to EXPLAIN and analyze the

Record the queries and times either in PG's log or in the application.

> bad vs good forms of the queries so I'll know what makes a 'good' vs
> 'bad' query (so I'll get a sense on how to rewrite queries).  Perhaps
> with that understanding, an algorithm for rewriting the queries will
> be apparent.
>
> I just figured I couldn't be the first person to run into this problem,
> but I can't find it mentioned anywhere.

After the basics (index on fields involved in joins etc) it all gets a bit 
specific to the size of the tables/indexes involved and the quirks of the 
parser.

If you logged the query-plan and cost estimates for each query processed it 
shouldn't be too difficult to automatically add indexes where required and 
see if it makes any difference. That assumes you have good clean patterns of 
usage in your queries. We're getting a bit AI there mind.

- Richard Huxton



view thread (6+ messages)

Message-ID: <200204181838.36240.richardh@archonet.com>
Permalink:  ../200204181838.36240.richardh@archonet.com/
Also on:    postgresql.org/message-id/200204181838.36240.richardh@archonet.com

 · 

reply

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-sql@postgresql.org
  Cc: richardh@archonet.com, dav@danger-island.com
  Subject: Re: SQL Query Optimization
  In-Reply-To: <200204181838.36240.richardh@archonet.com>

* 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