All perguntas(dba)

Tenho uma tabela com representação hierárquica e para modelar a estrutura em árvore é utilizada a enumeração de caminhos. Apesar de o plano de consulta ser otimizado e a consulta em si ser direta, a execução da consulta leva 4 segundos. Porém, quando removo a classificação, a consulta é rápida, mas preciso dessa classificação.

Basicamente, a consulta deve retornar os principais pais relevantes cujos filhos satisfaçam determinados critérios.

Aqui está o link para a consulta e plano de consulta: https://www.brentozar.com/pastetheplan/?id=S1njIuPRT

SELECT TOP 50 Id
FROM   Site root
WHERE  Archive = 288
       AND TypeId IN( 1 )
       AND EXISTS (SELECT 1
                   FROM   Site ch
                   WHERE  ch.[path] LIKE concat(root.Path, '%')
                          AND ch.Commercial = 0
                          AND ch.Archive = 288)
ORDER  BY root.Score DESC 

Eu tenho uma tabela com userse coluna username. Estou tentando construir um índice na users#usernamecoluna, mas está falhando com um erro de valor duplicado. O valor detectado é um valor de conjunto de caracteres árabes. Observe que quando eu faço

select count(*), username from users group by username having count(*)>=2;

ele retorna 0.

O agrupamento do banco de dados é en_us.UTF8.

Por que group bynão consegue detectar as duplicatas enquanto o índice exclusivo criado as encontra e falha?

Então este me deixou perplexo. Estamos fazendo uma limpeza de permissões e temos alguns grupos do AD que gostaríamos de remover de uma função de banco de dados.

Corremos ALTER ROLE MyRole DROP MEMBER [domain\group]e pegamos Commands completed successfully..

MAS

o grupo ainda é um membro da função.

Alguma ideia? O login que executa os comandos é um sysadmin na instância. Também tentamos o antigo exec sp_droprolemembercom o mesmo resultado.

Gostaria de saber se durante uma mysqldumpexecução da seguinte maneira

mysqldump --all-databases --master-data > dump.sql

todas as tabelas ficam bloqueadas durante todo o processo, ou apenas tabela por tabela? Em outras palavras, interrompe mysqldump(suspende) todas as consultas de escrita durante o processo, ou apenas aquelas que estão tentando modificar a tabela que está sendo exportada atualmente? Essas consultas são interrompidas ou suspensas?

Tenho um grande banco de dados que atende continuamente um aplicativo usado pelos clientes. Tem cerca de 50 GB e levará horas até que o despejo seja concluído. Não consigo parar o banco de dados e preciso despejá-lo sem afetar muito sua capacidade de funcionamento. Pretendo usar esse dump para replicar o banco de dados em outro servidor sob estratégia mestre-escravo. Para sua informação, o log binário está ativado e server_iddefinido.

+-------------------------------+
| version()                     |
+-------------------------------+
| 5.6.14-1+debphp.org~precise+1 |
+-------------------------------+

Como criar um id único no Flutter para adicionar ao banco de dados no firebase?

Eu tenho 4 tabelas e leva muito tempo para consultar com a cláusula OR (a cláusula AND funciona bem).

1. Novos artigos

Linhas: ~7000

Esquema:

id               | integer
publication_date | timestamp with time zone
metadata         | jsonb
text             | text
title            | character varying(2048)
is_spam          | boolean

Índices:

    "news_pkey" PRIMARY KEY, btree (id)
    "news_text_gin" gin (text gin_trgm_ops) WITH (fastupdate=off)
    "news_publication_date_6dfb01cd" btree (publication_date)
    "news_title_aa02bdd6" btree (title)
    "news_title_aa02bdd6_like" btree (title varchar_pattern_ops)

2. Usuário M2M para notícias

Linhas: ~200

Esquema:

id        | integer
hidden    | boolean
seen      | boolean
news_id   | integer
user_id   | integer

Índices:

    "usernews_pkey" PRIMARY KEY, btree (id)
    "usernews_news_id_8451f0f6" btree (news_id)
    "usernews_user_id_cf9591f3" btree (user_id)
    "unique_user_news_constraint" UNIQUE CONSTRAINT, btree (user_id, news_id)

3. Notícias M2M para tags

Linhas: ~15.000

Esquema:

id        | integer
news_id   | integer
tag_id    | integer

Índices:

    "newstag_pkey" PRIMARY KEY, btree (id)
    "newstag_news_id_331e55c0" btree (news_id)
    "newstag_tag_id_88f2fc8b" btree (tag_id)

4. Tag

Linhas: ~800

Esquema:

id           | integer
name         | text

