From: Jeff Boes <jboes@nexcerpt.com>
To: pgsql-sql@postgresql.org
Subject: Large number of lookups
Date: Fri, 30 Aug 2002 15:40:29 -0400
Message-ID: <akohm7$2rv0$1@news.hub.org> (raw)
I have a large table (>2 Mrows) against which my application runs some 1M
queries per day. The queries are almost all of the nature of
select PRIMARY_KEY_FLD from MY_TABLE
where SECONDARY_KEY_FLD = 'something';
I've optimized the table to the limits of what I can think of, now I'm
looking to optimize the application. I'm really only interested in the
yes/no question of whether the row exists (the returned PRIMARY_KEY_FLD
value is pretty much just for debug documentation). What I'm wondering
is whether my application would get better (faster) results if I ran a
number of queries at once.
For example, I could build SQL that looks like:
select PRIMARY_KEY_FLD, SECONDARY_KEY_FLD
from MY_TABLE WHERE SECONDARY_KEY_FLD in
(first_val, second_val, ...);
so that the list contains some number of values (either a limited "slice"
of the values I care about, or the whole pile--usually 100-200), and
iterate this until I've checked them all.
Another technique would be to construct a "UNION" table:
select PRIMARY_KEY_FLD, SECONDARY_KEY_FLD
from MY_TABLE
join (
select first_val as "SECONDARY_KEY_FLD"
union select second_val
union ...) as TEMP_TABLE
using (SECONDARY_KEY_FLD)
likewise, either taking some number of my desired values in "chunks" or
all at once.
What's likely to work better, and why? (I'm going off to write a
benchmark script, but I'd like to hear some theoretical answers, too.)
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jboes@nexcerpt.com
Subject: Re: Large number of lookups
In-Reply-To: <akohm7$2rv0$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox