From: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
---|---|
To: | Zeugswetter Andreas DAZ SD <ZeugswetterA(at)spardat(dot)at> |
Cc: | "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Fredrik Olsson <fredrik(dot)olsson(at)treyst(dot)se>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Inherited indexes. |
Date: | 2005-10-04 20:54:17 |
Message-ID: | 1128459257.8603.349.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Tue, 2005-10-04 at 18:16 +0200, Zeugswetter Andreas DAZ SD wrote:
> > Another possibility is optimizing for the special case of
> > indexing on a partitioning key. In this case, index values
> > would be very localized to one table, so just storing the
> > table info on each index page (or something similar) would work well.
>
> If you have the partitioning key in the index and the partitions don't
> overlap, it is better to create separate [unique] indexes on the
> subtables.
> Building separate indexes per partition is usually preferred because of:
> 1. performance of dropping a partition
> 2. smaller index for CE
...
> Imho the best solution would be a merge node,
> that merges results of
> several index accesses to avoid a sort and still use separate indexes.
> Such
> a merge node could probably also detect the case where accessing
> partitions in a certain
> order still produces ordered results.
Yes, that was my conclusion also.
There are a number of intermediate steps along the way, so it will take
some time to achieve it.
> Usually you will only want the "one big unique index" when the
> partitioning is not
> reflectable in the index keys, and then (also in other db's) such an
> index is usually a pain ...
Agreed^2. The idea of a global index is a non-starter for all of the
reasons that Tom gave and the main one: Its's unusably huge. There's no
point in partitioning a 1TB table if you then have to build a 500GB
index on it. The tree would be so deep that each insert would require
maybe 3 I/Os on index branch blocks before you got to the leaf. Insert
performance would suck real bad, which is a blocker since if you have a
large table you almost certainly have a lot of data to load. If you
don't have a big table you shouldn't be partitioning it anyway.
Best Regards, Simon Riggs
From | Date | Subject | |
---|---|---|---|
Next Message | Marc Munro | 2005-10-05 02:22:55 | Announcing Veil |
Previous Message | Jim C. Nasby | 2005-10-04 20:43:43 | Re: [HACKERS] Query in SQL statement |