Can anyone help me out with the following situation:
(a) a single query with 550 id's in the IN-clause resulting into 800+
seconds;
(b) 550 queries with a single id in the IN-clause resulting into overall
time of <60 seconds;
The table consists of 950.000 records, and the resultset consists of 205.000
records.
Why is there such an extreme difference in time?
And is there a way to reduce the difference in time?
More information about the situation is below.
Thank you for your help and time! =)
Postgres-version
7.3.1
The query is like:
SELECT a_id, b_id, score, c_id, d_id
FROM tbl_scores
WHERE a_id IN(...)
UNION
SELECT a_id, b_id, score, c_id, d_id
FROM tbl_scores_alike
WHERE a_id IN(...)
Definition of tables is like:
___Fields
CREATE TABLE public.tbl_scores (
id int4 DEFAULT nextval('"tbl_scores_id_seq"'::text) NOT NULL,
a_id int4,
b_id int4,
score int4, d_id int8,
CONSTRAINT tbl_scores_pkey UNIQUE (id),
dc date DEFAULT now(),
c_id int4,
INITIALLY IMMEDIATE
) WITHOUT OIDS;
___Index
CREATE INDEX tbl_scores_idx10 ON tbl_scores USING btree (a_id, b_id,
score, c_id, d_id);
Things that I tried to reduce the time of situation (a) - single * IN(many):
* vacuum of the database; hardly any improvement.
* selecting a single field in the resultset (a_id) instead of all fields;
hardly any improvement.
* only querying one table, skipping the UNION; hardly any improvement;
* ... what would you try?
__________________________________________________ _______________
MSN Zoeken helpt je om de gekste dingen te vinden! http://search.msn.nl
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
