On Fri, Oct 10, 2003 at 11:27:50 -0400,
Jeff Boes <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
