Eduard Sukharev's questions

我们有用户表：

CREATE UNIQUE INDEX uk_users_login_socnet ON public.users USING btree (login, socnet)

我们对我们的服务器进行了维护，在此期间它被备份并复制到不同的服务器pg_basebackup（由我们的系统管理员完成，据说没有错误和问题）。

从那时起，我们的日志中出现如下错误：

ERROR:  duplicate key value violates unique constraint "uk_users_login_socnet"
DETAIL:  Key (login, socnet)=([email protected], 1) already exists. (UPDATE users SET extra = COALESCE(extra, '') || hstore('udid', '') || hstore('udid_last_update', '1721646485') WHERE id = 1234567;)

同时，以下查询未返回任何结果：

SELECT u1.login, u1.socnet
FROM users u1
INNER JOIN users u2 ON u1.login = u2.login AND u1.socnet = u2.socnet

而它的EXPLAIN ANALYZE是：

Hash Join (cost=2817129.95..6386837.44 rows=9847200 width=18) (actual time=13654.755..31681.796 rows=15584155 loops=1)
    
Hash Cond: (((u1.login)::text = (u2.login)::text) AND (u1.socnet = u2.socnet))
    
-> Seq Scan on users u1 (cost=0.00..2490077.18 rows=15678918 width=18) (actual time=0.026..8806.271 rows=15582973 loops=1)
    
-> Hash (cost=2490077.18..2490077.18 rows=15678918 width=18) (actual time=13653.093..13653.093 rows=15582973 loops=1)
    
Buckets: 524288 Batches: 32 Memory Usage: 28510kB
    
-> Seq Scan on users u2 (cost=0.00..2490077.18 rows=15678918 width=18) (actual time=0.186..10887.626 rows=15582973 loops=1)
    
Planning time: 1.749 ms
    
Execution time: 32166.739 ms

但是这个查询：

SELECT t1.login, t1.socnet, t2.login, t2.socnet
FROM users AS t1
LEFT JOIN (SELECT id, login, socnet FROM users) AS t2
ON t2.login = t1.login AND t1.socnet = t2.socnet
WHERE t1.id != t2.id

login显示具有相同和的重复行的列表socnet，对我来说这毫无意义。以下分别是EXPLAIN ANALYZE：

Hash Join (cost=2817129.95..6411455.44 rows=9847199 width=36) (actual time=17015.349..33466.957 rows=1182 loops=1)
    
Hash Cond: (((t1.login)::text = (users.login)::text) AND (t1.socnet = users.socnet))
    
Join Filter: (t1.id <> users.id)
    
Rows Removed by Join Filter: 15583110
    
-> Seq Scan on users t1 (cost=0.00..2490077.18 rows=15678918 width=22) (actual time=0.034..9902.685 rows=15583110 loops=1)
    
-> Hash (cost=2490077.18..2490077.18 rows=15678918 width=22) (actual time=14344.722..14344.722 rows=15583110 loops=1)
    
Buckets: 524288 Batches: 32 Memory Usage: 30951kB
    
-> Seq Scan on users (cost=0.00..2490077.18 rows=15678918 width=22) (actual time=0.024..11382.363 rows=15583110 loops=1)
    
Planning time: 1.764 ms
    
Execution time: 33467.260 ms

PostgreSQL 服务器版本为 9.6

我的问题是：

• 发生了什么事？为什么 UNIQUE CONSTRAINT 中有重复数据？
• 为什么我对INNER JOIN和得出的结果不同LEFT JOIN？
• DB 结构可能发生了什么？如何调试？在哪里可以找到有关问题出现的提示？
• 如何修复此问题以达到一致状态？

给出下表：

CREATE TABLE chat_message (
    id bigint DEFAULT nextval('public.chat_message_id_seq'::regclass) NOT NULL,
    "user" integer,
    type smallint,
    text text
);
ALTER TABLE ONLY chat_message ADD CONSTRAINT pk_chat_message PRIMARY KEY (id);
CREATE INDEX idx_chat_message_user_type ON chat_message USING btree ("user", type);
CREATE INDEX k_chat_message_user ON chat_message USING btree ("user");

其中类型为1或NULL，则查询：

EXPLAIN ANALYZE
SELECT *
FROM "chat_message" AS t
WHERE true
  AND "type" = 1
  AND "user" = 1234567
ORDER BY "user", "type", "id" ASC
LIMIT 10 OFFSET 0;

