ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Индекс для ARRAY и POLYGON без PostGIS
10 сообщений из 10, страница 1 из 1
Индекс для ARRAY и POLYGON без PostGIS
    #38662108
Victor Borg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Victor Borg
Гость
Доброго Вам здоровья!

Есть таблица с геоданными
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
-- DROP TABLE "public"."restaurants_geo";
CREATE TABLE "public"."restaurants_geo" (
    "id" bigint NOT NULL DEFAULT nextval('restaurants_geo_id_seq'::regclass),
    "restaurant_id" bigint,
    "latlng" point,
    "zips" integer[],
    "poly" polygon,
    "dt" integer
) WITHOUT OIDS;

-- Indexes

CREATE INDEX restaurants_geo_idx_poly_zips ON restaurants_geo USING gist (poly, zips);
CREATE INDEX restaurants_geo_idx_zips_poly ON restaurants_geo USING gist (zips, poly);


 
Немного данных
Код: sql
1.
2.
3.
INSERT INTO "restaurants_geo" ("id", "restaurant_id", "latlng", "zips", "poly", "dt") VALUES ('13', '4804', '(39.957675999999999,-75.206254999999999)', '{19139}', '((39.952874000000001,-75.251119000000003),(39.952874000000001,-75.210954000000001),(39.969459999999998,-75.210954000000001),(39.969459999999998,-75.251119000000003))', '1');
INSERT INTO "restaurants_geo" ("id", "restaurant_id", "latlng", "zips", "poly", "dt") VALUES ('14', '4804', '(39.957675999999999,-75.206254999999999)', '{19104,19121,19130,19131,19139,19143,19146}', '((39.974331436142002,-75.206254999999999),(39.972099523590003,-75.195388375072),(39.966002196611001,-75.187435132057004),(39.957673971750999,-75.184526334988007),(39.949346761015001,-75.187439715153005),(39.943251462286,-75.195392958168),(39.941020563857997,-75.206254999999999),(39.943251462286,-75.217117041831997),(39.949346761015001,-75.225070284847007),(39.957673971750999,-75.227983665012005),(39.966002196611001,-75.225074867942993),(39.972099523590003,-75.217121624927998))', '1');
INSERT INTO "restaurants_geo" ("id", "restaurant_id", "latlng", "zips", "poly", "dt") VALUES ('15', '4804', '(39.957675999999999,-75.206254999999999)', '{19102,19109,19148,19151,19153,19023,19050,19072,19082,19096,19103,19104,19106,19107,19121,19122,19123,19130,19129,19131,19132,19133,19139,19140,19142,19143,19145,19146,19147,19004,19066}', '((40.007642308424998,-75.206254999999999),(40.000943524606001,-75.173641360095004),(39.982645456151999,-75.149781643707001),(39.957657745760997,-75.141069015274994),(39.932679162479999,-75.149822891560007),(39.914399348266002,-75.173682607985995),(39.907709691575,-75.206254999999999),(39.914399348266002,-75.238827392014002),(39.932679162479999,-75.262687108440005),(39.957657745760997,-75.271440984725004),(39.982645456151999,-75.262728356292996),(40.000943524606001,-75.238868639904993))', '2');




zips - содержит массив zipcodes
poly - полигон соответственно

Нужно найти записи которые соответствуют в которых есть нужный zip И заданная точка входит в полигон.
Код: sql
1.
2.
SET enable_seqscan = OFF;
select * from restaurants_geo where zips @> ARRAY[19130] and poly @> point '(39.957676,-75.206255)'



Хочется полного попадания в индекс т.е. и по zips и по poly
Код: sql
1.
2.
3.
4.
QUERY PLAN
Index Scan using restaurants_geo_idx_zips_poly on restaurants_geo  (cost=0.13..8.15 rows=1 width=100)
  Index Cond: (zips @> '{19130}'::integer[])
  Filter: (poly @> '(39.957676,-75.206255)'::point)


Я знаю о PostGIS - задание обойтись без него

Есть варианты ?

Спасибо.
...
Рейтинг: 0 / 0
05.06.2014, 05:17:24
| Ответить | Цитировать | Написать  
Индекс для ARRAY и POLYGON без PostGIS
    #38662449
Фотография Ёш
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ёш
Участник
Victor Borg,

использовать два индекса может только Bitmap Index Scan
...
Рейтинг: 0 / 0
05.06.2014, 12:42:07
| Ответить | Цитировать | Написать  
Индекс для ARRAY и POLYGON без PostGIS
    #38662728
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
ЁшVictor Borg,

использовать два индекса может только Bitmap Index Scanу него один составной, и он хотел бы его использовать для проверки обоих пространственных условий.
но в случае пространственных индексов это видимо не работает.


я вот тоже сижу - морщу репу.
задача у меня такая:
есть поле, скажем at timestamp[] надо придумать индексирование для шустрой проверки
Код: sql
1.
2.
--псевдокод
any(at) BETWEEN :start_t AS :end_t


ну или сккрестить как-то array[] с range-ем (tsrange ).
типа:
Код: sql
1.
'[2014-03-01,2014-03-02)'::tsrange @> any(at)


но так, чтобы индексно сие проистекало

пока кроме как грубо дискретизировать перед кладкой в [], и искать как overlep с [] дискретов из диапазона - в голову ничё нейдёт. видимо про унутреннюю механнику gin-ов придется чтить.
...
Рейтинг: 0 / 0
05.06.2014, 15:52:27
| Ответить | Цитировать | Написать  
Индекс для ARRAY и POLYGON без PostGIS
    #38662757
smagen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
smagen
Участник
Victor Borg,
У gist-индекса для полигонов нет поддержки поиска polygon @> point. Зато есть поддержка практически всех операторов с другими полигонами. Попробуйте сделать поиск с вырожденным полигоном:
Код: sql
1.
select * from restaurants_geo where zips @> ARRAY[19130] and poly @> polygon '((39.957676,-75.206255))'


Кром этого, в дальнейших экспериментах не забудьте попробовать gist__intbig_ops вместо gist__int_ops.
...
Рейтинг: 0 / 0
05.06.2014, 16:26:11
| Ответить | Цитировать | Написать  
Индекс для ARRAY и POLYGON без PostGIS
    #38662850
Фотография Misha Tyurin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Misha Tyurin
Участник
smagen,

как движок гиста совмещает внутри массив с полигоном?) вот в чем вопрос)
...
Рейтинг: 0 / 0
05.06.2014, 17:55:54
| Ответить | Цитировать | Написать  
Индекс для ARRAY и POLYGON без PostGIS
    #38662973
Victor Borg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Victor Borg
Гость
smagen,

Спасибо, поиск с вырожденным полигоном действительно работает.
Обязательно попробую bigint, но теоретически зачем ? у меня влазит все в int4, int8 за собой тянет увеличение таблицы, индекса.
Может там как-то хитро оптимизированно именно для int8?
...
Рейтинг: 0 / 0
05.06.2014, 20:48:05
| Ответить | Цитировать | Написать  
Индекс для ARRAY и POLYGON без PostGIS
    #38663030
Victor Borg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Victor Borg
Гость
smagen,

Двойное спасибо за gist__intbig_ops !
Влияет на количество элементов в массиве (а не на размер элементов), с gist__int_ops при вставке большого количества элементов выдает ошибку о не хватке памяти.

Всем спасибо.
...
Рейтинг: 0 / 0
05.06.2014, 22:34:41
| Ответить | Цитировать | Написать  
Индекс для ARRAY и POLYGON без PostGIS
    #38663531
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
qwwq.......... пока кроме как грубо дискретизировать перед кладкой в [], и искать как overlap с [] дискретов из диапазона - в голову ничё нейдёт....

вот примерная реализация идеи
 


Код: sql
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.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
-- Function: test.date_part(text, anyarray)
CREATE OR REPLACE FUNCTION test.date_part(part text,from_array anyarray)
  RETURNS integer[] --for btree_gist
AS
$BODY$
	SELECT ARRAY( 
		SELECT date_part(part, unnest(from_array))::integer u	
		) foo
	;	
$BODY$
  LANGUAGE sql IMMUTABLE SECURITY DEFINER
  COST 100;
COMMENT ON FUNCTION test.date_part(text, anyarray) IS 'Возвращает массив частей массива timestamp-oв или date-ов';
---------------------------
CREATE INDEX 
  ON test.test_20140201
  USING gist
  ((test.date_part('year',dates_oper)),(test.date_part('month',dates_oper)),(test.date_part('day',dates_oper)),(test.date_part('hour',dates_oper)));
---------------------
SELECT * FROm test.test_20140201
WHERE 
	 ( test.date_part('year',dates_oper) )&& ARRAY[2014]
	AND	( (( test.date_part('month',dates_oper) )&& ARRAY[2]
			AND  ( test.date_part('day',dates_oper) )&& ARRAY[28,29,30,31])
			)
		OR ( ( test.date_part('month',dates_oper) )&& ARRAY[3]
		AND  ( test.date_part('day',dates_oper) )&& ARRAY[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17])	
	--AND  ( test.date_part('hour',dates_oper) )&& ARRAY[..............]

	AND 
	'[2014-02-28 00:00:00,2014-03-17 00:00:00)'::tsrange @> any(dates_oper)
------------------------------------------------------
Bitmap Heap Scan on test_20140201  (cost=9.83..15.12 rows=1 width=162) (actual time=0.992..3.846 rows=47 loops=1)
  Recheck Cond: (((test.date_part('year'::text, dates_oper) && '{2014}'::integer[]) AND (test.date_part('month'::text, dates_oper) && '{2}'::integer[]) AND (test.date_part('day'::text, dates_oper) && '{28,29,30,31}'::integer[])) OR ((test.date_part('month'::text, dates_oper) && '{3}'::integer[]) AND (test.date_part('day'::text, dates_oper) && '{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}'::integer[])))
  Filter: (((test.date_part('year'::text, dates_oper) && '{2014}'::integer[]) AND (test.date_part('month'::text, dates_oper) && '{2}'::integer[]) AND (test.date_part('day'::text, dates_oper) && '{28,29,30,31}'::integer[])) OR ((test.date_part('month'::text, dates_oper) && '{3}'::integer[]) AND (test.date_part('day'::text, dates_oper) && '{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}'::integer[]) AND ('["2014-02-28 00:00:00","2014-03-17 00:00:00")'::tsrange @> ANY (dates_oper))))
  ->  BitmapOr  (cost=9.83..9.83 rows=1 width=0) (actual time=0.312..0.312 rows=0 loops=1)
        ->  Bitmap Index Scan on test_20140201_date_part_date_part1_date_part2_date_part3_idx  (cost=0.00..5.04 rows=1 width=0) (actual time=0.065..0.065 rows=0 loops=1)
              Index Cond: ((test.date_part('year'::text, dates_oper) && '{2014}'::integer[]) AND (test.date_part('month'::text, dates_oper) && '{2}'::integer[]) AND (test.date_part('day'::text, dates_oper) && '{28,29,30,31}'::integer[]))
        ->  Bitmap Index Scan on test_20140201_date_part_date_part1_date_part2_date_part3_idx  (cost=0.00..4.79 rows=1 width=0) (actual time=0.246..0.246 rows=47 loops=1)
              Index Cond: ((test.date_part('month'::text, dates_oper) && '{3}'::integer[]) AND (test.date_part('day'::text, dates_oper) && '{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}'::integer[]))
Total runtime: 3.935 ms
---------------------------------------------------
SELECT * FROm test.test_20140201
WHERE 	
	'[2014-02-28 00:00:00,2014-03-17 00:00:00)'::tsrange @> any(dates_oper);
-----------------------------------------------------
Seq Scan on test_20140201  (cost=0.00..4703.06 rows=4915 width=162) (actual time=18.144..55.262 rows=47 loops=1)
  Filter: ('["2014-02-28 00:00:00","2014-03-17 00:00:00")'::tsrange @> ANY (dates_oper))
  Rows Removed by Filter: 100489
Total runtime: 55.406 ms
------------------------------------------------------


вроде бы выигрыш в наличии

очевидно:
1. по воможности уменьшить число массивов-"частей" массива == частей индекса, ( в т.ч., чтобы реже впадать в Bitmap Or)
2. по возможности удешевить ф-ю (т.к. от recheck-ов по ф-ному выражению избавиться снаружи не удастся). (реальное условие будет много сложней)
...
3. придется писать генератор условий запроса по заданному диапазону. Возможно, с отказом от индексного "префикса" -- для слишком широких диапазонов - чтобы избавиться от дорогого счета при фулсканах, и в recheck -- для случая, близкого к full-cscan-у



но, возможно, есть более простые решения ?
а то у меня глаз замылился.
что может предложить ALL ?

среднее количество записей в массиве dates_oper -"сэм-восэм". точнее -- около 4.5. Но иногда бывает немногим больше 10-20.
//мне желательно ещё иметь возможность всё это совать в btree_gist (экстеншн такой) индекс (т.к. реальная картинка несколько сложнее). а он, вроде бы , не для любого _ops возможен
...
Рейтинг: 0 / 0
06.06.2014, 13:16:59
| Ответить | Цитировать | Написать  
Индекс для ARRAY и POLYGON без PostGIS
    #38664270
smagen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
smagen
Участник
Victor Borg,

А какая ошибка возникает при использовании gist__int_ops? Обычно просто индекс становится неэффективным.

Misha Tyurin,

gist начинает сплитить данные по второму ключу, только если первый ключ совпадает. Надеяться, что будет много повторяющихся полигонов не приходится. Тем не менее есть надежда, что сигнатура по второму ключу будет не везде содержать одни единицы, и всё равно будет полезна при поиске.
...
Рейтинг: 0 / 0
06.06.2014, 23:29:03
| Ответить | Цитировать | Написать  
Индекс для ARRAY и POLYGON без PostGIS
    #38665610
Фотография Misha Tyurin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Misha Tyurin
Участник
smagen,

ок! интересно... )
...
Рейтинг: 0 / 0
09.06.2014, 18:18:04
| Ответить | Цитировать | Написать  
10 сообщений из 10, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Индекс для ARRAY и POLYGON без PostGIS
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=38663030&tid=1998638]:
 0ms
get settings:
 11ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 277ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 49ms
get tp. blocked users:
 2ms
others: 210ms
total:  586ms
созд. / загр.: 586ms
x
x
Закрыть


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