pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedchoosing index to use
2+ messages / 2 participants
[nested] [flat]
* choosing index to use
@ 2005-05-16 11:39 Ilya A. Kovalenko <shadow@oganer.net>
0 siblings, 1 reply; 2+ messages in thread
From: Ilya A. Kovalenko @ 2005-05-16 11:39 UTC (permalink / raw)
To: 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 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))
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: choosing index to use
@ 2005-05-16 18:26 PFC <lists@boutiquenumerique.com>
parent: Ilya A. Kovalenko <shadow@oganer.net>
0 siblings, 0 replies; 2+ messages in thread
From: PFC @ 2005-05-16 18:26 UTC (permalink / raw)
To: Ilya A. Kovalenko <shadow@oganer.net>; 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
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-05-16 18:26 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-16 11:39 choosing index to use Ilya A. Kovalenko <shadow@oganer.net>
2005-05-16 18:26 ` PFC <lists@boutiquenumerique.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox