ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / gist и btree в одном запросе
3 сообщений из 3, страница 1 из 1
gist и btree в одном запросе
    #39634253
olzhas
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
olzhas
Гость
Всем привет! Вопрос к тем у кого есть опыт работы с расширением postgis и gist индексами.

Пример. у меня есть таблица

Код: plsql
1.
2.
3.
4.
5.
create table path (
  rn bigint,
  from_geo geography,
  to_geo geography
)


и 2 индекса, один обычный второй gist

Код: plsql
1.
2.
create unique index path_rn_idx on path (rn);
create index path_from_idx ON path USING GIST (from_geo);



у меня стори задача посика ближайших соседей, и gist индекс прекрасно с этим справляется.

Код: sql
1.
2.
3.
select *
from path p
where ST_DWithin(ST_GeomFromText('SRID=4326;POINT(72.181037902832 50.8136558532715)'),p.from_geo,1000);



Теперь далее стоит задача добавить еще поля в фильтр.
проблема заключается в том что если искать по нескольким полям всегда будет использоваться gist индекс

Код: sql
1.
2.
3.
4.
explain analyse
select *
from path p
where ST_DWithin(ST_GeomFromText('SRID=4326;POINT(72.181037902832 50.8136558532715)'),p.from_geo,50000) and rn =20;



получаем такой план

Код: plsql
1.
2.
3.
4.
5.
6.
Index Scan using path_from_idx on path p  (cost=0.28..8.68 rows=1 width=72) (actual time=10.698..10.698 rows=0 loops=1)
  Index Cond: (from_geo && '0101000020E6100000FEFFFF1F960B5240020000E025684940'::geography)
  Filter: ((rn = 20) AND ('0101000020E6100000FEFFFF1F960B5240020000E025684940'::geography && _st_expand(from_geo, 50000::double precision)) AND _st_dwithin('0101000020E6100000FEFFFF1F960B5240020000E025684940'::geography, from_geo, 50000::double precision, true))
  Rows Removed by Filter: 21193
Planning time: 0.289 ms
Execution time: 10.727 ms



В данном случае он должен был взять индекс по полую RN, он уникальный и я его специально сделал таким что бы продемонстрировать ошибку оптимизатора.

если же мы пойдем в PG_stats то вообще не увидим статистики для гео полей(vacuum и analyse делалось). Оптимизатор их считает уникальными (поле n_disticnt) видимо поэтому и берет их всегда.

Теперь вопрос как это можно решить? Я знаю про btree_gist расширение, но оно мне не походит.
...
Рейтинг: 0 / 0
21.04.2018, 21:57
| Ответить | Цитировать | Написать  
gist и btree в одном запросе
    #39634256
olzhas
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
olzhas
Гость
Если использовать родной postgres point, то все работает как надо

Код: plsql
1.
2.
3.
4.
5.
6.
insert into t_gist select generate_series(1,100000), point(round((random()*1000)::numeric, 2), round((random()*1000)::numeric, 2));
create index idx_t_gist_1 on t_gist using gist (pos);
create index idx_t_gist_2 on t_gist (id);

explain (analyze,verbose,timing,costs,buffers)
select * from t_gist where circle '((100,100) 10)'  @> pos and id between 20 and 400;



получаем нужный план запроса
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
Bitmap Heap Scan on t_gist  (cost=17.26..21.28 rows=1 width=20) (actual time=0.066..0.066 rows=0 loops=1)
  Recheck Cond: (('<(100,100),10>'::circle @> pos) AND (id >= 20) AND (id <= 400))
  ->  BitmapAnd  (cost=17.26..17.26 rows=1 width=0) (actual time=0.065..0.065 rows=0 loops=1)
        ->  Bitmap Index Scan on idx_t_gist_1  (cost=0.00..5.03 rows=100 width=0) (actual time=0.042..0.042 rows=35 loops=1)
              Index Cond: ('<(100,100),10>'::circle @> pos)
        ->  Bitmap Index Scan on idx_t_gist_2  (cost=0.00..11.98 rows=369 width=0) (actual time=0.020..0.020 rows=381 loops=1)
              Index Cond: ((id >= 20) AND (id <= 400))
Planning time: 0.093 ms
Execution time: 0.088 ms
...
Рейтинг: 0 / 0
21.04.2018, 22:01
| Ответить | Цитировать | Написать  
gist и btree в одном запросе
    #39634316
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
olzhas,

наверное придется переписать запрос так, чтобы gist индекс не мог использоваться, например, через ST_Distance (если не путаю).
получается что тут проблема в оценке селективности условия для && оператора, возможно используется дефолтная (DEFAULT_GEOGRAPHY_SEL 0.000005), с которой получим 1 строку.

статистика на самом деле какая-то для postgis типов собирается, в pg_stats ее не видно, надо pg_statistic смотреть. можно попробовать stats_target для поля поднять и сделать analyze, но у меня в тесте это никак не помогло. я правда сходу не понял что на самом деле там хранится, вот тут вроде она должна использоваться.

а какая версия postgis используется? может в последних релизах что-то улучшили.
...
Рейтинг: 0 / 0
22.04.2018, 11:45
| Ответить | Цитировать | Написать  
3 сообщений из 3, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / gist и btree в одном запросе
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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