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
