Message-ID: <424928D8.7060705@numerixtechnology.de>
Date: Tue, 29 Mar 2005 11:07:20 +0100
From: T E Schmitz <mailreg@numerixtechnology.de>
Reply-To: mailreg@numerixtechnology.de
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: cost of CREATE VIEW ...  AS SELECT DISTINCT
References: <42487A6D.6020506@numerixtechnology.de>
	<1112047463.22988.25.camel@state.g2switchworks.com>
In-Reply-To: <1112047463.22988.25.camel@state.g2switchworks.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Hello Scott,

Scott Marlowe wrote:
> On Mon, 2005-03-28 at 15:43, T E Schmitz wrote:
> 
>>How expensive would it be to maintain the following VIEW:
>>
>>CREATE VIEW origin AS SELECT DISTINCT origin FROM transaktion
>>
>>if there is in index on transaktion.origin; the table transaktion has 
>>thousands of records and there are only a few distinct origin?
> 
> 
> The cost will only be encurred when running the view.  if you want
> materialized views (which WOULD have maintenance costs) you'll have to

Thank you for the pointer - that might come in handy for another scenario.

> The cost of executing that view should be the same as the cost of
> running the query by hand.

I did an EXPLAIN ANALYZE and a sequential scan was carried out despite 
the index I had on the column. Maybe this is because I only have very 
few records in my test DB.

Would the "SELECT DISTINCT origin" always cause a sequential table scan 
regardless whether there is an index on the origin column or not?


-- 


Regards/Gruß,

Tarlika Elisabeth Schmitz