In-Reply-To: <424928D8.7060705@numerixtechnology.de>
References: <42487A6D.6020506@numerixtechnology.de>
	<1112047463.22988.25.camel@state.g2switchworks.com>
	<424928D8.7060705@numerixtechnology.de>
Mime-Version: 1.0 (Apple Message framework v619.2)
Content-Type: text/plain; charset=US-ASCII; format=flowed
Message-Id: <140ac5a92c5f5fc2e38bd10a17e7d70f@mail.nih.gov>
Content-Transfer-Encoding: 7bit
Cc: Scott Marlowe <smarlowe@g2switchworks.com>,
	pgsql-sql@postgresql.org
From: Sean Davis <sdavis2@mail.nih.gov>
Subject: Re: cost of CREATE VIEW ...  AS SELECT DISTINCT
Date: Tue, 29 Mar 2005 05:59:01 -0500
To: mailreg@numerixtechnology.de


On Mar 29, 2005, at 5:07 AM, T E Schmitz wrote:

> 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?
>

I think you are right.  If this is expensive and run often, you could 
always normalize further and create a table of "unique_origin" that 
would have only unique origins and set transaktion to have a foreign 
key referring to the unique_origin table and then just query the 
unique_origin table when you need to do the query above.

Sean