pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: Matt.Casters@advalvas.be
Cc: pgsql-performance@postgresql.org
Subject: Re:
Date: 20 Jan 2005 11:31:52 -0500
Message-ID: <871xcgdptz.fsf@stark.xeocode.com> (raw)
In-Reply-To: <16153.193.190.212.113.1106213675.squirrel@193.190.212.113>
References: <16153.193.190.212.113.1106213675.squirrel@193.190.212.113>


"Matt Casters" <Matt.Casters@advalvas.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




view thread (15+ messages)  latest in thread

Message-ID: <871xcgdptz.fsf@stark.xeocode.com>
Permalink:  ../871xcgdptz.fsf@stark.xeocode.com/
Also on:    postgresql.org/message-id/871xcgdptz.fsf@stark.xeocode.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: gsstark@mit.edu, Matt.Casters@advalvas.be
  Subject: Re:
  In-Reply-To: <871xcgdptz.fsf@stark.xeocode.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox