Re: SQL Query Optimization

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