在awspostgresql中查询40亿行的表时，查询性能非常慢

rsl1atfo  于 2023-03-08  发布在  PostgreSQL
关注(0)|答案(2)|浏览(119)
    • 此bounty已结束**。此问题的答案可获得+100声望奖励。奖励宽限期将在2小时后结束。Shiplu Mokaddim正在查找规范答案：寻找能回答上述两个问题的响应。但不仅仅关注示例查询。

问题
我们有一个存储用户活动的关系表。如下所示的查询需要77秒!

FROM "site_activity"
WHERE
    (
        NOT "site_activity"."is_deleted"
        AND "site_activity"."user_id" = 68812389
        AND NOT (
            "site_activity"."kind" IN (
                'updated',
                'duplicated',
                'reapplied'
            )
        )
        AND NOT (
            "site_activity"."content_type_id" = 14
            AND "site_activity"."kind" = 'created'
        )
    )
ORDER BY
    "site_activity"."created_at" DESC,
    "site_activity"."id" DESC
LIMIT  9;

查询计划如下所示

QUERY PLAN
--------------------------------------------------------------------------------------------
Limit
    (cost=17750.72..27225.75 rows=9 width=16)
    (actual time=199501.336..199501.338 rows=9 loops=1)
  Output: id, created_at
  Buffers: shared hit=4502362 read=693523 written=37273
  I/O Timings: read=190288.205 write=446.870
  ->  Incremental Sort
      (cost=17750.72..2003433582.97 rows=1902974 width=16)
      (actual time=199501.335..199501.336 rows=9 loops=1)
        Output: id, created_at
        Sort Key: site_activity.created_at DESC, site_activity.id DESC
        Presorted Key: site_activity.created_at
        Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 25kB  Peak Memory: 25kB
        Buffers: shared hit=4502362 read=693523 written=37273
        I/O Timings: read=190288.205 write=446.870
        ->  Index Scan Backward using site_activity_created_at_company_id_idx on public.site_activity
            (cost=0.58..2003345645.30 rows=1902974 width=16)
            (actual time=198971.283..199501.285 rows=10 loops=1)
              Output: id, created_at
              Filter: (
                (NOT site_activity.is_deleted) AND (site_activity.user_id = 68812389)
                AND ((site_activity.kind)::text <> ALL ('{updated,duplicated,reapplied}'::text[]))
                AND ((site_activity.content_type_id <> 14) OR ((site_activity.kind)::text <> 'created'::text))
              )
              Rows Removed by Filter: 14735308
              Buffers: shared hit=4502353 read=693523 written=37273
              I/O Timings: read=190288.205 write=446.870
Settings: effective_cache_size = '261200880kB',
          effective_io_concurrency = '400',
          jit = 'off',
          max_parallel_workers = '24',
          random_page_cost = '1.5',
          work_mem = '64MB'
Planning:
  Buffers: shared hit=344
Planning Time: 6.429 ms
Execution Time: 199501.365 ms
(22 rows)

Time: 199691.997 ms (03:19.692)

表事实

1.它包含的行略多于40亿行。
1.表格结构为

Table "public.site_activity"
    Column      |           Type           | Collation | Nullable |                   Default
----------------+--------------------------+-----------+----------+----------------------------------------------
id              | bigint                   |           | not null | nextval('site_activity_id_seq'::regclass)
created_at      | timestamp with time zone |           | not null |
modified_at     | timestamp with time zone |           | not null |
is_deleted      | boolean                  |           | not null |
object_id       | bigint                   |           | not null |
kind            | character varying(32)    |           | not null |
context         | text                     |           | not null |
company_id      | integer                  |           | not null |
content_type_id | integer                  |           | not null |
user_id         | integer                  |           |          |
Indexes:
    "site_activity_pkey" PRIMARY KEY, btree (id)
    "site_activity_modified_at_idx" btree (modified_at)
    "site_activity_company_id_idx" btree (company_id)
    "site_activity_created_at_company_id_idx" btree (created_at, company_id)
    "site_activity_object_id_idx" btree (object_id)
    "site_activity_content_type_id_idx" btree (content_type_id)
    "site_activity_kind_idx" btree (kind)
    "site_activity_kind_idx1" btree (kind varchar_pattern_ops)
    "site_activity_user_id_idx" btree (user_id)
Foreign-key constraints:
    "site_activity_company_id_fk_site_company_id" FOREIGN KEY (company_id)
        REFERENCES site_company(id) DEFERRABLE INITIALLY DEFERRED
    "site_activity_content_type_id_fk_django_co" FOREIGN KEY (content_type_id)
        REFERENCES django_content_type(id) DEFERRABLE INITIALLY DEFERRED
    "site_activity_user_id_fk_site_user_id" FOREIGN KEY (user_id)
        REFERENCES site_user(id) DEFERRABLE INITIALLY DEFERRED

a. kind被当作enum，在db中我们把它存储为varchar，但是在python中我们把它当作enum，所以值是固定的，里面有大约100个值。
b. content_type_id具有大约80个值。
1.这是价值的分布
a. context实际上是JSON，最大大小为8Mb。
a. 3个content_type_id值占据92%的行。它们是14和19。
a. 3个kind占用了75%的行。它们是created、updated和sent。
a. kind和content_type_id的组合产生460个值，其中1个组合包含35%的行，我们一直在查询中排除它们。
1.复制副本示例的类型为db.r5.12xlarge。24个核心，48vCPU，384 GB内存，存储类型为io1。

问题

1.如果这个表增长到1000亿，我们怎么办？在目前的预测中，这可能在未来3 - 5年内发生。

  1. NoSQL是一个好的解决方案吗？注意，我们并不是只使用id或kind来访问文档。

注解

1.我所介绍的事实可能会使解决方案偏向于在同一主机上复制，然后在多个主机上分片，但如果有其他解决方案可以保持1000亿的规模，我们应该是好的。
1.我们 * 不一定 * 使用AWS。但 * 首选 *。

postgresql

2条答案

按热度按时间
7lrncoxx

7lrncoxx1#

当前计划是扫描已按“created_at”排序的行（使用索引），然后在找到10时停止（也许加上几行来说明关系）传递其余的条件。它认为它会非常快地完成这一点，只经过大约1/73，表的000（27225.75 / 2003433582.97）。但实际上它必须扫描的远不止这些（14735308 /400000000，或者表的1/270），所以它严重的估计错了那部分，不知道是不是因为满足条件的行数估计错了（它认为应该有1902974，我们不知道实际上有多少，因为它很早就停止了，所以停止了计数）或者因为它假设匹配的行将均匀地分布在索引上，而实际上并不是这样。
对你来说最好的索引可能是(user_id, created_at)，这样你就可以跳转到索引中有正确user_id的部分（我假设这是你的选择性的绝大部分来源），然后仍然按照“created_at”的顺序遍历那部分。你可以只在(user_id)上删除原始索引，因为新的列将适用于旧的列适用的任何内容。您还可以在该索引的其他两列之间添加“is_deleted”，因为它不会破坏排序属性，并将提供一些额外的选择性（但可能不多）。然而，在那里添加任何其他列都会破坏排序属性。

赞(0）分享  回复(0）举报  2023-03-08
xdnvmnnf

xdnvmnnf2#

查询

从格式化WHERE子句开始，使其更易于理解。

FROM   site_activity s
WHERE  s.user_id = 68812389
AND    NOT s.is_deleted
AND    s.kind <> ALL ('{updated,duplicated,reapplied}'::text[])
AND    (content_type_id <> 14 OR kind <> 'created')
ORDER  BY s.created_at DESC, s.id DESC
LIMIT  9;

索引

您评论说您总是排除这两种情况下的行，所以这个部分的多列索引是最佳的：

CREATE INDEX ON public.site_activity (user_id, created_at, id)
WHERE  NOT is_deleted
AND   (content_type_id <> 14 OR kind <> 'created')

只有当许多行具有相同的(user_id, created_at)时，添加id才有意义。否则，从索引中删除id。
从索引中排除表中大的、不相关的部分可以为这样大的索引付出代价（但是您可以阻止对索引中涉及的任何列的更改进行HOT更新，包括WHERE子句中的列）。
只有当索引的筛选器是查询中筛选器的明显子集时，才能使用索引。

表格定义

优化你的表定义是值得的，比如：

Column      |     Type     | Nullable |                   Default
----------------+--------------+----------+----------------------------------------------
id              | bigint       | not null | nextval('site_activity_id_seq'::regclass)
user_id         | int          | not null |  -- NOT NULL ??
kind            | smallint     | not null |  -- "around 100 values"
content_type_id | smallint     | not null |  -- "around 80 values"
created_at      | timestamptz  | not null |
modified_at     | timestamptz  | not null |
object_id       | bigint       | not null |
company_id      | int          | not null |
is_deleted      | bool         | not null |
context         | text         | not null |

最重要的是，kind现在占用2个字节，而不是33个字节或更多。

加上重新排列列顺序所带来的大量节省。请参阅：

  • 在PostgreSQL中计算和节省空间

大列context（“最大大小为8Mb”）对于大多数行来说通常会存储在吐司表的行外，因此要使用的元组会缩小到一半大小，这对大多数操作来说都是不同的。
而且我怀疑你的一些索引可能是消耗品。

赞(0）分享  回复(0）举报  2023-03-08
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com