Re: cost of CREATE VIEW ... AS SELECT DISTINCT

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: T E Schmitz <mailreg(at)numerixtechnology(dot)de>
Cc: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: cost of CREATE VIEW ... AS SELECT DISTINCT
Date: 2005-03-29 13:29:41
Message-ID: 20050329132941.GA27251@wolff.to
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

On Tue, Mar 29, 2005 at 11:07:20 +0100,
T E Schmitz <mailreg(at)numerixtechnology(dot)de> wrote:
>
> Would the "SELECT DISTINCT origin" always cause a sequential table scan
> regardless whether there is an index on the origin column or not?

It's worse than that, SELECT DISTINCT cannot use a hash aggregate plan
and will need to do a sort to eliminate duplicates. Unless the view
is used in a way that restricts the candidate rows, this probably isn't going
to be very fast. You might be better off changing the view to use GROUP BY
instead of DISTINCT.

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Alvaro Herrera 2005-03-29 13:58:23 Re: cost of CREATE VIEW ... AS SELECT DISTINCT
Previous Message T E Schmitz 2005-03-29 13:21:15 Re: cost of CREATE VIEW ... AS SELECT DISTINCT