Re: Big disappointment with Postgres
From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Sun, 13 Feb 2011 00:34:00 -0500
Message-ID: <8rp8q7Fb0eU1_at_mid.individual.net>
On 2/9/2011 12:39 PM, Jonathan Lewis wrote:
>>
>> "Thomas Kellerer"<OTPXDAJCSJVU_at_spammotel.com> wrote in message
>> news:8r34l6Fn40U1_at_mid.individual.net...
>>>
>>> I will leave this futile discussion now
>>>
>>
>> Before you go, could you tell us what you would have to do as a DBA to
>> ensure that Postgres could calculate the correct selectivity on something
>> as simple as:
>>
>> select *
>> from orders
>> where
>> date_delivered> date_placed + 72 hours
>> ;
>>
The idea of SQL is that you tell the system WHAT you want and the system figures out HOW to get it.
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.
BUT, as you say the problem here is selectivity. It not you wanting to pick a plan. It is you wanting to hint selectivity. For example:
SELECT * FROM emp WHERE salary > ?
Date: Sun, 13 Feb 2011 00:34:00 -0500
Message-ID: <8rp8q7Fb0eU1_at_mid.individual.net>
On 2/9/2011 12:39 PM, Jonathan Lewis wrote:
> "Jonathan Lewis"<jonathan_at_jlcomp.demon.co.uk> wrote in message > news:qpudnSPoRugqwdDQnZ2dnUVZ8mydnZ2d_at_bt.com...
>>
>> "Thomas Kellerer"<OTPXDAJCSJVU_at_spammotel.com> wrote in message
>> news:8r34l6Fn40U1_at_mid.individual.net...
>>>
>>> I will leave this futile discussion now
>>>
>>
>> Before you go, could you tell us what you would have to do as a DBA to
>> ensure that Postgres could calculate the correct selectivity on something
>> as simple as:
>>
>> select *
>> from orders
>> where
>> date_delivered> date_placed + 72 hours
>> ;
>>
> > I don't suppose there's anyone from the Postgress optimizer programmers > around still to supply an answer to this question. > If this one is too easy, I'm ready with several that gradually get harder.Not a Postgress optimizre guy obviosuly, but let me jump in here and try to disect things.
The idea of SQL is that you tell the system WHAT you want and the system figures out HOW to get it.
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.
BUT, as you say the problem here is selectivity. It not you wanting to pick a plan. It is you wanting to hint selectivity. For example:
SELECT * FROM emp WHERE salary > ?
Well, if the intent of the query is to find highly paid employees then you have a certain expectation.. Perhaps: SELECT * FROM emp WHERE salary > ? SELECTIVITY 0.02
This is way more useful than dictating the plan.
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.
Just some food for thought....
Serge
PS: Why the OP went to Postgress and not PostgressPlus (aka EDB) is a mystery to me.
-- Serge Rielau SQL Architect DB2 for LUW, IBM Toronto Lab Blog: tinyurl.com/SQLTips4DB2 Wiki: tinyurl.com/Oracle2DB2Wiki Twitter: srielauReceived on Sat Feb 12 2011 - 23:34:00 CST