Dr.YSG's questions

在 PostgreSQL 9.2 中，我可以毫无问题地创建一个同时具有地理 (postGIS) 类型和整数作为复合索引的索引。但是现在（9.6）它抱怨索引的创建，我不明白它提供的提示：

列和数据都已正确创建，Postgres 抱怨创建索引。

ERROR: data type integer has no default operator class for access method "gist" 
HINT: You must specify an operator class for the index 
      or define a default operator class for the data type. 
********** Error**********  
ERROR: data type integer has no default operator class for access method "gist" 
SQL state: 42704 
Hint: You must specify an operator class for the index 
      or define a default operator class for the data type.

架构定义如下：

- Table: portal.inventory

-- DROP TABLE portal.inventory;

CREATE TABLE portal.inventory
(
  type character varying,
  pid integer,
  size bigint,
  date timestamp without time zone,
  path character varying,
  outline geography(Polygon,4326)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE portal.inventory
  OWNER TO postgres;

-- Index: portal.inventory_compound_idx

-- DROP INDEX portal.inventory_compound_idx;

CREATE INDEX inventory_compound_idx
  ON portal.inventory
  USING gist
  (outline, pid);

-- Index: portal.inventory_icompound_idx

-- DROP INDEX portal.inventory_icompound_idx;

CREATE INDEX inventory_icompound_idx
  ON portal.inventory
  USING gist
  (pid, outline);

我在 Windows 10 上运行 PostgreSQL 9.6

它似乎有一个更新监视器，它不使用来自 Windows 的代理的任何信息（我们有一个公司代理）。它也不会查看环境变量 HTTP_PROXY 或 HTTPS_PROXY（我已将其设置为通过 CNTLM 为我们的公司防火墙添加凭据）。这对于需要通过我们的传出防火墙的程序（例如 NPM 和 GIT）很有用。

所以我每隔 10 分钟或几分钟就会收到以下消息，作为一个名为的程序的弹出窗口：

"D:\Program Files (x86)\postgresql\updatemonitor\bin\UpdManager.exe" --execute  "D:\Program Files\PostgreSQL\9.6\bin\stackbuilder.exe"

该消息非常具有欺骗性，但我追踪了该程序，并且是上面列出的那个。

这个问题与我之前问过的一个问题有关：Order of columns in a compound index in PostgreSQL (and query order)

我认为我可以在这里加强和限制我的问题，而不是让这个问题过载。给定以下查询（和 EXPLAIN ANALYZE），我正在创建的复合索引有帮助吗？

第一个查询仅使用简单索引（大纲上的 GIST）和（pid 上的 BTREE）运行。

查询是：

EXPLAIN ANALYZE SELECT DISTINCT ON (path) oid, pid, product_name, type, path, size 
FROM portal.inventory AS inv 
WHERE ST_Intersects(st_geogfromtext('SRID=4326;POLYGON((21.51947021484375 51.55059814453125, 18.9129638671875 51.55059814453125, 18.9129638671875 48.8287353515625, 21.51947021484375 48.8287353515625, 21.51947021484375 51.55059814453125))'), inv.outline) 
AND (inv.pid in (20010,20046)) 

--

结果如下（速度更快，但可能只是因为数据库是热的）。

"Unique  (cost=581.76..581.76 rows=1 width=89) (actual time=110.436..110.655 rows=249 loops=1)"
"  ->  Sort  (cost=581.76..581.76 rows=1 width=89) (actual time=110.434..110.477 rows=1377 loops=1)"
"        Sort Key: path"
"        Sort Method: quicksort  Memory: 242kB"
"        ->  Bitmap Heap Scan on inventory inv  (cost=577.48..581.75 rows=1 width=89) (actual time=39.257..105.878 rows=1377 loops=1)"
"              Recheck Cond: ((pid = ANY ('{20010,20046}'::integer[])) AND ('0103000020E6100000010000000500000000000000FC843540000000007AC6494000000000B8E93240000000007AC6494000000000B8E9324000000000146A484000000000FC84354000000000146A484000000000FC843540000000007AC64940'::geography && outline))"
"              Rows Removed by Index Recheck: 3731"
"              Filter: (_st_distance('0103000020E6100000010000000500000000000000FC843540000000007AC6494000000000B8E93240000000007AC6494000000000B8E9324000000000146A484000000000FC84354000000000146A484000000000FC843540000000007AC64940'::geography, outline, 0::double precision, false) < 1e-005::double precision)"
"              Rows Removed by Filter: 533"
"              ->  BitmapAnd  (cost=577.48..577.48 rows=1 width=0) (actual time=38.972..38.972 rows=0 loops=1)"
"                    ->  Bitmap Index Scan on inventory_pid_idx  (cost=0.00..123.82 rows=6204 width=0) (actual time=1.116..1.116 rows=7836 loops=1)"
"                          Index Cond: (pid = ANY ('{20010,20046}'::integer[]))"
"                    ->  Bitmap Index Scan on inventory_outline_idx  (cost=0.00..453.41 rows=8212 width=0) (actual time=37.765..37.765 rows=63112 loops=1)"
"                          Index Cond: ('0103000020E6100000010000000500000000000000FC843540000000007AC6494000000000B8E93240000000007AC6494000000000B8E9324000000000146A484000000000FC84354000000000146A484000000000FC843540000000007AC64940'::geography && outline)"
"Total runtime: 110.731 ms"

现在这是添加了复合索引的结果：（注意绝对时间较慢）

"Unique  (cost=37.81..37.82 rows=1 width=89) (actual time=2464.353..2464.561 rows=249 loops=1)"
"  ->  Sort  (cost=37.81..37.82 rows=1 width=89) (actual time=2464.349..2464.389 rows=1377 loops=1)"
"        Sort Key: path"
"        Sort Method: quicksort  Memory: 242kB"
"        ->  Bitmap Heap Scan on inventory inv  (cost=33.54..37.80 rows=1 width=89) (actual time=2361.018..2459.653 rows=1377 loops=1)"
"              Recheck Cond: (('0103000020E6100000010000000500000000000000FC843540000000007AC6494000000000B8E93240000000007AC6494000000000B8E9324000000000146A484000000000FC84354000000000146A484000000000FC843540000000007AC64940'::geography && outline) AND (pid = ANY ('{20010,20046}'::integer[])))"
"              Filter: (_st_distance('0103000020E6100000010000000500000000000000FC843540000000007AC6494000000000B8E93240000000007AC6494000000000B8E9324000000000146A484000000000FC84354000000000146A484000000000FC843540000000007AC64940'::geography, outline, 0::double precision, false) < 1e-005::double precision)"
"              Rows Removed by Filter: 533"
"              ->  Bitmap Index Scan on inventory_compound_idx  (cost=0.00..33.53 rows=1 width=0) (actual time=2321.684..2321.684 rows=1910 loops=1)"
"                    Index Cond: (('0103000020E6100000010000000500000000000000FC843540000000007AC6494000000000B8E93240000000007AC6494000000000B8E9324000000000146A484000000000FC84354000000000146A484000000000FC843540000000007AC64940'::geography && outline) AND (pid = ANY ('{20010,20046}'::integer[])))"
"Total runtime: 2558.022 ms"

最后，这是表定义：

CREATE TABLE portal.inventory
(
  oid bigint,
  product_name character varying(100),
  type character varying(25),
  pid integer,
  size bigint,
  date timestamp without time zone,
  path character varying(200),
  outline geography(Polygon,4326)
)
WITH (
  OIDS=FALSE
);


CREATE INDEX inventory_compound_idx
  ON portal.inventory
  USING gist
  (outline, pid);


CREATE INDEX inventory_outline_idx
  ON portal.inventory
  USING gist
  (outline);


CREATE INDEX inventory_pid_idx
  ON portal.inventory
  USING btree
  (pid);

更新：下面列出的问题的答案：

我可以调整表格，但我正在努力使行变细。您的建议各不相同，类型等是我想更改的内容。

基本上，每一行代表有关地理空间图像文件的一些元数据。我们正在管理 50M，这很可能会增长到数亿或更多。在数据库中，每个文件都由一个唯一的 OID 引用（很抱歉该术语重复）。它们按“产品”分组，其中 PID 是产品 ID。每个产品可以有大约 1,000 个 OID。每个图像文件都有一个地理空间边界框（轮廓）。这就是我搜索所需的全部内容。其余数据不会为空（类型是文本字符串，大小是文件大小，日期是文件创建日期，路径是文件的 UNC 文件路径）。

现在这就是为什么我先按大纲，然后按 PID 对查询进行排序。产品将按地理空间分组。因此，波兰克拉科夫的所有 OID 行都将位于物理上的同一区域。所以我假设，如果我将桶缩小到一个小区域，第二个索引将非常小（比如一个城市区域大约 100 种产品）。IN( ..) 子句将退出。

PIDS 的实际值来自我在此处发布的另一个问题。但该表只针对产品，因此其大小约为 30K，这意味着快速搜索而不需要复合查询。

我想知道 POSTGreSQL 规划器是否足够聪明，可以决定如果两个索引都存在，则 (outline,pid) 的复合索引是否比 (pid, outline) 更快。好吧，我想我可以测试一下。

我有一次需要在 25M 行上进行 25M 行的内部连接。盒子是Alienware area 51，4核25GB内存和SATA驱动（非系统盘）。到目前为止，它已经花费了 22 小时。我做了 btree 索引 ID (bigint) 列，这两个表都在进行连接。有小费吗？你觉得我要等多久？

EXPLAIN SELECT
    public.products_by_location_mv.id,
    public.products_by_location_mv."data_object.unique_id",
    public.products_by_location_mv.location AS outline,
    public.products_by_location_mv.elevation_ft,
    public.products_by_location_mv."geo_product.geo_product_id" AS pid,
    public.products_by_location_mv.cntry_name,
    public.products_by_location_mv.product_name,
    public.products_by_location_mv.product_type,
    public.products_by_location_mv.product_producer,
    public.products_by_location_mv.product_size,
    public.products_by_location_mv.do_location,
    public.products_by_location_mv.product_location,
    public.obj4.uid AS oid,
    public.obj4.size_bytes,
    public.obj4.object_date,
    public.obj4.description,
    public.obj4.location AS path
INTO
    public.inventory0
FROM
    public.obj4
INNER JOIN
    public.products_by_location_mv
ON
    (
        public.obj4.id = public.products_by_location_mv.id) ;

"Hash Join  (cost=3825983.03..12908235.27 rows=24202368 width=1356)"
"  Hash Cond: (products_by_location_mv.id = obj4.id)"
"  ->  Seq Scan on products_by_location_mv  (cost=0.00..1457298.68 rows=24202368 width=721)"
"  ->  Hash  (cost=1414691.68..1414691.68 rows=25507868 width=643)"
"        ->  Seq Scan on obj4  (cost=0.00..1414691.68 rows=25507868 width=643)"

我有一张有 50K 行的表。它实际上是一个 PostGIS 表。

查询有 4 个部分（1 个必填）（3 个可选）

1. 具有 4 纬度、经度的交集框（地理矩形）（我使用 st_intersects）[强制]
2. 日期字段的日期范围（最小值、最大值）
3. 当前使用 IN( .....) 的文件类型（一组最多 8 个文本值），但如果需要，我可以将其设为临时表。我看到很多人不喜欢IN。
4. 国家（文本值）。

我预计返回大约 100 - 4,000 行

如果我在表上创建复合索引，我应该首先使用哪一列。细粒度的可能是位置（数据分布在世界各地）。我目前将其作为 GIST 索引。

其他索引将是 BTREE。

我的直觉说使用细粒度，当然最后。例如，只有大约 12 种文件类型，因此对于索引来说这将是非常大的存储桶。

PostgreSQL 和 PostGIS 专家（谁知道系统内部）怎么说？

更新：

让我把这个问题变得尖锐。

1. 我不希望任何人不得不做我应该做的工作。我太尊重你的时间了。所以我稍后会进行解释分析。
2. 我所寻找的只是一些指示、技巧和指南。
3. 我读了这篇优秀的小帖子：https ://devcenter.heroku.com/articles/postgresql-indexes#managing-and-maintaining-indexes关于索引
4. 我通常做的是创建 4 个单独的索引（地理框、国家名称、文件类型和日期），但想看看复合查询会做什么。

告诉我这些假设是否有误。（我对复合索引的想法很陌生）

1. 秩序很重要。选择最能减少行的索引作为第一个索引（在我的情况下，位置（地理）是一个简单的多边形或多多边形会做得最好）。
2. 有时查询会跳过索引。但是，如果我使用键 (#1, #2, #3, #4) 创建复合查询，那么即使用户创建了要求 #1、#3 的内容，规划器仍将使用单个复合查询，因为他们订购得到维护。
3. 通常我会创建三个 BTREE 查询和一个 GIST（用于地理类型）。PostGIS 不支持从多个索引类型创建复合。所以我将不得不使用 GIST 复合索引。但这不应该伤害事情。
4. 如果我确实创建了一些额外的复合或单值索引，计划者足够聪明，可以选择最聪明的一个......
5. Country Name 可以有大约 250 个不同的值，并且显然与位置 (geobox) 密切相关，但如果减少行大小的下一个最佳索引是 file_type，我应该使用下一个。我不希望用户经常在他们的查询集中使用国家或日期。
6. 我不必担心创建 4 个键的复合索引会大大增加索引数据的大小。即，如果一键索引将获得 90% 的性能提升，那么添加 3 个以上的项目以使其复合并没有什么坏处。相反，我真的应该创建两个索引。一个单一的地理索引，也是一个复合索引，让规划者自己决定哪个是最好的，它会考虑索引表的大小。

同样，我不是要求任何人设计我的解决方案，我不会嘲笑其他人的工作。但我确实需要 PostGreSQL 文档没有告诉我有关实施的东西

[我还没有显示 EXPLAIN 结果的原因是我必须从 24M 行表创建这个 25K 行表。花费的时间比我想象的要多。我将事物聚集到 1,000 个项目组中，并让用户查询 25K 行表。但我的下一个问题将涉及使用该查询的结果转到 MASTER 25M 行表并将其提取出来，这就是复合索引的性能真正受到影响的地方]。

下面的示例查询：

SELECT
    public.product_list_meta_mv.cntry_name       AS country,
    public.product_list_meta_mv.product_producer AS producer,
    public.product_list_meta_mv.product_name     AS prod_name,
    public.product_list_meta_mv.product_type     AS ptype,
    public.product_list_meta_mv.product_size     AS size,
    ST_AsGeoJSON(public.product_list_meta_mv.the_geom, 10, 2)          AS outline
FROM
    public.product_list_meta_mv 
WHERE
    public.product_list_meta_mv.cntry_name = 'Poland' 
AND
    ST_Intersects(public.product_list_meta_mv.the_geom,
    st_geogfromtext('SRID=4326;POLYGON((21.23107910156250 51.41601562500000,
                                        18.64379882812500 51.41601562500000,
                                        18.64379882812500 48.69415283203130,
                                        21.23107910156250 48.69415283203130,
                                        21.23107910156250 51.41601562500000))')) 
AND (date >= '1/2/1900 5:00:00 AM' 
 AND date <= '2/26/2014 10:26:44 PM')
AND (public.product_list_meta_mv.product_type in
    ('CIB10','DTED0','DTED1','DTED2','CIB01','CIB05')) ;

解释分析结果（我没有输入任何复合索引，从我看到的速度来看，我不知道是否需要）。

"Bitmap Heap Scan on catalog_full cat  (cost=4.33..37.49 rows=1 width=7428) (actual time=1.147..38.051 rows=35 loops=1)"
"  Recheck Cond: ('0103000020E61000000100000005000000000000005838354000000000AEB0494000000000A0A7324000000000AEB0494000000000A0A73240000000006C5D48400000000058383540000000006C5D4840000000005838354000000000AEB04940'::geography && outline)"
"  Filter: (((type)::text = ANY ('{CADRG,CIB10,DTED1,DTED2}'::text[])) AND (_st_distance('0103000020E61000000100000005000000000000005838354000000000AEB0494000000000A0A7324000000000AEB0494000000000A0A73240000000006C5D48400000000058383540000000006C5D4840000000005838354000000000AEB04940'::geography, outline, 0::double precision, false) < 1e-005::double precision))"
"  Rows Removed by Filter: 61"
"  ->  Bitmap Index Scan on catalog_full_outline_idx  (cost=0.00..4.33 rows=8 width=0) (actual time=0.401..0.401 rows=96 loops=1)"
"        Index Cond: ('0103000020E61000000100000005000000000000005838354000000000AEB0494000000000A0A7324000000000AEB0494000000000A0A73240000000006C5D48400000000058383540000000006C5D4840000000005838354000000000AEB04940'::geography && outline)"
"Total runtime: 38.109 ms"

EXPLAIN ANALYZE SELECT pid,product_name,type,country,date,size,cocom,description,egpl_date,ST_AsGeoJSON(outline, 10, 2) AS outline 
FROM portal.catalog_full AS cat 
WHERE ST_Intersects(st_geogfromtext('SRID=4326;POLYGON((21.2200927734375 51.38031005859375, 18.65478515625 51.38031005859375, 18.65478515625 48.7298583984375, 21.2200927734375 48.7298583984375, 21.2200927734375 51.38031005859375))'), cat.outline) 
AND (cat.type in ('CADRG','CIB10','DTED1','DTED2'))