X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 70F60528BF for ; Mon, 16 May 2005 15:26:55 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 57545-05 for ; Mon, 16 May 2005 18:26:51 +0000 (GMT) Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id CE2B2528CA for ; Mon, 16 May 2005 15:26:49 -0300 (ADT) Received: (qmail 11113 invoked from network); 16 May 2005 20:27:15 +0200 Received: from unknown (HELO localhost) (boutiquenumerique-lists@192.168.0.4) by boutiquenumerique.com with SMTP; 16 May 2005 20:27:15 +0200 To: "Ilya A. Kovalenko" , pgsql-sql@postgresql.org Subject: Re: choosing index to use References: <551153969240.20050516193940@oganer.net> Message-ID: From: PFC Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?= Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Date: Mon, 16 May 2005 20:26:48 +0200 In-Reply-To: <551153969240.20050516193940@oganer.net> User-Agent: Opera M2(BETA2)/8.0 (Linux, build 987) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.295 tagged_above=0 required=5 tests=AWL, HOT_NASTY X-Spam-Level: X-Archive-Number: 200505/110 X-Sequence-Number: 21423 Try indexing on client, time instead of time, client... putting the equality condition on the first column of the index can make it faster. Else, analyze, increase your stats, etc... On Mon, 16 May 2005 13:39:40 +0200, Ilya A. Kovalenko wrote: > Greetings, > > How can I control which indexes will or won't be used by query ? > > I never think, that I'll need something like this. > > Short version: > > Simple SELECT query perfomance speeds up (10x-20x) after _removing_ > one of indexes. Because (as EXPLAIN shows), after removing, query > switches to another index. > How to make such without index removing ? > > PostgreSQL 8.0.2, OpenBSD 3.7, i386 > Thank you. > Ilya A. Kovalenko > Special EQ SW section > JSC Oganer-Service > > Details: > > CREATE TABLE traffic_stat > ( > time timestamptz NOT NULL, > client inet NOT NULL, > remote inet NOT NULL, > count_in int8 NOT NULL, > count_out int8 NOT NULL > ) WITHOUT OIDS; > > CREATE INDEX traffic_client_idx > ON traffic_stat > USING btree > (client); > CREATE INDEX traffic_date_idx > ON traffic_stat > USING btree > ("time"); > CREATE INDEX traffic_remote_idx > ON traffic_stat > USING btree > (remote); > CREATE INDEX traffic_multy_idx > ON traffic_stat > USING btree > ("time", client, remote); > CREATE INDEX traffic_date_client_idx > ON traffic_stat > USING btree > ("time", client); > > SELECT count(*) FROM traffic_stat; > > 135511 > > Query is: > SELECT to_char(time, 'DD.MM.YYYY HH24:MI.SS'), remote, count_in, > count_out > FROM traffic_stat WHERE client = '192.168.xxx.xxx' AND > time > '2005-05-16' AND time < '2005-05-16'::date + '1 days'::interval > ORDER BY time; > > Case 1: > SELECT ... > > Total query runtime: 2643 ms. > Data retrieval runtime: 20 ms. > 39 rows retrieved. > > EXPLAIN SELECT ... > > Index Scan using traffic_date_idx on traffic_stat (cost=0.00..3.08 > rows=1 width=35) > Index Cond: (("time" > '2005-05-16 00:00:00+08'::timestamp with time > zone) AND ("time" < '2005-05-17 00:00:00'::timestamp without time zone)) > Filter: (client = '192.168.114.31'::inet) > > Case 2: > > DROP INDEX traffic_date_idx; > > SELECT ... > > Total query runtime: 290 ms. > Data retrieval runtime: 20 ms. > 41 rows retrieved. > > EXPLAIN SELECT ... > > Index Scan using traffic_date_client_idx on traffic_stat > (cost=0.00..4.37 rows=1 width=35) > Index Cond: (("time" > '2005-05-16 00:00:00+08'::timestamp with time > zone) AND ("time" < '2005-05-17 00:00:00'::timestamp without time zone) > AND (client = '192.168.114.31'::inet)) > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >