Re: SQL Query Optimization

Dav,

> I should be more clear, the problem is that the application user can
> basically construct the SQL query dynamically, so I have no control
> on
> how the original SQL query will be formed or what it will consist of.
> It can be any possible query in practice. Because of this, it is not
> just
> a matter of analyzing any specific queries, and i don't want to start
> creating every possible index (although i might, if i have to).

See Tom's response. He's the expert.

However, if the user is allowed to write any query they wish, it does
sound like you'll need to construct every reasonable index. This will
make UPDATES on your tables very expensive, but you have no way of
anticipating what the user will ask.

You'll also need to take a really hard look at the relational structure
of your database. Seemingly trivial lack of Normal Form in your table
structures can become very costly as far as subqueries are concerned.
Also, DISTINCT can be very costly on large tables.

> I just figured I couldn't be the first person to run into this
> problem,
> but I can't find it mentioned anywhere.

Good luck. I can't even think of any books I've reveiwed that would
address this issue. Part of the problem, I think, is that optimization
is so circumstantial.

> btw, I'm running postgresql-7.1.2 (compilied from source) on rh7.0

I very much suggest that you upgrade to 7.2.1. Tom and company have
made substantial improvements to the query parser and the tracking of
index statistics in 7.2.

-Josh