Re: Table partitioning for maximum speed?

Bruno Wolff III wrote:

>On Fri, Oct 10, 2003 at 11:27:50 -0400,
> Jeff Boes <jboes(at)nexcerpt(dot)com> wrote:
>
>
>>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.
>
>

Actually, the query is *not* slow; but since we executing it a million
times a day, any savings we can realize will add up in a hurry. For
example, yesterday this query resulted in the following stats:

'count' => 814621,
'avg' => '0.009',
'time' => '7674.932'

That is, we executed it 814,621 times, for a total (wallclock) time
spent waiting of 7,674 seconds (obviously, we have multiple backends
executing). So, even if we can cut this by only 0.004, that would result
in a savings of almost an hour.

So, again: will front-loading the work by mapping the original query to
16 (or 256) different queries by examining the first digit save us
anything? (My colleague who came up with this idea thinks so, since the
calculation will be done on a box other than the database host, and even
one disk access saved per query would outweigh the calculation.)

Will having 15 (or 255) additional tables make the cache behave
differently? Is there overhead associated with having another 15 (or
255) tables?

--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise