Re: What needs to be done for real Partitioning?

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Global indexes would seriously reduce the performance of both vacuum and
> cluster for a single partition, and if you want seq scans you don't need
> an index for that at all. So the above doesn't strike me as a strong
> argument for global indexes ...

I think he means some sort of plan for queries like

select * from invoices where customer_id = 1

where customer 1 only did business with us for two years. One could imagine
some kind of very coarse grained bitmap index that just knows which partitions
customer_id=1 appears in, and then does a sequential scan of those partitions.

But I think you can do nearly as well without using global indexes of any
type. Assuming you had local indexes on customer_id for each partition and
separate histograms for each partition the planner could conclude that it
needs sequential scans for some partitions and a quick index lookup expecting
0 records for other partitions.

Not as good as pruning partitions entirely but if you're doing a sequential
scan the performance hit of a few index lookups isn't a problem.

--
greg