Re: choosing index to use - Mailing list pgsql-sql

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 <shadow@oganer.net>  
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
>