Índices:

    "system_tag_pkey" PRIMARY KEY, btree (id)
    "system_tag_name_0ef0fc9a_like" btree (name text_pattern_ops)
    "system_tag_name_0ef0fc9a_uniq" UNIQUE CONSTRAINT, btree (name)

Minha consulta é:

SELECT 
    "news"."id",
    "news"."metadata", 
    "news"."title",
    "news"."text",
    "news"."publication_date", 
    COALESCE("usernews"."seen", false) AS "seen" 
FROM "news" 
    LEFT OUTER JOIN "newstag" ON ("news"."id" = "newstag"."news_id") 
    LEFT OUTER JOIN "system_tag" ON ("newstag"."tag_id" = "system_tag"."id")
    LEFT OUTER JOIN "usernews" ON ("news"."id" = "usernews"."news_id") 
WHERE 
    (
    NOT "news"."is_spam" AND ("news"."metadata" -> 'appeals_journal') = 'true' 
    AND (UPPER("news"."text"::text) LIKE UPPER('%something%') OR UPPER("system_tag"."name"::text) LIKE UPPER('%something%'))
    AND ("news"."publication_date" AT TIME ZONE 'Europe/London')::date BETWEEN '2022-12-02'::date AND '2025-01-10'::date
    ) 
ORDER BY "news"."publication_date" DESC

O que cria esta análise explicativa:

 Sort  (cost=1389.70..1389.71 rows=1 width=1031) (actual time=2478.054..2478.281 rows=1597 loops=1)
   Sort Key: news.publication_date DESC
   Sort Method: quicksort  Memory: 2351kB
   ->  Nested Loop Left Join  (cost=0.56..1389.69 rows=1 width=1031) (actual time=21.685..2471.670 rows=1597 loops=1)
         Join Filter: (news.id = usernews.news_id)
         Rows Removed by Join Filter: 343317
         ->  Nested Loop Left Join  (cost=0.56..1382.48 rows=1 width=1030) (actual time=21.573..2390.879 rows=1597 loops=1)
               Filter: ((upper(news.text) ~~ '%SOMETHING%'::text) OR (upper(system_tag.name) ~~ '%SOMETHING%'::text))
               Rows Removed by Filter: 13770
               ->  Seq Scan on news  (cost=0.00..1373.53 rows=1 width=1030) (actual time=0.032..40.981 rows=7053 loops=1)
                     Filter: ((NOT is_spam) AND ((metadata -> 'appeals_journal'::text) = 'true'::jsonb) AND ((timezone('Europe/London'::text, publication_date))::date >= '2022-12-02'::date) AND ((timezone('Europe/London'::text, publication_date))::date <= '2025-01-10'::date))
                     Rows Removed by Filter: 14
               ->  Nested Loop Left Join  (cost=0.56..8.91 rows=2 width=26) (actual time=0.008..0.017 rows=2 loops=7053)
                     ->  Index Scan using newstag_news_id_331e55c0 on newstag  (cost=0.29..8.32 rows=2 width=8) (actual time=0.005..0.007 rows=2 loops=7053)
                           Index Cond: (news_id = news.id)
                     ->  Index Scan using system_tag_pkey on system_tag  (cost=0.28..0.30 rows=1 width=26) (actual time=0.003..0.003 rows=1 loops=15329)
                           Index Cond: (id = newstag.tag_id)
         ->  Seq Scan on usernews  (cost=0.00..4.87 rows=187 width=5) (actual time=0.006..0.026 rows=215 loops=1597)
 Planning Time: 0.830 ms
 Execution Time: 2478.637 ms

