Re: partitioning question 1

> -----Original Message-----
> From: Ben [mailto:midfield(at)gmail(dot)com]
> Sent: Thursday, October 28, 2010 12:37 PM
> To: pgsql-performance(at)postgresql(dot)org
> Subject: partitioning question 1
>
> hello --
>
> my last email was apparently too long to respond to so i'll
> split it up into shorter pieces. my first question :
>
> my understanding of how range partitioning and constraint
> exclusion works leads me to believe that it does not buy any
> query performance that a clustered index doesn't already give
> you -- the advantages are all in maintainability. an index
> is able to eliminate pages just as well as constraint
> exclusion is able to eliminate table partitions. the I/O
> advantages of having queries target small subtables are the
> same as the I/O advantages of clustering the index : result
> pages in a small range are very close to each other on disk.
>
> finally, since constraint exclusion isn't as flexible as
> indexing (i've seen old mailing list posts that say that
> constraint exclusion only works with static constants in
> where clauses, and only works with simple operators like >, <
> which basically forces btree indexes when i want to use gist)
> it is indeed likely that partitioning can be slower than one
> big table with a clustered index.
>
> 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.

Regards,
Igor Neyman