Re: Pointers needed on optimizing slow SQL statements

Hi,

Le 6 juin 09 à 10:50, Simon Riggs a écrit :
> On Wed, 2009-06-03 at 21:21 -0400, Robert Haas wrote:
>> But, we're not always real clever about selectivity.  Sometimes you
>> have to fake the planner out, as discussed here.
[...]
>
>> Fortunately, these kinds of problems are fairly rare, but they can be
>> extremely frustrating to debug.  With any kind of query debugging,
>> the
>> first question to ask yourself is "Are any of my selectivity
>> estimates
>> way off?".  If the answer to that question is no, you should then ask
>> "Where is all the time going in this plan?".  If the answer to the
>> first question is yes, though, your time is usually better spent
>> fixing that problem, because once you do, the plan will most likely
>> change to something a lot better.
>
> The Function Index solution works, but it would be much better if we
> could get the planner to remember certain selectivities.
>
> I'm thinking a command like
>
>     ANALYZE foo [WHERE .... ]
>
> which would specifically analyze the selectivity of the given WHERE
> clause for use in queries.

I don't know the stats subsystem well enough to judge by myself how
good this idea is, but I have some remarks about it:
  - it looks good :)
  - where to store the clauses to analyze?
  - do we want to tackle JOIN selectivity patterns too (more than one
table)?

An extension to the ANALYZE foo WHERE ... idea would be then to be
able to analyze random SQL, which could lead to allow for maintaining
VIEW stats. Is this already done, and if not, feasible and a good idea?

This way one could define a view and have the system analyze the
clauses and selectivity of joins etc, then the hard part is for the
planner to be able to use those in user queries... mmm... maybe this
isn't going to help much?

Regards,
--
dim