Re: Partitioned table performance

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