主页 / dba / 问题 / 59922
Accepted
Dr.YSG
Asked: 2014-03-01 08:26:56 +0800 CST

PostgreSQL 中复合索引中的列顺序（和查询顺序）

  • 772

我有一张有 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'))
postgresql postgis

3 个回答

  1. Best Answer

    作为我工作的一部分，我维护了一个相当大的 PostgreSQL 数据库（磁盘上大约 120gb，几个数百万行的表），并收集了一些关于如何加快查询速度的技巧。首先对您的假设发表一些评论：

    1. 是的，顺序很重要，但真正不同的只有第一个，其余的都是二等索引。
    2. 我不确定它是否总是同时使用两者，我的猜测是查询计划器将使用#1，然后对其余部分做一些聪明的事情。
    3. 我对 GIST 没有经验。
    4. 是的，首先添加所有索引，看看什么是使用最多的，什么提供了最好的性能。
    5. 我建议您尝试两者并衡量最有效的方法。尝试用不同的子查询重写 sql，也许国家和时间合二为一，然后加入 intersect-query。我没有注意到 IN 子句的任何性能问题，只要 IN 列表不是数千个元素长。我的猜测是，根据可用输入标准专门调整的一些不同查询将产生最佳结果。

    6. 我建议不要制作 4 路索引。尝试创建一个然后检查大小，它们会变得非常大。根据我的经验，四个 1 键索引几乎与单个 4 路索引一样快。对某些特定查询非常有效的一个技巧是部分索引，例如：

      CREATE INDEX ON table_x (key1, key2, key3) WHERE some_x_column = 'XXXX';

    我在我的 .psqlrc 文件中创建了带有查询的别名，以帮助查找要添加或删除的索引。随意在 GitHub 上查看它们：.psql

    我经常使用 :seq_scans 和 :bigtables ，然后使用 \d table_name 来获取有关表的详细信息。完成一些更改后不要忘记重置统计信息，选择 pg_stat_reset();

    • 11

  2. 以我的经验，如果你想获取相当多的行，复合索引只有在你的 SELECT 和查询的 WHERE 子句中添加所有字段时才能大大提高你的性能，这样 Postgres 将运行Index-Only扫描。

    在使用导致仅索引扫描的 5 字段复合索引而不是单字段索引后，我的查询速度提高了大约两到三倍。

    此外，根据我的经验，与单字段索引相比，5 字段索引使用的磁盘空间大约是单字段索引的两倍，两者都具有相同的第一个字段。（在大约 1 亿行的数据库上测试）

    最后，感谢Claes 的精彩提示，特别是关于部分索引的提示。我肯定会试一试。

    • 2

  3. 我认为最有可能帮助的事情（如果有的话）是将 product_type 作为第二列添加到 gist 索引中。但是，在不知道有多少行与您的典型/有问题的查询的每个 AND 条件（单独）匹配的情况下，我们只能猜测。

    当我处理这个问题时，我做的第一件事是以简化的形式运行查询，其中 WHERE 子句只有一个条件，每个条件依次执行，在 EXPLAIN ANALYZE 下。查看每一行的估计行和实际行。

    • 1

相关问题