给出以下输出：

                                                                       QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=53644.94..53644.97 rows=10 width=127) (actual time=4.817..4.818 rows=6 loops=1)
   ->  Sort  (cost=53644.94..53681.60 rows=14663 width=127) (actual time=4.816..4.816 rows=6 loops=1)
         Sort Key: id
         Sort Method: quicksort  Memory: 26kB
         ->  Bitmap Heap Scan on chat_message t  (cost=362.86..53328.08 rows=14663 width=127) (actual time=1.975..2.181 rows=6 loops=1)
               Recheck Cond: (("user" = 1234567) AND (type = 1::smallint))
               Heap Blocks: exact=3
               ->  Bitmap Index Scan on idx_chat_message_user_type  (cost=0.00..359.19 rows=14663 width=0) (actual time=1.822..1.822 rows=6 loops=1)
                     Index Cond: (("user" = 1234567) AND (type = 1::smallint))
 Planning time: 0.348 ms
 Execution time: 5.028 ms

但是一旦 LIMIT 值降低到某个值以下（在我的本地机器上为 9），那么查询计划就会变为这样：

                                                                        QUERY PLAN                                                                         
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..50193.33 rows=9 width=127) (actual time=23119.188..46005.965 rows=6 loops=1)
   ->  Index Scan using pk_chat_message on chat_message t  (cost=0.56..81775168.50 rows=14663 width=127) (actual time=23119.187..46005.962 rows=6 loops=1)
         Filter: ((type = 1::smallint) AND ("user" = 1234567))
         Rows Removed by Filter: 49452956
 Planning time: 14.840 ms
 Execution time: 46006.683 ms

这实在是太慢了。

对于这个确切的用户来说，存在巨大的数据偏差：它有 50 000 行WHERE type is NULL，但只有 6 行WHERE type = 1。此外，请求相同的 LIMIT 9，但WHERE type is NULL具有完全相同的查询计划，但运行速度很快：

                                                                         QUERY PLAN                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=153793.13..153793.15 rows=9 width=127) (actual time=886.897..886.898 rows=9 loops=1)
   ->  Sort  (cost=153793.13..153909.07 rows=46374 width=127) (actual time=886.894..886.894 rows=9 loops=1)
         Sort Key: gs_type, id
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Bitmap Heap Scan on chat_message t  (cost=1143.90..152826.25 rows=46374 width=127) (actual time=12.561..878.947 rows=49934 loops=1)
               Recheck Cond: (("user" = 1234567) AND (type IS NULL))
               Heap Blocks: exact=10903
               ->  Bitmap Index Scan on idx_chat_message_user_type  (cost=0.00..1132.31 rows=46374 width=0) (actual time=9.942..9.942 rows=49934 loops=1)
                     Index Cond: (("user" = 1234567) AND (type IS NULL))
 Planning time: 0.308 ms
 Execution time: 887.027 ms

在生产服务器上，将完全相同的数据加载到规格与我的笔记本电脑不同的服务器（更多的内存、巨大的shared_buffers、max_mem来自不同其他表的恒定工作负载）中，其行为方式类似，只有限制阈值不同（最高到 75 时很慢Index Scan，然后从 76 开始很快Bitmap Heap Scan+Bitmap Index Scan甚至更高）。

一些附加信息：

SELECT * FROM pg_stat_user_tables WHERE relname = 'chat_message';

relname     |seq_scan   |seq_tup_read   |idx_scan   |idx_tup_fetch  |n_tup_ins  |n_tup_upd  |n_tup_del  |n_tup_hot_upd  |n_live_tup |n_dead_tup |n_mod_since_analyze|last_vacuum|last_autovacuum|last_analyze   |last_autoanalyze   |vacuum_count   |autovacuum_count   |analyze_count  |autoanalyze_count  |
chat_message|0          |0              |11         |197,652,914    |0          |0          |0          |0              |0          |0          |0                  |           |               |               |                   |0              |0                  |0              |0                  |

SELECT * FROM pg_stats where tablename = 'chat_message';

schemaname  |tablename      |attname       |inherited|null_frac|avg_width|n_distinct|most_common_vals
public      |chat_message   |id            |false    |0        |8        |-1        |
public      |chat_message   |user          |false    |0        |4        |30145     |{redacted}
public      |chat_message   |text          |false    |0        |38       |45553     |{redacted}
public      |chat_message   |type          |false    |0.7656   |2        |1         |{1}

我的问题是：

• Index Scan为什么当涉及到那几行时，速度就会变得非常慢？
• 为什么Index Scan总是使用pk_chat_message索引，即使有更合适的索引idx_chat_message_user_type，即使ORDER BY子句包含WHERE子句中的所有字段（order by 影响索引的使用）？
• 为什么它LIMIT N会影响查询计划，因为它Index Scan更喜欢Bitmap Index + Heap Scan？
• 怎样才能使这个查询对于这个user + type和其他查询表现良好（在 1 秒内）？

