Как я могу создать индекс по столбцу широты/долготы, используя GIST в сочетании с логическим столбцом?

CREATE INDEX ON products USING gist (
   st_geographyfromtext(
      'SRID=4326;POINT(' || longitude || ' ' || latitude || ')'
   )
) WHERE is_location_independent;

Разве предложение WHERE здесь не задом наперед? Какой смысл индексировать местоположения для точек, где местоположения не имеют значения?

Если я создам индекс с отрицательным WHERE, то с небольшим изменением вашего запроса я смогу заставить его использовать оба индекса с BitmapOr.

explain SELECT COUNT(*) FROM products WHERE 
(
    ST_DWithin( ST_GeographyFromText( 'SRID=4326;POINT(' || longitude || ' ' || latitude || ')' ), ST_GeographyFromText('SRID=4326;POINT(13.621700 45.940900)'), 800000 )
    AND 
    not is_location_independent
) 
OR is_location_independent;

Дает план

Aggregate  (cost=63.09..63.10 rows=1 width=8)
   ->  Bitmap Heap Scan on products  (cost=8.53..63.09 rows=2 width=0)
         Recheck Cond: ((st_dwithin(st_geographyfromtext((((('SRID=4326;POINT('::text || (longitude)::text) || ' '::text) || (latitude)::text) || ')'::text)), '0101000020E6100000AED85F764F3E2B40386744696FF84640'::geography, '800000'::double precision, true) AND (NOT is_location_independent)) OR is_location_independent)
         Filter: ((st_dwithin(st_geographyfromtext((((('SRID=4326;POINT('::text || (longitude)::text) || ' '::text) || (latitude)::text) || ')'::text)), '0101000020E6100000AED85F764F3E2B40386744696FF84640'::geography, '800000'::double precision, true) AND (NOT is_location_independent)) OR is_location_independent)
         ->  BitmapOr  (cost=8.53..8.53 rows=2 width=0)
               ->  Bitmap Index Scan on products_st_geographyfromtext_idx1  (cost=0.00..4.38 rows=1 width=0)
                     Index Cond: (st_geographyfromtext((((('SRID=4326;POINT('::text || (longitude)::text) || ' '::text) || (latitude)::text) || ')'::text)) && _st_expand('0101000020E6100000AED85F764F3E2B40386744696FF84640'::geography, '800000'::double precision))
               ->  Bitmap Index Scan on products_is_location_independent_idx  (cost=0.00..4.14 rows=2 width=0)
                     Index Cond: (is_location_independent = true)

Без ручного переписывания запроса, включающего явное «не is_location_independent» И, объединенное с функцией geography, планировщик не сможет понять, что он может использовать индекс.

С другой стороны, вы можете сделать индекс без предложения WHERE, и тогда он будет работать независимо от того, перепишете ли вы запрос, как я предлагаю, или нет. Поскольку подавляющее большинство ваших записей зависит от местоположения, создание частичного индекса не принесет большой эффективности.

Я создал 3 индекса ниже. Первые 2 используются в вашем исходном запросе, 1-й и 3-й используются в моей перезаписи. Я также сделал версию первого индекса, которая была отфильтрована только для WHERE is_location_indepedent, и эта версия будет использоваться вместо оригинала.

"products_is_location_independent_idx" btree (is_location_independent)
"products_st_geographyfromtext_idx" gist (st_geographyfromtext(((('SRID=4326;POINT('::text || longitude) || ' '::text) || latitude) || ')'::text))
"products_st_geographyfromtext_idx1" gist (st_geographyfromtext(((('SRID=4326;POINT('::text || longitude) || ' '::text) || latitude) || ')'::text)) WHERE NOT is_location_independent

Нет смысла включать is_location_independent в индекс, так как у вас есть OR в условии WHERE.

Есть две возможности:

• Если во многих строках is_location_independent установлено в TRUE, PostgreSQL всегда будет использовать последовательное сканирование, поскольку оно является самым быстрым.

• Если только в нескольких строках is_location_independent установлено в TRUE, создайте второй индекс: обычный индекс B-дерева только для is_location_independent.

  Затем вы можете получить сканирование индекса растрового изображения и растровое изображение или для ускорения запроса.