Re:

"Matt Casters" <Matt(dot)Casters(at)advalvas(dot)be> writes:

> I've been reading up on partitioned tabes on pgsql, will the performance
> benefit will be comparable to Oracle partitioned tables?

Postgres doesn't have any built-in support for partitioned tables. You can do
it the same way people did it on Oracle up until 8.0 which is by creating
views of UNIONs or using inherited tables.

The main advantage of partitioned tables is being able to load and drop data
in large chunks instantaneously. This avoids having to perform large deletes
and then having to vacuum huge tables to recover the space.

However in Postgres you aren't going to get most of the performance advantage
of partitions in your query plans. The Oracle planner can prune partitions it
knows aren't relevant to the query to avoid having to search through them.

This can let it get the speed of a full table scan without the disadvantage of
having to read irrelevant tuples. Postgres is sometimes going to be forced to
either do a much slower index scan or read tables that aren't relevant.

--
greg