> -----Original Message----- > From: Ben [mailto:midfi...@gmail.com] > Sent: Friday, October 29, 2010 12:16 PM > To: Igor Neyman > Cc: pgsql-performance@postgresql.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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
