ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Планировщик не использует индекс
5 сообщений из 5, страница 1 из 1
Планировщик не использует индекс
    #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, 08:45
| Ответить | Цитировать | Написать  
Планировщик не использует индекс
    #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, 10:03
| Ответить | Цитировать | Написать  
Планировщик не использует индекс
    #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, 15:43
| Ответить | Цитировать | Написать  
Планировщик не использует индекс
    #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, 16:05
| Ответить | Цитировать | Написать  
Планировщик не использует индекс
    #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
11.07.2017, 18:37
| Ответить | Цитировать | Написать  
5 сообщений из 5, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Планировщик не использует индекс
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39486657&tid=1996379]:
 0ms
get settings:
 9ms
get forum list:
 11ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 34ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 47ms
get tp. blocked users:
 2ms
others: 11ms
total:  136ms
созд. / загр.: 136ms
x
x
Закрыть


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