我查询 YouTube Data Api 以获得频道上最受欢迎的视频列表，然后获取它们的统计数据，每小时 4 次（每 15 分钟一次，由 cron）。数据存储在 Postgres 中，但将其转储并加载到另一个 SQL 数据库中不会有问题。现在我有以下数据表：

 video_id| views_count | likes_count | timestamp 
---------+-------------+-------------+---------------------
     foo | 100         | 1           | 2018-12-01 12:01:03
     foo | 101         | 1           | 2018-12-01 12:16:06
     foo | 105         | 1           | 2018-12-01 12:31:01
     bar | 199         | 0           | 2018-12-01 12:01:02
     bar | 200         | 0           | 2018-12-01 12:16:08
     bar | 301         | 5           | 2018-12-01 12:31:02
     ... | ...

UPD：这是架构（粘贴到sqlfiddle）：

CREATE TABLE video_statistics
(
  video_id TEXT not null,
  views_count INTEGER not null,
  likes_count INTEGER not null,
  timestamp TIMESTAMPTZ not null
);

我应该如何查询该数据以便按小时view_counts和likes_count按视频分组的列获得增量？澄清我想要得到的东西：

hour_of_day|video_id|views_increment|likes_increment
-----------+--------+---------------+---------------
     ...   | ...
     11    | foo    | 4             | 0
     12    | foo    | 5             | 1
     ...   | ...
     11    | bar    | 73            | 0
     12    | bar    | 102           | 5
     ...   | ...

换句话说，这是一个基于历史数据的“发布视频的最佳时间”，并考虑了数周和数月的数据。我应该将数据转储到一些时间序列数据库或其他更适合这种情况的数据库中，然后在那里查询吗？还是我应该求助于用代码计算这个？

我有一个遗留应用程序。我想使用Percona 博客中的脚本找出哪些表最大。事实证明，我的一些表的索引比实际数据大 3 倍甚至更多。我已经从 MySql 服务器中转储了模式，发现有些表有很多看起来是冗余的索引。

以下是最糟糕的表格之一的创建方式：

CREATE TABLE `pictures_relations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pic_id` int(10) DEFAULT NULL,
  `url_id` int(10) DEFAULT NULL COMMENT 'id урла, с которого файл был/будет скачан',
  `is_stub` tinyint(1) NOT NULL DEFAULT '0',
  `item_id` int(11) NOT NULL,
  `module_id` int(11) NOT NULL,
  `item_type` varchar(8) NOT NULL,
  `is_file` tinyint(1) NOT NULL,
  `is_hide` tinyint(1) NOT NULL,
  `is_cover` tinyint(1) NOT NULL,
  `image_only` tinyint(1) DEFAULT NULL,
  `is_custom` tinyint(1) NOT NULL DEFAULT '0',
  `skip` tinyint(1) NOT NULL DEFAULT '0',
  `order` int(11) NOT NULL,
  `author` int(11) NOT NULL,
  `add_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `is_hide` (`is_hide`),
  KEY `is_cover` (`is_cover`),
  KEY `add_date` (`add_date`),
  KEY `item_id_2` (`item_id`,`module_id`,`item_type`,`is_file`,`is_cover`),
  KEY `order` (`item_id`,`module_id`,`item_type`,`is_file`,`order`),
  KEY `item_id` (`item_id`,`module_id`,`item_type`,`is_file`,`is_custom`),
  KEY `pic_id` (`pic_id`),
  KEY `item_id_3` (`item_id`,`module_id`,`item_type`,`is_file`,`skip`),
  KEY `item_id_4` (`item_id`,`module_id`,`item_type`,`is_file`),
  KEY `pic_id_2` (`pic_id`,`skip`),
  KEY `skip` (`skip`,`pic_id`),
  KEY `stub_url` (`is_stub`,`url_id`),
  KEY `stub_skip_url` (`is_stub`,`skip`,`url_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6794944 DEFAULT CHARSET=utf8;

我可以清楚地看到 indices order、item_id、item_id_2和大部分相似item_id_3，item_id_4但它们略有不同。使用方便的视图从这个 DBA.StackExchange 答案中轻松检测冗余索引有帮助，但作用不大——它确实表明它对item_id_4所有其他索引都是冗余的，但无助于将它们拆分/合并到更合理的索引中。

问题是： 是否有经验法则可以将这些索引重构为理智的东西，减少重复？

就像，如果我有idx1('a', 'b', 'c'), idx2('a', 'b', 'd'), ，idx3('a', 'b', 'e')那么我必须把它变成idx1('a', 'b'),idx2('c')和？idx3('d')idx4('e')