Tom Lane wrote:
> The thing I object to about the "I want to decorate my queries with
> planner hints" mindset is that it's coming at it from the wrong
> direction. You should never be thinking in terms of "fix this one
> query", because that just leads back into the same dead end that your
> fix doesn't work tomorrow. What you *should* be thinking about is "why
> did the planner get this wrong, and how do I fix the generic problem?".
> If you attack it that way then your fix is much more likely to work on
> the next slightly-different query.
>
> So some kind of override for statistical guesses doesn't seem completely
> silly to me. But it needs to be declarative information that's stored
> somewhere out of view of the actual SQL queries. IMHO anyway.
>
> regards, tom lane
Imagine a join between two tables:
select a.x, b.y where a.x = f(b.y) from a, b;
I may know that, given the data I've put into the tables, only one value in b
will ever match one value in a. Or perhaps I know that no more than ten rows in
b will match a given value in a. But how can the statistics from ANALYZE ever
see through arbitrary math functions to know this sort of thing?
The current analyze functionality, as I understand it, can store information
about a given table, but not about the relationships between the data in several
tables, which is the information the planner would need to choose the right
plan. Do all the requests from postgres users for giving hints to the planner
involve this type of situation, where the hints are not about a single table,
but rather about the relationship between two or more tables and specific joins
between them?
Do I understand correctly? Is this a reasonable analysis?
mark