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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
