Eduard Sukharev's questions

Temos tabela para usuários:

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

Tivemos manutenção em nosso servidor, durante a qual foi feito backup e replicado para outro servidor usando pg_basebackup(concluído por nossos administradores de sistema, supostamente sem erros e problemas).

Desde então, temos erros em nossos logs como os seguintes:

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;)

Enquanto isso, a consulta a seguir não retorna resultados:

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

enquanto é 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

Mas esta consulta:

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

mostra uma lista de linhas duplicadas com iguais logine socnet, o que para mim não faz sentido. Aqui está o respectivo 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

A versão do servidor PostgreSQL é 9.6

Minhas perguntas são:

• O que está acontecendo? Por que tenho dados duplicados em UNIQUE CONSTRAINT?
• Por que tenho resultados diferentes para INNER JOINe LEFT JOIN?
• O que pode ter acontecido com a estrutura do banco de dados e como depurá-la? Onde procurar dicas sobre o que está quebrado?
• Como corrigir isso para um estado consistente?

Dada a seguinte tabela:

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");

onde type é 1ou NULL, então a consulta:

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;

fornece a seguinte saída:

                                                                       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

Mas uma vez que o valor LIMIT é reduzido abaixo de algum valor (para 9 na minha máquina local), o plano de consulta muda para isto:

                                                                        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

o que é muito lento.

Há uma enorme distorção de dados para esse usuário exato: são 50.000 rows WHERE type is NULLe apenas 6 WHERE type = 1. Além disso, solicitando o mesmo LIMIT 9, mas WHERE type is NULLtem exatamente o mesmo plano de consulta, mas funciona rápido:

                                                                         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

No servidor de produção, exatamente os mesmos dados carregados em um servidor com especificações diferentes do meu laptop (mais memória RAM, enorme shared_buffers, max_memcarga de trabalho constante de outras tabelas diferentes) se comportam de maneira semelhante, apenas o valor do limite é diferente (é lento Index Scanaté 75, e depois rápido Bitmap Heap Scan+ Bitmap Index Scande 76 e mais).

Algumas informações adicionais:

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}

Minhas perguntas são:

• Por que o mesmo Index Scanfica muito lento quando se trata dessas poucas linhas?
• Por que Index Scansempre usa pk_chat_messageíndice, mesmo que haja mais adequado idx_chat_message_user_type, mesmo que ORDER BYa cláusula tenha todos os campos da WHEREcláusula ( ordem por influencia o uso do índice )?
• Por que LIMIT Nafeta o plano de consulta porque ele Index Scanprefere Bitmap Index + Heap Scan?
• O que pode ser feito para que esta consulta tenha um desempenho decente (menos de 1s) para esta user + typee outras?

Eu consulto a API de dados do YouTube para obter uma lista dos vídeos mais populares em um canal e, em seguida, obtenho suas estatísticas, 4 vezes por hora (a cada 15 minutos, por cron). Os dados são armazenados no Postgres, mas despejá-los e carregá-los em outro banco de dados SQL não seria um problema. Agora tenho a seguinte tabela de dados:

 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: Aqui está o esquema (colado em sqlfiddle ):

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

Como devo consultar esses dados para obter incrementos por hora view_countse likes_countcolunas, agrupados por vídeo? Para esclarecer o que quero obter:

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
     ...   | ...

Em outras palavras, é um "melhor momento para postar vídeo" com base em dados históricos, levando em consideração dados durante muitas semanas e meses. Devo despejar os dados em algum banco de dados de série temporal ou outro, mais apropriado para esses casos, e consultá-lo lá? Ou devo apenas recorrer a calcular isso no código?

Eu tenho um aplicativo legado. Eu queria saber quais tabelas são as maiores usando script do blog Percona . Acontece que algumas das minhas tabelas têm índices maiores que os dados reais por fator de 3 ou mais. Eu despejei o esquema do servidor MySql e descobri que algumas tabelas têm muitos índices que parecem ser redundantes.

Veja como uma das piores tabelas é criada:

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;

Posso ver claramente que os índices order, item_id, item_id_2e são item_id_3em item_id_4sua maioria semelhantes, mas diferem um pouco. O uso de visualizações de conveniência para detectar facilmente índices redundantes desta resposta do DBA.StackExchange ajuda, mas não muito - mostra que item_id_4é redundante para todos os outros, mas não ajuda a dividi-los/mesclá-los em índices mais sensatos.

A questão é: existe uma regra prática para refatorar esses índices em algo sensato, com menos duplicação?

Tipo, se eu tiver idx1('a', 'b', 'c'), idx2('a', 'b', 'd'), idx3('a', 'b', 'e'), então eu tenho que transformá-lo em idx1('a', 'b'), idx2('c'), idx3('d')e idx4('e')?