Re: A Guide to Constraint Exclusion (Partitioning)

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:

> It's very common to scan whole ranges of dates on a large table, so in
> those cases you are really just maintaining the indexes for partitioning
> purposes. On older data it may be desirable not to have lots of indexes,
> or at least use their resources on the indexes they really do want.
>
> Also, if you have a List partitioned table where all rows in that table
> have a single value, then you maintain an index for no reason other than
> partitioning. Thats an expensive waste.
>
> Simply put, adding a constraint is faster and cheaper than adding an
> pointless index. CE gives people that option.

Note also that the index is only useful if the index is *being used*. And
index scans are much slower than sequential scans.

So a query like "select * from invoices where fiscal_year = ?" is best
implemented by doing a sequential scan across invoices_fy05. This is *much*
faster than using indexes even if the indexes manage to speed up the empty
partitions simply because an index scan across the full partition would be so
much slower than a sequential scan.

--
greg