Re: Big disappointment with Postgres
Date: Sun, 13 Feb 2011 12:51:03 -0000
"Serge Rielau" <srielau_at_ca.ibm.com> wrote in message news:8rp8q7Fb0eU1_at_mid.individual.net...
> The idea of SQL is that you tell the system WHAT you want and the system
> figures out HOW to get it.
The fundamental idea of the relational database is that you store only the
and don't store "routing" information with the data. The database is only required
to give you the right answer, relational theory says nothing about performance,
that's an implementation detail.
> Now the DBMS is responsible to derive proper statistics from the data and
> things like extended stats (or column group stats) have been mentioned.
> In your query it is absolutely possible for the DBMS to understand the
> correlation, it's just that non ethat I know of has ever bothered
> implementing it.
Technically Oracle 8 could handle this requirement, provided the query were
rewritten in a suitable fashion. The problem isn't one of correlation, by
the way -
you could know correlation coefficient and still get a hugely misleading estimate
> BUT, as you say the problem here is selectivity. It not you wanting to
> pick a plan. It is you wanting to hint selectivity.
But the "best plan" cannot be derived without the ability to calculate the
selectivity. This was simplifying the problem.
> In your case WHERE date_delivered> date_placed + 72 hours SELECTIVITY
> Would tell the optimizer that you expect this to be the exception and to
> act accordingly.
Which brings us back to the need for hints even for very simple queries -
question was: "how do you do it without hinting".
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.comReceived on Sun Feb 13 2011 - 06:51:03 CST