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

On Tue, Mar 29, 2005 at 02:21:15PM +0100, T E Schmitz wrote:
> Bruno Wolff III wrote:
> >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.
>
> As far as I can see (via EXPLAIN), both DISTINCT and GROUP BY will
> lead to a sequentail scan. Is that correct?

That (GROUP BY using a seqscan) may be caused by the small size of the
table. Try populating it some more.

--
Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
"¿Qué importan los años? Lo que realmente importa es comprobar que
a fin de cuentas la mejor edad de la vida es estar vivo" (Mafalda)