Re: Partitioned table performance

From: Stacy White
Subject: Re: Partitioned table performance
Date: ,
Msg-id: 006901c4e7c7$62118500$0200a8c0@grownups
(view: Whole thread, Raw)
In response to: Partitioned table performance  ("Stacy White")
List: pgsql-performance

Tree view

Partitioned table performance  ("Stacy White", )
 Re: Partitioned table performance  (Josh Berkus, )
 Re: Partitioned table performance  ("Stacy White", )
  Re: Partitioned table performance  (Josh Berkus, )
 Re: Partitioned table performance  ("Stacy White", )
  Re: Partitioned table performance  (Josh Berkus, )
   Re: Partitioned table performance  (Greg Stark, )
    Re: Partitioned table performance  (Josh Berkus, )
     Re: Partitioned table performance  (Greg Stark, )
     Re: Partitioned table performance  ("Jim C. Nasby", )
    Re: Partitioned table performance  (Tom Lane, )
     Re: Partitioned table performance  ("Jim C. Nasby", )
 Re: Partitioned table performance  ("Stacy White", )

The discussion seems to have diverged a little, so I don't feel too bad
about making some semi-off-topic comments.

From: "Greg Stark" <>
> Like I said though, we found "global indexes" defeated the whole purpose.

First semi-off-topic comment: I think this depends on the index, the data,
and the goal of the partitioning.  We use partitioning on one of our Oracle
projects for performance rather than managability.  In this case, a global
index on a non-partitioned field can be helpful.

Imagine an 'orders' table with 100 partitions on week.  Products have a
short life cycle, and are typically around for only a week or two.  A query
like 'SELECT * FROM orders WHERE product_no = ?' forces a lookup on 100
different local indexes, but only one global index.


Second-semi-off-topic comment: Josh mentioned that Oracle's partitioning
introduces it's own overhead, so I re-ran my earlier benchmarks on one of
our Oracle machines.

I believe Oracle's licensing agreement prohibits me from posting any
benchmarks, so all I'll say is that Postgres' inheritance partitioning
implementation is _very_ low overhead, and even union views are competitive.



pgsql-performance by date:

From: "Stacy White"
Date:
Subject: Re: Partitioned table performance
From: Pailloncy Jean-Gerard
Date:
Subject: 8rc2 & BLCKSZ