choosing index to use
От | Ilya A. Kovalenko |
---|---|
Тема | choosing index to use |
Дата | |
Msg-id | 551153969240.20050516193940@oganer.net обсуждение исходный текст |
Ответы |
Re: choosing index to use
|
Список | pgsql-sql |
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 int8NOT 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'::timestampwith 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-1600: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))
В списке pgsql-sql по дате отправления: