Гость
Войти | Профиль | Очистить
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Планировщик не использует индекс / 5 сообщений из 5, страница 1 из 1
11.07.2017, 08:45
    #39486227
kalombo
kalombo
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Планировщик не использует индекс
Использую расширение Postgis. Есть две таблицы:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
-- 14 тысяч записей
CREATE TABLE public.storagepricingapp_storagelocation
(
  id integer NOT NULL DEFAULT nextval('storagepricingapp_storagelocation_id_seq'::regclass),
  location geometry(Point,4326) NOT NULL,
  CONSTRAINT storagepricingapp_storagelocation_pkey PRIMARY KEY (id)
)

CREATE INDEX storagepricingapp_storagelocation_location_id
  ON public.storagepricingapp_storagelocation
  USING gist
  (location);


-- 30 миллионов записей
CREATE TABLE public.storagepricingapp_storageunit
(
  id integer NOT NULL DEFAULT nextval('storagepricingapp_storageunit_id_seq'::regclass),
  sizeid character varying(30) NOT NULL,
  dateupdated date NOT NULL,
  facility_id integer NOT NULL,
  datetimeadded timestamp with time zone NOT NULL,
  CONSTRAINT storagepricingapp_storageunit_pkey PRIMARY KEY (id),
  CONSTRAINT st_facility_id_f26e0175_fk_storagepricingapp_storagelocation_id FOREIGN KEY (facility_id)
      REFERENCES public.storagepricingapp_storagelocation (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)

CREATE INDEX storagepricingapp_storageunit_e32a5395
  ON public.storagepricingapp_storageunit
  USING btree
  (facility_id);



Делаю запрос:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
set enable_seqscan = on;
explain analyze 
	SELECT "storagepricingapp_storageunit"."id"

FROM "storagepricingapp_storageunit" 

	INNER JOIN "storagepricingapp_storagelocation" 

		ON ("storagepricingapp_storageunit"."facility_id" = "storagepricingapp_storagelocation"."id") 

WHERE ST_DistanceSphere("storagepricingapp_storagelocation"."location", ST_GeomFromEWKB('\x0101000020e6100000b04cf0ff963f5ac0ad4886eb90dc4340'::bytea)) <= 8046.72




"Hash Join  (cost=4942.68..1126775.50 rows=10162748 width=4) (actual time=55.645..42943.863 rows=118421 loops=1)"
"  Hash Cond: (storagepricingapp_storageunit.facility_id = storagepricingapp_storagelocation.id)"
"  ->  Seq Scan on storagepricingapp_storageunit  (cost=0.00..905874.43 rows=30488243 width=8) (actual time=8.818..31864.672 rows=30490125 loops=1)"
"  ->  Hash  (cost=4884.22..4884.22 rows=4677 width=4) (actual time=29.693..29.693 rows=31 loops=1)"
"        Buckets: 8192  Batches: 1  Memory Usage: 66kB"
"        ->  Seq Scan on storagepricingapp_storagelocation  (cost=0.00..4884.22 rows=4677 width=4) (actual time=0.801..29.661 rows=31 loops=1)"
"              Filter: (_st_distance(geography(location), '0101000020E6100000B04CF0FF963F5AC0AD4886EB90DC4340'::geography, '0'::double precision, false) <= '8046.72'::double precision)"
"              Rows Removed by Filter: 13988"
"Planning time: 0.382 ms"
"Execution time: 42950.661 ms"



Отключаю segscan:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
set enable_seqscan = off;
explain analyze 
	SELECT "storagepricingapp_storageunit"."id"

FROM "storagepricingapp_storageunit" 

	INNER JOIN "storagepricingapp_storagelocation" 

		ON ("storagepricingapp_storageunit"."facility_id" = "storagepricingapp_storagelocation"."id") 

WHERE ST_DistanceSphere("storagepricingapp_storagelocation"."location", ST_GeomFromEWKB('\x0101000020e6100000b04cf0ff963f5ac0ad4886eb90dc4340'::bytea)) <= 8046.72





"Nested Loop  (cost=58.65..16423015.28 rows=10162748 width=4) (actual time=81.540..15493.680 rows=118421 loops=1)"
"  ->  Index Scan using storagepricingapp_storagelocation_pkey on storagepricingapp_storagelocation  (cost=0.29..13925.21 rows=4677 width=4) (actual time=50.875..148.087 rows=31 loops=1)"
"        Filter: (_st_distance(geography(location), '0101000020E6100000B04CF0FF963F5AC0AD4886EB90DC4340'::geography, '0'::double precision, false) <= '8046.72'::double precision)"
"        Rows Removed by Filter: 13988"
"  ->  Bitmap Heap Scan on storagepricingapp_storageunit  (cost=58.36..3474.21 rows=3425 width=8) (actual time=8.376..493.988 rows=3820 loops=31)"
"        Recheck Cond: (facility_id = storagepricingapp_storagelocation.id)"
"        Heap Blocks: exact=14184"
"        ->  Bitmap Index Scan on storagepricingapp_storageunit_e32a5395  (cost=0.00..57.51 rows=3425 width=0) (actual time=3.144..3.144 rows=3820 loops=31)"
"              Index Cond: (facility_id = storagepricingapp_storagelocation.id)"
"Planning time: 46.526 ms"
"Execution time: 15502.063 ms"




Как заставить планировщик использовать индекс?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.07.2017, 10:03
    #39486290
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Планировщик не использует индекс
kalombo,

в зависимости от числа локаций в радиусе.

попробуйте навязать так, например:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
with L as (SELECT id as facility_id FROM storagepricingapp_storagelocation lo

WHERE ST_DistanceSphere(lo."location"
,ST_GeomFromEWKB('\x0101000020e6100000b04cf0ff963f5ac0ad4886eb90dc4340'::bytea)) <= 8046.72)

SELECT su."id"
FROM "storagepricingapp_storageunit" su 
	INNER JOIN L
		USING(facility_id)


а лучше подумайте над тем, нельзя ли как--то гист индекс прикрутить: https://habrahabr.ru/post/228023/
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.07.2017, 15:43
    #39486657
kalombo
kalombo
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Планировщик не использует индекс
qwwqkalombo,

в зависимости от числа локаций в радиусе.

попробуйте навязать так, например:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
with L as (SELECT id as facility_id FROM storagepricingapp_storagelocation lo

WHERE ST_DistanceSphere(lo."location"
,ST_GeomFromEWKB('\x0101000020e6100000b04cf0ff963f5ac0ad4886eb90dc4340'::bytea)) <= 8046.72)

SELECT su."id"
FROM "storagepricingapp_storageunit" su 
	INNER JOIN L
		USING(facility_id)



Не помогло....

qwwqа лучше подумайте над тем, нельзя ли как--то гист индекс прикрутить: https://habrahabr.ru/post/228023/

Я так понял, проблема не в индексе для location, а в том, что не используется индекс для facility_id. Я пробовал заставить планировщик использовать индекс путем использования St_DWithin и преобразования geometry в geography - не помогло. Здесь результаты http://dumpz.org/2622879/

1 - seqscan off, 2 - geometry и ST_DistanceSphere, 3 - geography и ST_DWithin

Но в любом случае за статью спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.07.2017, 16:05
    #39486717
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Планировщик не использует индекс
kalomboНе помогло....

а план с WITH есть ? (можно на ANY( ARRAY(SELECT id..) переписать.

еще можно через ,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT  su."id"  FROM storagepricingapp_storagelocation lo

WHERE ST_DistanceSphere(lo."location"
,ST_GeomFromEWKB('\x0101000020e6100000b04cf0ff963f5ac0ad4886eb90dc4340'::bytea)) <= 8046.72)

, LATERAL (SELECT su."id"
FROM "storagepricingapp_storageunit" su 
WHERE su.facility_id = lo.id
LIMIT 10000 -- тут нужна ручная оценка сверху 
                    -- на максимальное число по записей su на локацию
) AS su


kalomboqwwqа лучше подумайте над тем, нельзя ли как--то гист индекс прикрутить: https://habrahabr.ru/post/228023/

Я так понял, проблема не в индексе для location, а в том, что не используется индекс для facility_id. Я пробовал заставить планировщик использовать индекс путем использования St_DWithin и преобразования geometry в geography - не помогло. Здесь результаты http://dumpz.org/2622879/

1 - seqscan off, 2 - geometry и ST_DistanceSphere, 3 - geography и ST_DWithin

Но в любом случае за статью спасибо.
если суметь загнать поиск локации на гист, планер мог бы четче оценить что искать нестед--лупом по фасилити дешевле. (оценить число локаций в радиусе более точно) . далее можно доп. фильтр более точным _St_DistanceSferoid наложить.


ещй костами можно поиграть. снизить рендом-кост с 4 до 2 например.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.07.2017, 18:37
    #39486870
Alexius
Alexius
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Планировщик не использует индекс
kalombo,

проблема в том, что планировщик не может оценить корректно число строк, подходящих под условие
Код: sql
1.
ST_DistanceSphere("storagepricingapp_storagelocation"."location", ST_GeomFromEWKB('\x0101000020e6100000b04cf0ff963f5ac0ad4886eb90dc4340'::bytea)) <= 8046.72



и считает что под него попадает треть строк. если есть уверенность, что под это условие не будет попадать сильно много строк, то можно попробовать прибить nested loop с помощью lateral join или в варианте с with выше дописать limit 100-1000 (максимальное число строк, которое по идее будет под условие с ST_DistanceSphere подходить).

еще может получиться нужный план при добавлении order by "storagepricingapp_storageunit"."id". если база помещается в память или используются ssd диски, то можно уменьшить аккуратно random_page_cost.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Планировщик не использует индекс / 5 сообщений из 5, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 577ms
Закрыть
start [/forum/topic.php?fid=53&mobile=1&tid=1996379]:
 0ms
get settings:
 11ms
get forum list:
 11ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 137ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 46ms
get tp. blocked users:
 2ms
others: 350ms
total:  577ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]