Message-ID: <452D04DF.4040904@enterprisedb.com>
Date: Wed, 11 Oct 2006 15:51:11 +0100
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Thunderbird 1.5.0.7 (X11/20060915)
MIME-Version: 1.0
To: Bucky Jordan <bjordan@lumeta.com>
CC: Tom Lane <tgl@sss.pgh.pa.us>, Brian Herlihy <btherl@yahoo.com.au>,
	Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Simple join optimized badly?
References: <78ED28FACE63744386D68D8A9D1CF5D4209C7A@MAIL.corp.lumeta.com>
In-Reply-To: <78ED28FACE63744386D68D8A9D1CF5D4209C7A@MAIL.corp.lumeta.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Bucky Jordan wrote:
> 
> Is this along the lines of "I'm loading a big table and touching every
> row of data, so I may as well collect some stats along the way" and "I
> know my data contains these statistical properties, but the analyzer
> wasn't able to figure that out (or maybe can't figure it out efficiently
> enough)"?
> 
> While it seems like this would require more knowledge from the user
> (e.g. more about their data, how the planner works, and how it uses
> statistics) this would actually be helpful/required for those who really
> care about performance. ...

The user would have to know his data, but he wouldn't need to know how 
the planner works. While with hints like "use index X", he *does* need 
to know how the planner works.

Being able to give hints about statistical properties of relations and 
their relationships seems like a good idea to me. And we can later 
figure out ways to calculate them automatically.

BTW, in DB2 you can declare a table as volatile, which means that the 
cardinality of the table varies greatly. The planner favors index scans 
on volatile tables.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com