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(at)nexcerpt(dot)com> wrote:
> Bruno Wolff III wrote:
>
> >On Thu, Oct 09, 2003 at 18:37:19 +0000,
> > Jeff Boes <jboes(at)nexcerpt(dot)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.