Re: Big disappointment with Postgres

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 13 Feb 2011 12:51:03 -0000
Message-ID: <K4adnY5Cz9HkTsrQnZ2dnUVZ8uadnZ2d_at_bt.com>

"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 data,
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
of selectivity.

> 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 correct
selectivity. This was simplifying the problem.

>
> In your case WHERE date_delivered> date_placed + 72 hours SELECTIVITY
> 0.01
> 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 - when my
question was: "how do you do it without hinting".

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Received on Sun Feb 13 2011 - 06:51:03 CST