Re: Table partitioning for maximum speed?

Please keep discussions on the list so that others may learn from or comment
on the suggested solutions.

On Fri, Oct 10, 2003 at 11:27:50 -0400,
  Jeff Boes <jboes@nexcerpt.com> wrote:
> Bruno Wolff III wrote:
> 
> >On Thu, Oct 09, 2003 at 18:37:19 +0000,
> > Jeff Boes <jboes@nexcerpt.com> wrote:
> > 
> >
> >>
> >>The idea bandied about now is to partition this table into 16 (or 256, 
> >>or ...) chunks by first digit (or 2, or ...). In the simplest case, this 
> >>would mean:
> >>   
> >>
> >
> >If there is an index on the checksum column, then you shouldn't get
> >much of a speed up by partitioning the data.
> >If you don't have an index on the checksum, it sounds like you should.
> > 
> >
> Yes, the table has:
> 
>    Table "public.link_checksums"
> Column  |     Type      | Modifiers
> ---------+---------------+-----------
> md5     | character(32) | not null
> link_id | integer       | not null
> Indexes: ix_link_checksums_pk primary key btree (md5)

In that event I would expect that you might only save a few disk accesses
by having a btree with fewer levels.

If the query is slow, it might be doing a sequential search because of
a type mismatch. You can use explain to double check what plan is being
used.