Re: Optimize query: time of "single * IN(many)" > time

Firstly you should always provide an explain from your query before
posting to this list. I think the problem is that in <7.4 PG did not use
indexes for IN queries. This has been fixed in 7.4.

On Thu, 2004-01-08 at 05:44, Paul Janssen wrote:
> Hello!
>
> 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