Date: Mon, 16 May 2005 19:39:40 +0800
From: "Ilya A. Kovalenko" <shadow@oganer.net>
Reply-To: "Ilya A. Kovalenko" <shadow@oganer.net>
Organization: JSC Oganer-Service
Message-ID: <551153969240.20050516193940@oganer.net>
To: pgsql-sql@postgresql.org
Subject: choosing index to use
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

    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))