From: Bruno Wolff III <bruno@wolff.to>
To: T E Schmitz <mailreg@numerixtechnology.de>
Cc: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: cost of CREATE VIEW ... AS SELECT DISTINCT
Date: Tue, 29 Mar 2005 07:29:41 -0600
Message-ID: <20050329132941.GA27251@wolff.to> (raw)
In-Reply-To: <424928D8.7060705@numerixtechnology.de>
References: <42487A6D.6020506@numerixtechnology.de>
<1112047463.22988.25.camel@state.g2switchworks.com>
<424928D8.7060705@numerixtechnology.de>
On Tue, Mar 29, 2005 at 11:07:20 +0100,
T E Schmitz <mailreg@numerixtechnology.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, mailreg@numerixtechnology.de, smarlowe@g2switchworks.com
Subject: Re: cost of CREATE VIEW ... AS SELECT DISTINCT
In-Reply-To: <20050329132941.GA27251@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox