Скорость

[Ezhick]

имеем таблицу с 600 000 000 записей. Один из столбцов - geom. Строим новую таблицу, в которую фильтруем записи где geom ST_Within определенной геометрии. Соотношение внутри-вне примерно 8 к 1. По столбцу geom построен gist индекс.

Что будет быстрее, один запрос ко всей базе, 600 запросов с лимитом в 1 000 000 записей или 600 000 запросов с лимитом в 1000 записей (лимит ставится по полю id, имеющемуся у каждой строки)?

[arzobispo]

Привет!
Посмотрите линк http://gis-lab.info/qa/postgis-work.html, там про пространственный запросы.

[Ezhick]

посмотрел, благодаря чему вспомнил про explain... и о ужас - мой запрос отказывается использовать индекс даже при enable_seqscan=off, делает полное сканирование таблицы...

В чем может быть причина?

[yellow-sky]

Покажите запрос на создание вашей таблицы, вашей вьюхи и сам запрос на выборку.
Индекс строите на таблицу или вьюху?

[Ezhick]

Таблица и индексы

Код: Выделить всё

-- Table: nodes

CREATE TABLE nodes
(
  id bigint NOT NULL,
  "version" integer NOT NULL,
  user_id integer NOT NULL,
  tstamp timestamp without time zone NOT NULL,
  changeset_id bigint NOT NULL,
  geom geometry,
  CONSTRAINT pk_nodes PRIMARY KEY (id),
  CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),
  CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL),
  CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 4326)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE nodes OWNER TO osm;

-- Index: idx_nodes

CREATE INDEX idx_nodes
  ON nodes
  USING btree
  (id, user_id, tstamp, changeset_id);

-- Index: nodes_geom_idx
CREATE INDEX nodes_geom_idx
  ON nodes
  USING gist
  (geom);

запрос

Код: Выделить всё

select id, geom from nodes where ST_Within(nodes.geom, 'ЗДЕСЬ ГЕОМЕТРИЯ В WKB') = TRUE;

вывод от explain

Код: Выделить всё

bitmap Heap Scan on nodes  (cost=8764395.63..18458106.78 rows=91293905 width=108)
    [QUERY PLAN] => Bitmap Heap Scan on nodes  (cost=8764395.63..18458106.78 rows=91293905 width=108)

Может ли быть причиной то, что геометрия, в которой ищем, ОЧЕНЬ большая - вся граница РФ?

[yellow-sky]

Странно. У меня индекс используется. Попробуйте для тестов использовать геометрию меньшего размера - возьмите границу субъекта или района. Планировщик действительно может отказаться от использования индекса, при слишком большом envelope ограничивающей геометрии.
Еще вопрос. Задаете ли вы проекцию ограничивающей геометрии при запросе?

[Ezhick]

Ээээ, а вот можно про проекцию чуть подробнее? Что это и с чем его едят? Я мягко говоря еще ламер в работе с постгисом

[yellow-sky]

Проекция, а вернее SRID - идентификатор пространственной привязки, определяет в какой системе координат\проекции находятся геоданные. При запросе SRID слоя и SRID ограничивающей геометрии должны совпадать, в противном случае результат непредсказуем, как впрочем слабо предсказуемы и действия планировщика.
В скрипте создания таблицы у вас указано ограничение на SRID - 4326 (WGS84).
Это означает, что все заливаемые данные должны находится именно в этой ск. Так же вы должны быть уверенны, что геометрия, которую вы даете для ограничения выборки тоже должна быть в этой же проекции. В этом случае благоразумнее использовать ST_GeomFromText с указанием вторым параметром вашего SRID.

[Ezhick]

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

[yellow-sky]

Тогда пробуйте писать запрос с меньшей геометрией выборки - посмотрим на результат.

UPD. Вчитался в ваш ответ Вы строите границу РФ из точек, которые находятся в той же таблице?

[Ezhick]

Граница строится отдельно, заранее. Хранится в другой таблице. А теперь мне нужно выбрать точки внутри этой границы.

Меньшие ограничивающие геометрии попробую, отпишусь.