У меня есть следующая таблица / индексы -
CREATE TABLE test
(
coords geography(Point,4326),
user_id varchar(50),
created_at timestamp
);
CREATE INDEX ix_coords ON test USING GIST (coords);
CREATE INDEX ix_user_id ON test (user_id);
CREATE INDEX ix_created_at ON test (created_at DESC);
Это запрос, который я хочу выполнить:
select *
from updates
where ST_DWithin(coords, ST_MakePoint(-126.4, 45.32)::geography, 30000)
and user_id='3212312'
order by created_at desc
limit 60
Когда я запускаю запрос, он использует только ix_coords
index. Как я могу гарантировать, что Postgres использует также ix_user_id
и ix_created_at
индекс для запроса?
Это новая таблица, в которую я произвел массовую вставку производственных данных. Всего строк в таблице test
: 15 069 489
Я использую PostgreSQL 9.2.1 (с Postgis) с (effective_cache_size = 2 ГБ). Это моя локальная OSX с 16 ГБ ОЗУ, Core i7 / 2,5 ГГц, без SSD-диска.
Добавление вывода EXPLAIN ANALYZE
-
Limit (cost=71.64..71.65 rows=1 width=280) (actual time=1278.652..1278.665 rows=60 loops=1)
-> Sort (cost=71.64..71.65 rows=1 width=280) (actual time=1278.651..1278.662 rows=60 loops=1)
Sort Key: created_at
Sort Method: top-N heapsort Memory: 33kB
-> Index Scan using ix_coords on test (cost=0.00..71.63 rows=1 width=280) (actual time=0.198..1278.227 rows=178 loops=1)
Index Cond: (coords && '0101000020E61000006666666666E63C40C3F5285C8F824440'::geography)
Filter: (((user_id)::text = '4f1092000b921a000100015c'::text) AND ('0101000020E61000006666666666E63C40C3F5285C8F824440'::geography && _st_expand(coords, 30000::double precision)) AND _st_dwithin(coords, '0101000020E61000006666666666E63C40C3F5285C8F824440'::geography, 30000::double precision, true))
Rows Removed by Filter: 3122459
Total runtime: 1278.701 ms
ОБНОВИТЬ:
Основываясь на приведенных ниже предложениях, я попробовал index on cords + user_id:
CREATE INDEX ix_coords_and_user_id ON updates USING GIST (coords, user_id);
..но получаю следующую ошибку:
ERROR: data type character varying has no default operator class for access method "gist"
HINT: You must specify an operator class for the index or define a default operator class for the data type.
ОБНОВИТЬ:
Итак, CREATE EXTENSION btree_gist;
решил проблему составного индекса btree / gist. И теперь мой индекс выглядит так
CREATE INDEX ix_coords_user_id_created_at ON test USING GIST (coords, user_id, created_at);
ПРИМЕЧАНИЕ: btree_gist не принимает DESC / ASC.
Новый план запроса:
Limit (cost=134.99..135.00 rows=1 width=280) (actual time=273.282..273.292 rows=60 loops=1)
-> Sort (cost=134.99..135.00 rows=1 width=280) (actual time=273.281..273.285 rows=60 loops=1)
Sort Key: created_at
Sort Method: quicksort Memory: 41kB
-> Index Scan using ix_updates_coords_user_id_created_at on updates (cost=0.00..134.98 rows=1 width=280) (actual time=0.406..273.110 rows=115 loops=1)
Index Cond: ((coords && '0101000020E61000006666666666E63C40C3F5285C8F824440'::geography) AND ((user_id)::text = '4e952bb5b9a77200010019ad'::text))
Filter: (('0101000020E61000006666666666E63C40C3F5285C8F824440'::geography && _st_expand(coords, 30000::double precision)) AND _st_dwithin(coords, '0101000020E61000006666666666E63C40C3F5285C8F824440'::geography, 30000::double precision, true))
Rows Removed by Filter: 1
Total runtime: 273.331 ms
Запрос выполняется лучше, чем раньше, почти на секунду лучше, но все еще не очень хорошо. Я думаю, это лучшее, что я могу получить ?? Я надеялся где-то около 60-80мс. Кроме того, взяв order by created_at desc
из запроса, сокращается еще 100 мсек, что означает, что он не может использовать индекс. Как бы то ни было, чтобы это исправить?
user_id='3212312'
? Выполняли ли выvacuum analyze
перед этим запросом (по крайней мере, после заполнения таблицы)? - person wildplasser   schedule 03.10.2012ix_coords
недоступен - может ли он использовать другой индекс и какова его стоимость, - попробуйтеBEGIN; DROP INDEX ix_coords ON thetable; EXPLAIN ANALYZE the_query; ROLLBACK;
. - person Craig Ringer   schedule 04.10.2012