index usage
От | Timur Irmatov |
---|---|
Тема | index usage |
Дата | |
Msg-id | 10597430257.20030117164800@sarkor.com обсуждение исходный текст |
Ответы |
Re: index usage
|
Список | pgsql-performance |
Hi, everyone! I have a simple query which takes almost 3 seconds to complete, but disabling sequence scans leads to a new plan using index. This second plan takes less than 1 millisecond to run. So, I'd like to hear any comments and suggestions. Details. CREATE TABLE MediumStats ( year SMALLINT NOT NULL, month SMALLINT NOT NULL, day SMALLINT NOT NULL, hour SMALLINT NOT NULL, --- and then goes few data fields figureId INTEGER NOT NULL, typeId INTEGER NOT NULL PRIMARY KEY (figureId, typeId, year, month, day, hour) ); CREATE FUNCTION indexHelper (INT2, INT2, INT2, INT2) RETURNS CHARACTER(10) AS ' return sprintf("%d%02d%02d%02d", @_); ' LANGUAGE 'plperl' WITH (isCachable); CREATE INDEX timeIndex ON MediumStats (indexHelper(year,month,day,hour)); and that is the query: SELECT * FROM MediumStats WHERE indexHelper(year,month,day,hour) < '2002121500' LIMIT 1; First, original plan: Limit (cost=0.00..0.09 rows=1 width=22) (actual time=2969.30..2969.30 rows=0 loops=1) -> Seq Scan on mediumstats (cost=0.00..1332.33 rows=15185 width=22) (actual time=2969.29..2969.29 rows=0 loops=1) Total runtime: 2969.39 msec Second plan, seq scans disabled: Limit (cost=0.00..0.19 rows=1 width=6) (actual time=0.43..0.43 rows=0 loops=1) -> Index Scan using timeindex on mediumstats (cost=0.00..2898.96 rows=15185 width=6) (actual time=0.42..0.42 rows=0 loops=1) Total runtime: 0.54 msec Table MediumStats currently has 45000 rows, all rows belong to this month.
В списке pgsql-performance по дате отправления: