|
|
 | | From: | Greg Stark | | Subject: | Re: | | Date: | 20 Jan 2005 11:31:52 -0500 |
|
|
 |
"Matt Casters" 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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
|
|
| | From: | Mischa | | Subject: | Re: | | Date: | Thu, 20 Jan 2005 10:50:55 -0800 |
|
|
| No support for partitioned tables? Perhaps in name ... but I use a time-based
"partition" tables that inherit from a base table; new partitions are "placed"
(moved) round-robin on a set of drives. Somewhat manual, but if you really need
a solution now, it works.
Quoting Greg Stark :
>
> "Matt Casters" 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
--
"Dreams come true, not free."
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
|
|
|