Eu tentei com AND clase e obtive:

 Sort  (cost=159.27..159.27 rows=1 width=1031) (actual time=127.686..127.725 rows=421 loops=1)
   Sort Key: news.publication_date DESC
   Sort Method: quicksort  Memory: 697kB
   ->  Hash Right Join  (cost=153.68..159.26 rows=1 width=1031) (actual time=126.488..126.824 rows=421 loops=1)
         Hash Cond: (news.news_id = news.id)
         ->  Seq Scan on usernews  (cost=0.00..4.87 rows=187 width=5) (actual time=0.078..0.113 rows=215 loops=1)
         ->  Hash  (cost=153.67..153.67 rows=1 width=1030) (actual time=126.393..126.395 rows=421 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 504kB
               ->  Nested Loop  (cost=20.99..153.67 rows=1 width=1030) (actual time=2.431..125.366 rows=421 loops=1)
                     ->  Nested Loop  (cost=20.70..121.97 rows=19 width=4) (actual time=1.803..3.367 rows=727 loops=1)
                           ->  Seq Scan on system_tag  (cost=0.00..21.98 rows=1 width=4) (actual time=0.098..1.559 rows=2 loops=1)
                                 Filter: (upper(name) ~~ '%SOMETHING%'::text)
                                 Rows Removed by Filter: 817
                           ->  Bitmap Heap Scan on newstag  (cost=20.70..99.45 rows=54 width=8) (actual time=0.131..0.733 rows=364 loops=2)
                                 Recheck Cond: (tag_id = system_tag.id)
                                 Heap Blocks: exact=84
                                 ->  Bitmap Index Scan on newstag_tag_id_88f2fc8b  (cost=0.00..20.69 rows=54 width=0) (actual time=0.119..0.119 rows=364 loops=2)
                                       Index Cond: (tag_id = system_tag.id)
                     ->  Index Scan using news_pkey on news  (cost=0.28..1.60 rows=1 width=1030) (actual time=0.165..0.165 rows=1 loops=727)
                           Index Cond: (id = newstag.news_id)
                           Filter: ((NOT is_spam) AND (upper(text) ~~ '%SOMETHING%'::text) AND ((metadata -> 'appeals_journal'::text) = 'true'::jsonb) AND ((timezone('Europe/London'::text, publication_date))::date >= '2022-12-02'::date) AND ((timezone('Europe/London'::text, publication_date))::date <= '2025-01-10'::date))
                           Rows Removed by Filter: 0
 Planning Time: 1.463 ms
 Execution Time: 127.852 ms

Como posso acelerar minha consulta com a instrução OR? As únicas coisas que consigo pensar são em usar UNION (que é bastante rápido) ou visualizações materializadas com índices (o que não acho uma boa ideia, porque minhas tabelas obtêm dados constantemente do analisador).

PS: Eu uso PostgreSQL 12.6.

Eu tenho uma dessas situações:

CREATE TABLE table (
    `col1` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `col2` VARCHAR(512) NOT NULL COLLATE 'utf8mb4_general_ci',
    PRIMARY KEY (`col1`) USING BTREE,
    UNIQUE INDEX `col2` (`col2`) USING BTREE,
[...]

e então eu tenho essa declaração:

INSERT INTO table (col2, col3) VALUES ( val2, val3) ON DUPLICATE KEY UPDATE (col2=val2, col3=val3)

se houver uma chave duplicada em col2 UPDATE irá incrementar automaticamentecol1

Pergunta existe uma maneira de fazer um incremento automático de tabela apenas em INSERT ?

Existem diversas funções projetadas especificamente para atingir índices de texto completo. Acho que o seguinte é uma lista completa:

• CONTAINS
• CONTAINSTABLE
• FREETEXT
• FREETEXTTABLE

É possível atingir um índice de texto completo sem usar uma dessas funções? Se você atingiu ou não o índice, pode ser comprovado colando seu plano de execução.

Sou bastante novato quando se trata de armazenamento de dados e quero aprender alguns truques.

Quero saber como decidir entre RDBMS e uma loja NoSQL.

Vejamos o exemplo de um sistema onde o tamanho dos dados é muito grande, digamos TBs. E também precisamos de uma consistência forte, mas também de baixa latência.

Para questões de tamanho de dados, o NoSQL pode ser uma boa opção, mas por motivos de consistência, o tipo RDBMS pode parecer bom. Mas alta consistência significa baixa latência.

Embora alguns RDBMS também ofereçam fragmentação para resolver tamanhos de big data, a latência ainda é um problema.

Como examinamos todos esses prós e contras para chegar a uma decisão?

Além disso, quaisquer recursos sobre este tópico do ponto de vista do iniciante podem ser de grande ajuda.

Eu tenho uma tabela assim:

id  user             userid  user_id  ipaddr
1   'Client'         10      0        1.1.1.1
2   'admin1'         1       0        2.2.2.2
3   'Client'         3       0        1.1.1.1
4   'admin2'         5       0        3.3.3.3
5   'Client'         12      0        4.4.4.4
6   'admin1'         1       0        2.2.2.2
7   'Client'         21      0        4.4.4.4
8   'Client'         25      0        6.6.6.6
9   'Sub-Client 20'  35      0        4.4.4.4

Vou executar uma consulta que descobre qual:

find in `ipaddr` column and find any ip-addresses are the same for other `user`s which names are `Client` or `Sub-Client`.

Espero ter conseguido explicar claramente.

Suponha isto:

ip-address 1.1.1.1 ==> Clients with userid 10, 3.
ip-address 4.4.4.4 ==> Clients with userid 12, 21, 35.

Isso mostra que esses clientes fizeram login exatamente com o mesmo IP.

Não sei que consulta executar e nem sei como se chama isso, para poder pesquisar no Google.