Re: On partitioning

On Sun, Aug 31, 2014 at 9:03 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Aside from costing planning time, most likely that would forever prevent
> us from pushing some types of intelligence about partitioning into the
> executor.

How would it affect this calculus if there were partitioned indexes
which were created on the overall table and guaranteed to exist on
each partition that the planner could use -- and then possibly also
per-partition indexes that might exist in addition to those? So the
planner could make deductions and leave some intelligence about
partitions to the executor as long as they only depend on partitioned
indexes but might be able to take advantage of a per-partition index
if it's an unusual situation. I'm imagining for example a partitioned
table where only the current partition is read-write and OLTP queries
restrict themselves to working only with the current partition. Having
excluded the other partitions the planner is free to use any of the
indexes liberally.

That said, I think the typical approach to this is to only allow
indexes that are defined for the whole table. If the user wants to
have different indexes for the current time period they would have a
separate table with all the indexes on it that is only moved into the
partitioned table once it's finished being used for for the atypical
queries. Oracle supports "local partitioned indexes" (which are
partitioned like the table) and "global indexes" (which span
partitions) but afaik it doesn't support indexes on only some
partitions.

Furthermore, we have partial indexes. Partial indexes mean you can
always create a partial index on just one partition's range of keys.
The index will exist for all partitions but just be empty for all but
the partitions that matter. The planner can plan based on the partial
index's where clause which would accomplish the same thing, I think.

--
greg