Re: Optimize query: time of "single * IN(many)" > time
От | Dave Smith |
---|---|
Тема | Re: Optimize query: time of "single * IN(many)" > time |
Дата | |
Msg-id | 1073561294.2205.4.camel@davehome обсуждение исходный текст |
Ответ на | Optimize query: time of "single * IN(many)" > time of "many * IN(single)" ("Paul Janssen" <postgresuser@hotmail.com>) |
Ответы |
Re: Optimize query: time of "single * IN(many)" > time
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: