我可以在使用数据库后激活 PITR 吗？

Question

Chessbrain

Asked: 2019-10-09 00:41:07 +0800 CST2019-10-09 00:41:07 +0800 CST 2019-10-09 00:41:07 +0800 CST

可怕地执行查询，如何解决

772

select
    user_id,
    count(id) as unread_count
from
    notifications
where
    is_read = false
    and user_id in(select(unnest('{200 user IDs}' :: bigint[])))
group by
    user_id;

问题是，这个查询运行了 1 分钟，有时甚至比这稍长。该表有 32gb 大，并且 user_id 字段上已经有一个索引。

这是一个执行计划

HashAggregate  (cost=123354.81..123629.64 rows=27483 width=16) (actual time=90823.880..90823.972 rows=188 loops=1)
  Group Key: user_id
  ->  Nested Loop  (cost=2.32..123217.40 rows=27483 width=16) (actual time=0.184..90752.136 rows=48571 loops=1)
        ->  HashAggregate  (cost=1.76..2.76 rows=100 width=8) (actual time=0.146..0.577 rows=200 loops=1)
              Group Key: unnest(200 user IDs)
              ->  Result  (cost=0.00..0.51 rows=100 width=8) (actual time=0.021..0.073 rows=200 loops=1)
        ->  Index Scan using ix_notification_user_id on notification  (cost=0.56..1229.40 rows=275 width=16) (actual time=119.659..453.533 rows=243 loops=200)
              Index Cond: (200 user IDs)
              Filter: (NOT is_read)
              Rows Removed by Filter: 368
Planning time: 0.189 ms
Execution time: 90824.196 ms

我尝试了一种使用临时表的解决方案，将 unnest 值插入到临时表中，然后进行比较。但是性能根本没有提高。

我已经运行此查询以查看索引统计信息：

    schemaname,
    tablename,
    reltuples::bigint,
    relpages::bigint,
    otta,
    round(case when otta = 0 then 0.0 else sml.relpages / otta::numeric end, 1) as tbloat,
    relpages::bigint - otta as wastedpages,
    bs*(sml.relpages-otta)::bigint as wastedbytes,
    pg_size_pretty((bs*(relpages-otta))::bigint) as wastedsize,
    iname,
    ituples::bigint,
    ipages::bigint,
    iotta,
    round(case when iotta = 0 or ipages = 0 then 0.0 else ipages / iotta::numeric end, 1) as ibloat,
    case
        when ipages < iotta then 0
        else ipages::bigint - iotta
    end as wastedipages,
    case
        when ipages < iotta then 0
        else bs*(ipages-iotta)
    end as wastedibytes
    --CASE WHEN ipages < iotta THEN pg_size_pretty(0) ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS wastedisize

    from (
    select
        schemaname,
        tablename,
        cc.reltuples,
        cc.relpages,
        bs,
        ceil((cc.reltuples*((datahdr + ma- (case when datahdr % ma = 0 then ma else datahdr % ma end))+ nullhdr2 + 4))/(bs-20::float)) as otta,
        coalesce(c2.relname, '?') as iname,
        coalesce(c2.reltuples, 0) as ituples,
        coalesce(c2.relpages, 0) as ipages,
        coalesce(ceil((c2.reltuples*(datahdr-12))/(bs-20::float)), 0) as iotta
        -- very rough approximation, assumes all cols

        from (
        select
            ma,
            bs,
            schemaname,
            tablename,
            (datawidth +(hdr + ma-
            (
                case
                when hdr % ma = 0 then ma
                else hdr % ma
            end)))::numeric as datahdr,
            (maxfracsum*(nullhdr + ma-
            (
                case
                when nullhdr % ma = 0 then ma
                else nullhdr % ma
            end))) as nullhdr2
        from
            (
            select
                schemaname,
                tablename,
                hdr,
                ma,
                bs,
                sum((1-null_frac)* avg_width) as datawidth,
                max(null_frac) as maxfracsum,
                hdr +(
                select
                    1 + count(*)/ 8
                from
                    pg_stats s2
                where
                    null_frac <> 0
                    and s2.schemaname = s.schemaname
                    and s2.tablename = s.tablename ) as nullhdr
            from
                pg_stats s,
                (
                select
                    (
                    select
                        current_setting('block_size')::numeric) as bs,
                    case
                        when substring(v, 12, 3) in ('8.0',
                        '8.1',
                        '8.2') then 27
                        else 23
                    end as hdr,
                    case
                        when v ~ 'mingw32' then 8
                        else 4
                    end as ma
                from
                    (
                    select
                        version() as v) as foo ) as constants
            group by
                1,
                2,
                3,
                4,
                5 ) as foo ) as rs
    join pg_class cc on
        cc.relname = rs.tablename
    join pg_namespace nn on
        cc.relnamespace = nn.oid
        and nn.nspname = rs.schemaname
    left join pg_index i on
        indrelid = cc.oid
    left join pg_class c2 on
        c2.oid = i.indexrelid ) as sml
where
    sml.relpages - otta > 0
    or ipages - iotta > 10
order by
    wastedbytes desc,
    wastedibytes desc;

PK 索引和 user_id 索引都超过 5gbwastedsize和超过 500k+ wastedpages。

我的问题是，对此有什么解决方案？这纯粹是一个需要的索引问题，reindex还是我缺少的其他东西？

我不允许更改表的结构，我只需要对其进行优化，以某种方式从 1 分钟以上减少到 1 秒以下

在 user_id where is_read = false 添加部分索引后，查询时间减少了大约 10-15 秒。但这显然还需要很长时间。

编辑：该表中共有 3250 万行。运行此查询：

SELECT t.user_id, COALESCE(unread_count, 0) AS unread_count
FROM   unnest('{200 user_ids}'::bigint[]) t(user_id)
LEFT   JOIN LATERAL (
   SELECT count(*) AS unread_count
   FROM   notification n
   WHERE  n.user_id = t.user_id
   AND    n.is_read = false
   ) sub ON true
;

这个执行计划的结果（有趣的是，昨天运行了超过一分钟，今天运行了约 30 秒或更短时间）：

Nested Loop Left Join  (cost=1209.05..120908.50 rows=100 width=16) (actual time=333.088..27260.557 rows=200 loops=1)
  Buffers: shared hit=1981 read=20396 dirtied=7
  I/O Timings: read=27023.896
  ->  Function Scan on unnest t  (cost=0.00..1.00 rows=100 width=8) (actual time=0.022..0.360 rows=200 loops=1)
  ->  Aggregate  (cost=1209.04..1209.05 rows=1 width=8) (actual time=136.292..136.293 rows=1 loops=200)
        Buffers: shared hit=1981 read=20396 dirtied=7
        I/O Timings: read=27023.896
        ->  Index Only Scan using ix_test on notification n  (cost=0.44..1208.29 rows=300 width=0) (actual time=2.153..136.170 rows=105 loops=200)
              Index Cond: (user_id = t.user_id)
              Heap Fetches: 21088
              Buffers: shared hit=1981 read=20396 dirtied=7
              I/O Timings: read=27023.896
Planning time: 0.135 ms
Execution time: 27260.745 ms

2 个回答

Voted

jjanes · Answer 1 · 2019-10-09T06:42:13+08:00

您的解释计划有点令人困惑，因为看起来索引扫描一次获取所有 200 个 user_id 的数据，但随后执行了 200 次。但是做实验，这不是它正在做的，嵌套循环的每次迭代都是从该列表中获取一个 user_id 的数据，而不是整个列表。所以这只是 EXPLAIN 输出中的一个演示问题。

如果您set track_io_timing = on执行 EXPLAIN (ANALYZE, BUFFERS)，我相信您会发现大部分时间都花在了从磁盘读取数据上。读取随机分散在 32 GB 上的 48571 行并不快，除非所有数据都已缓存在内存中，或者数据位于速度极快的 PCIe SSD 上。

最好的办法是让它使用仅索引扫描，而不是投入一些重要的硬件。对于您显示的查询，需要这样的索引：

create index on notifications (user_id , is_read, id);

在尝试之前先吸尘桌子。如果可行，您将需要考虑如何保持表的良好真空，因为默认的 autovac 设置可能不够用。

我不会担心报告的膨胀。该查询（您从哪里得到的？）报告索引中有大量浪费的字节，即使是在新重新索引的表上也是如此。此外，它报告的 wastedpages 并不是完全空的页面，而是 wastedbytes 除以页面大小。这对我来说似乎很愚蠢。

Erwin Brandstetter · Answer 2 · 2019-10-09T15:42:41+08:00

user_id您的查询从传递的数组中删除。通常，您希望显示那些计数为0.
LEFT JOIN LATERAL .. ON true, 其次是COALESCE负责处理。如果你真的想要那些被淘汰的 switchCROSS JOIN和 drop COALESCE，同样的表现：

SELECT t.user_id, COALESCE(unread_count, 0) AS unread_count
FROM   unnest('{200 user IDs}'::bigint[]) t(user_id)
LEFT   JOIN LATERAL (
   SELECT count(*) AS unread_count
   FROM   notifications n
   WHERE  n.user_id = t.user_id
   AND    n.is_read = false
   ) sub ON true

要点：我已经看到这种查询风格比 large INfollowed by GROUP BYover over 更快。有关的：

使用大 IN 优化 Postgres 查询

次要点： count(*)比count(id)- 快一点，并且在这个查询中等效，因为id是 PK，因此NOT NULL. 看：

Postgres：计数（*）与计数（id）

一个基本的 btree 索引(user_id)对它有好处。由于id无关紧要，因此不包括它。部分索引可能会有所帮助，就像 a_horse 建议的那样：

CREATE INDEX ON notifications (user_id) WHERE is_read = false;

你报告说它减少了 10-15 秒。不过，这可能意味着两件事之一：

涉及大量行is_read = false，这使得索引很有用。
新索引是一个胜利，因为它以原始状态开始，没有膨胀。（你提到了现有索引的大量膨胀。）但是如果只有很少的相关行与is_read = false，那么收益将随着时间的推移而消失，剩下的就是增加的写入成本和更多的占用空间。

问题中没有足够的信息可以说明。输出中的这一点EXPLAIN尚无定论：

Rows Removed by Filter: 368

表中的行数很重要。的份额is_read = false。以及此处建议的其他项目。

可怕地执行查询，如何解决

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

可怕地执行查询，如何解决

2 个回答

相关问题