Re: Partitioned table performance

[Josh Berkus <josh(at)agliodbs(dot)com>]

Stacy,

Thanks for the stats!

> In some cases we've seen some increased performance in tests by splitting
> the table into several smaller tables.  Both 'UNION ALL' views, and the
> superclass/subclass scheme work well at pruning down the set of rows a
> query uses, but they seem to introduce a large performance hit to the time
> to process each row (~50% for superclass/subclass, and ~150% for union
> views).

This seems reasonable, actually, given your test.   Really, what you should be 
comparing it against is not against selecting from an individual partition, 
but selecting from the whole business as one large table.  

I also suspect that wider rows results in less overhead proportionally; note 
that your test contains *only* the indexed rows.   I should soon have a test 
to prove this, hopefully.

However, I would be interested in seeing EXPLAIN ANALYZE from your tests 
rather than just EXPLAIN.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco