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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
