Re: partitioning question 1

> -----Original Message-----
> From: Ben [mailto:midfield(at)gmail(dot)com]
> Sent: Friday, October 29, 2010 12:16 PM
> To: Igor Neyman
> Cc: pgsql-performance(at)postgresql(dot)org
> Subject: Re: partitioning question 1
>
> On Oct 29, 2010, at 7:38 AM, Igor Neyman wrote:
>
> >> is my intuition completely off on this?
> >>
> >> best regards, ben
> >>
> >
> > If your SELECT retrieves substantial amount of records, table scan
> > could be more efficient than index access.
> >
> > Now, if while retrieving large amount of records "WHERE clause" of
> > this SELECT still satisfies constraints on some partition(s), then
> > obviously one (or few) partition scans will be more efficient than
> > full table scan of non-partitioned table.
> >
> > So, yes partitioning provides performance improvements, not only
> > maintenance convenience.
>
> my impression was that a *clustered* index would give a lot
> of the same I/O benefits, in a more flexible way. if you're
> clustered on the column in question, then an index scan for a
> range is much like a sequential scan over a partition (as far
> as i understand.)
>
> b
>

Even with clustered index you still read index+table, which is more
expensive than just table scan (in situation I described above).
PG clustered index is not the same as SQL Server clustered index (which
includes actual table pages on the leaf level).

Igor Neyman