laurent's questions

Eu tenho a consulta SQL abaixo que é executada de forma extremamente lenta. Quanto a esta consulta , isso se deve à instrução "ORDER BY", pois o Postgres está varrendo a changestabela por "contador" que pode ter milhões de valores. A remoção da instrução "ORDER BY" torna a consulta mais rápida.

Para a outra consulta mencionada acima, otimizei-a criando um índice em dois campos. Para esta consulta, entretanto, não tenho certeza de qual índice seria o correto. Tentei com um índice ativado, (item_id, counter)mas não ajudou em nada e não sei o que mais poderia tentar. Alguma sugestão?

Consulta SQL lenta:

SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
FROM "changes"
WHERE counter > -1
AND type = 2
AND item_id IN (SELECT item_id FROM user_items WHERE user_id = 'xxxx')
ORDER BY "counter" ASC
LIMIT 200;

EXPLICAR (ANALISAR, BUFFERS, CONFIGURAÇÕES) resultado:

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1001.15..27628.99 rows=200 width=99) (actual time=98730.912..116273.818 rows=200 loops=1)
   Buffers: shared hit=78113369 read=3224064 dirtied=3
   I/O Timings: read=137436.119
   ->  Gather Merge  (cost=1001.15..10431526.45 rows=78343 width=99) (actual time=98730.911..116273.783 rows=200 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=78113369 read=3224064 dirtied=3
         I/O Timings: read=137436.119
         ->  Nested Loop  (cost=1.13..10421483.70 rows=32643 width=99) (actual time=98493.185..112919.559 rows=75 loops=3)
               Buffers: shared hit=78113369 read=3224064 dirtied=3
               I/O Timings: read=137436.119
               ->  Parallel Index Scan using changes_pkey on changes  (cost=0.56..5949383.56 rows=6197986 width=99) (actual time=1.076..42523.117 rows=4075591 loops=3)
                     Index Cond: (counter > '-1'::integer)
                     Filter: (type = 2)
                     Rows Removed by Filter: 10370914
                     Buffers: shared hit=18993521 read=2672415
                     I/O Timings: read=85551.814
               ->  Index Scan using user_items_item_id_index on user_items  (cost=0.56..0.72 rows=1 width=23) (actual time=0.017..0.017 rows=0 loops=12226772)
                     Index Cond: ((item_id)::text = (changes.item_id)::text)
                     Filter: ((user_id)::text = 'xxxx'::text)
                     Rows Removed by Filter: 1
                     Buffers: shared hit=59119848 read=551649 dirtied=3
                     I/O Timings: read=51884.305
 Settings: effective_cache_size = '16179496kB', jit = 'off', work_mem = '100000kB'
 Planning Time: 1.465 ms
 Execution Time: 116273.929 ms
(26 rows)

Índices:

"changes_pkey" PRIMARY KEY, btree (counter)
"changes_id_index" btree (id)
"changes_id_unique" UNIQUE CONSTRAINT, btree (id)
"changes_item_id_index" btree (item_id)
"changes_user_id_counter_index" btree (user_id, counter)
"changes_user_id_index" btree (user_id)

Tenho tentado depurar uma consulta particularmente lenta que nunca é concluída (leva uma eternidade e eventualmente atinge o tempo limite) e descobri que tudo se resume à ORDER BYafirmação: se estiver lá, nunca será concluída; se eu removê-la, ela retornará instantaneamente.

Minha suposição era que não havia índice nesse campo, mas descobri que existe um:

CREATE UNIQUE INDEX changes_pkey ON public.changes USING btree (counter)

No entanto, isso não parece fazer nenhuma diferença, então estou me perguntando qual poderia ser o motivo? Talvez seja porque é um "ÍNDICE ÚNICO" diferente dos outros índices desta tabela?

Confira abaixo as dúvidas:

Nunca complete:

SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
FROM "changes"
WHERE counter > -1
AND (type = 1 OR type = 3)
AND user_id = 'xxxxxxx'
ORDER BY "counter" ASC
LIMIT 200

Conclui instantaneamente:

SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
FROM "changes"
WHERE counter > -1
AND (type = 1 OR type = 3)
AND user_id = 'xxxxxxx'
LIMIT 200

Índices nessa tabela:

changes              | changes_id_index                          | CREATE INDEX changes_id_index ON public.changes USING btree (id)
changes              | changes_id_unique                         | CREATE UNIQUE INDEX changes_id_unique ON public.changes USING btree (id)
changes              | changes_item_id_index                     | CREATE INDEX changes_item_id_index ON public.changes USING btree (item_id)
changes              | changes_pkey                              | CREATE UNIQUE INDEX changes_pkey ON public.changes USING btree (counter)
changes              | changes_user_id_index                     | CREATE INDEX changes_user_id_index ON public.changes USING btree (user_id)

postgres=> EXPLAIN SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
postgres-> FROM "changes"
postgres-> WHERE counter > -1
postgres-> AND (type = 1 OR type = 3)
postgres-> AND user_id = 'xxxxxxxx'
postgres-> ORDER BY "counter" ASC
postgres-> LIMIT 200;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..9206.44 rows=200 width=99)
   ->  Index Scan using changes_pkey on changes  (cost=0.56..5746031.01 rows=124834 width=99)
         Index Cond: (counter > '-1'::integer)
         Filter: (((user_id)::text = 'xxxxxxxx'::text) AND ((type = 1) OR (type = 3)))
(4 rows)

* EXPLAIN para a consulta rápida:

postgres=> EXPLAIN SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
postgres-> FROM "changes"
postgres-> WHERE counter > -1
postgres-> AND (type = 1 OR type = 3)
postgres-> AND user_id = 'xxxxxxxx'
postgres-> LIMIT 200;
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..1190.09 rows=200 width=99)
   ->  Index Scan using changes_user_id_index on changes  (cost=0.56..742468.10 rows=124834 width=99)
         Index Cond: ((user_id)::text = 'xxxxxxxx'::text)
         Filter: ((counter > '-1'::integer) AND ((type = 1) OR (type = 3)))
(4 rows)

Alguma ideia de qual poderia ser o motivo dessa consulta lenta?

Tenho duas consultas SQL relativamente complexas às quais associo usando um arquivo UNION ALL. Cada consulta individual é rápida e retorna instantaneamente. O problema é que, uma vez unidos, eles têm um desempenho terrivelmente ruim e muitas vezes expiram.

Esta é a consulta completa:

SELECT "id", "item_id", "item_name", "type", "updated_time", "counter" FROM (
    SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
    FROM "changes"
    WHERE counter > -1
    AND (type = 1 OR type = 3)
    AND user_id = 'USER_ID'
    ORDER BY "counter" ASC
    LIMIT 100
) as sub1
UNION ALL
SELECT "id", "item_id", "item_name", "type", "updated_time", "counter" FROM (
    SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
    FROM "changes"
    WHERE counter > -1
    AND type = 2
    AND item_id IN (SELECT item_id FROM user_items WHERE user_id = 'USER_ID')
    ORDER BY "counter" ASC
    LIMIT 100
) as sub2
ORDER BY counter ASC -- SLOW!!
LIMIT 100;

Depois de algumas pesquisas, descobri que o motivo da lentidão está no ORDER BY counter ASCfinal da declaração (não nas declarações internas - tudo bem). Se estiver lá, leva mais de um minuto e expira. Sem ele, ele retorna em 5ms.

Isso não faz sentido para mim, já que a essa altura temos um total de 200 linhas, então a ordenação deve ser muito rápida (especialmente porque as linhas já estão ordenadas!).

Tentei analisar as consultas, mas nada se destaca para mim:

• Consulta completa (leva mais de 60 segundos)

 Limit  (cost=1.70..325537.14 rows=100 width=101)
   ->  Merge Append  (cost=1.70..651072.57 rows=200 width=101)
         Sort Key: changes.counter
         ->  Limit  (cost=0.56..106310.10 rows=100 width=101)
               ->  Index Scan using changes_pkey on changes  (cost=0.56..4162018.71 rows=3915 width=101)
                     Index Cond: (counter > '-1'::integer)
                     Filter: (((user_id)::text = 'USER_ID'::text) AND ((type = 1) OR (type = 3)))
         ->  Limit  (cost=1.12..544758.47 rows=100 width=101)
               ->  Nested Loop  (cost=1.12..11516171.34 rows=2114 width=101)
                     ->  Index Scan using changes_pkey on changes changes_1  (cost=0.56..3986383.73 rows=10843703 width=101)
                           Index Cond: (counter > '-1'::integer)
                           Filter: (type = 2)
                     ->  Index Only Scan using user_items_user_id_item_id_unique on user_items  (cost=0.56..0.69 rows=1 width=24)
                           Index Cond: ((user_id = 'USER_ID'::text) AND (item_id = (changes_1.item_id)::text))

• Consulta completa sem último ORDER BY(leva 5 ms) :

 Limit  (cost=23934.97..180049.76 rows=100 width=101)
   ->  Append  (cost=23934.97..336164.56 rows=200 width=101)
         ->  Limit  (cost=23934.97..23935.22 rows=100 width=101)
               ->  Sort  (cost=23934.97..23944.76 rows=3915 width=101)
                     Sort Key: changes.counter
                     ->  Bitmap Heap Scan on changes  (cost=284.11..23785.34 rows=3915 width=101)
                           Recheck Cond: ((user_id)::text = 'USER_ID'::text)
                           Filter: ((counter > '-1'::integer) AND ((type = 1) OR (type = 3)))
                           ->  Bitmap Index Scan on changes_user_id_index  (cost=0.00..283.13 rows=6209 width=0)
                                 Index Cond: ((user_id)::text = 'USER_ID'::text)
         ->  Limit  (cost=312214.83..312226.33 rows=100 width=101)
               ->  Gather Merge  (cost=312214.83..312357.89 rows=1244 width=101)
                     Workers Planned: 1
                     ->  Sort  (cost=311214.82..311217.93 rows=1244 width=101)
                           Sort Key: changes_1.counter
                           ->  Nested Loop  (cost=148.64..311167.28 rows=1244 width=101)
                                 ->  Parallel Bitmap Heap Scan on user_items  (cost=148.07..11209.94 rows=1785 width=24)
                                       Recheck Cond: ((user_id)::text = 'USER_ID'::text)
                                       ->  Bitmap Index Scan on user_items_user_id_index  (cost=0.00..147.31 rows=3034 width=0)
                                             Index Cond: ((user_id)::text = 'USER_ID'::text)
                                 ->  Index Scan using changes_item_id_index on changes changes_1  (cost=0.56..167.91 rows=13 width=101)
                                       Index Cond: ((item_id)::text = (user_items.item_id)::text)
                                       Filter: ((counter > '-1'::integer) AND (type = 2))

Estou pensando que poderia simplesmente remover a instrução "ordenar por" e ordená-las eu mesmo no código, mas isso não parece muito claro.

Alguma ideia do que pode ser feito para melhorar isso?

Estou tentando depurar a consulta lenta abaixo, mas estou lutando para entender por que ela é lenta. Posso ver que tanto o plano quanto o subplano fazem uma varredura de índice, incluindo uma "varredura somente de índice" para o subplano, portanto ambos devem ser rápidos. No entanto, esta consulta específica leva 7 segundos.

Alguma ideia desta saída EXPLAIN onde pode estar o problema?

select "id", "item_id", "item_name", "type", "updated_time" from "changes"
where (
  ((type = 1 OR type = 3) AND user_id = 'USER_ID')
  or type = 2 AND item_id IN (SELECT item_id FROM user_items WHERE user_id = 'USER_ID')
) and "counter" > '35885954' order by "counter" asc limit 100;

 Limit  (cost=8409.70..8553.44 rows=100 width=101) (actual time=7514.730..7514.731 rows=0 loops=1)
   ->  Index Scan using changes_pkey on changes  (cost=8409.70..2387708.44 rows=1655325 width=101) (actual time=7514.728..7514.729 rows=0 loops=1)
         Index Cond: (counter > 35885954)
         Filter: ((((type = 1) OR (type = 3)) AND ((user_id)::text = 'USER_ID'::text)) OR ((type = 2) AND (hashed SubPlan 1)))
         Rows Removed by Filter: 11378536
         SubPlan 1
           ->  Index Only Scan using user_items_user_id_item_id_unique on user_items  (cost=0.56..8401.57 rows=3030 width=24) (actual time=0.085..3.011 rows=3589 loops=1)
                 Index Cond: (user_id = 'USER_ID'::text)
                 Heap Fetches: 2053
 Planning Time: 0.245 ms
 Execution Time: 7514.781 ms
(11 rows)

Estou tentando otimizar uma consulta, que nunca é concluída no Postgres 12.7. Demora horas, até dias, faz a CPU ir 100% e nunca mais retorna:

SELECT "id", "counter", "item_id", "item_name", "type", "updated_time"
FROM "changes"
WHERE (type = 1 OR type = 3) AND user_id = 'kJ6GYJNPM4wdDY5dUV1b8PqDRJj6RRgW'
OR type = 2 AND item_id IN (SELECT item_id FROM user_items WHERE user_id = 'kJ6GYJNPM4wdDY5dUV1b8PqDRJj6RRgW')
ORDER BY "counter" ASC LIMIT 100;

Tentei reescrevê-lo aleatoriamente usando UNION e acredito que seja equivalente. Basicamente, há duas partes na consulta, uma para tipo = 1 ou 3 e outra para tipo = 2.

(
    SELECT "id", "counter", "item_id", "item_name", "type", "updated_time"
    FROM "changes"
    WHERE (type = 1 OR type = 3) AND user_id = 'kJ6GYJNPM4wdDY5dUV1b8PqDRJj6RRgW'
) UNION (
    SELECT "id", "counter", "item_id", "item_name", "type", "updated_time"
    FROM "changes"
    WHERE type = 2 AND item_id IN (SELECT item_id FROM user_items WHERE user_id = 'kJ6GYJNPM4wdDY5dUV1b8PqDRJj6RRgW')
) ORDER BY "counter" ASC LIMIT 100;

Essa consulta retorna em 10 segundos, em vez de nunca retornar após vários dias para a outra. Alguma ideia do que está causando essa enorme diferença?

Planos de consulta

Para a consulta original:

                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1001.01..1697110.80 rows=100 width=119)
   ->  Gather Merge  (cost=1001.01..8625312957.40 rows=508535 width=119)
         Workers Planned: 2
         ->  Parallel Index Scan using changes_pkey on changes  (cost=0.98..8625253259.82 rows=211890 width=119)
               Filter: ((((type = 1) OR (type = 3)) AND ((user_id)::text = 'kJ6GYJNPM4wdDY5dUV1b8PqDRJj6RRgW'::text)) OR ((type = 2) AND (SubPlan 1)))
               SubPlan 1
                 ->  Materialize  (cost=0.55..18641.22 rows=143863 width=33)
                       ->  Index Only Scan using user_items_user_id_item_id_unique on user_items  (cost=0.55..16797.90 rows=143863 width=33)
                             Index Cond: (user_id = 'kJ6GYJNPM4wdDY5dUV1b8PqDRJj6RRgW'::text)

E para a consulta UNION:

Limit  (cost=272866.63..272866.88 rows=100 width=212) (actual time=10564.742..10566.964 rows=100 loops=1)
   ->  Sort  (cost=272866.63..273371.95 rows=202128 width=212) (actual time=10564.739..10566.950 rows=100 loops=1)
         Sort Key: changes.counter
         Sort Method: top-N heapsort  Memory: 69kB
         ->  Unique  (cost=261604.20..265141.44 rows=202128 width=212) (actual time=9530.376..10493.030 rows=147261 loops=1)
               ->  Sort  (cost=261604.20..262109.52 rows=202128 width=212) (actual time=9530.374..10375.845 rows=147261 loops=1)
                     Sort Key: changes.id, changes.counter, changes.item_id, changes.item_name, changes.type, changes.updated_time
                     Sort Method: external merge  Disk: 19960kB
                     ->  Gather  (cost=1000.00..223064.76 rows=202128 width=212) (actual time=2439.116..7356.233 rows=147261 loops=1)
                           Workers Planned: 2
                           Workers Launched: 2
                           ->  Parallel Append  (cost=0.00..201851.96 rows=202128 width=212) (actual time=2421.400..7815.315 rows=49087 loops=3)
                                 ->  Parallel Hash Join  (cost=12010.60..103627.94 rows=47904 width=119) (actual time=907.286..3118.898 rows=24 loops=3)
                                       Hash Cond: ((changes.item_id)::text = (user_items.item_id)::text)
                                       ->  Parallel Seq Scan on changes  (cost=0.00..90658.65 rows=365215 width=119) (actual time=1.466..2919.855 rows=295810 loops=3)
                                             Filter: (type = 2)
                                             Rows Removed by Filter: 428042
                                       ->  Parallel Hash  (cost=11290.21..11290.21 rows=57631 width=33) (actual time=78.190..78.191 rows=48997 loops=3)
                                             Buckets: 262144  Batches: 1  Memory Usage: 12416kB
                                             ->  Parallel Index Only Scan using user_items_user_id_item_id_unique on user_items  (cost=0.55..11290.21 rows=57631 width=33) (actual time=0.056..107.247 rows=146991 loops=1)
                                                   Index Cond: (user_id = 'kJ6GYJNPM4wdDY5dUV1b8PqDRJj6RRgW'::text)
                                                   Heap Fetches: 11817
                                 ->  Parallel Seq Scan on changes changes_1  (cost=0.00..95192.10 rows=36316 width=119) (actual time=2410.556..7026.664 rows=73595 loops=2)
                                       Filter: (((user_id)::text = 'kJ6GYJNPM4wdDY5dUV1b8PqDRJj6RRgW'::text) AND ((type = 1) OR (type = 3)))
                                       Rows Removed by Filter: 1012184
 Planning Time: 65.846 ms
 Execution Time: 10575.679 ms
(27 rows)

Definições

                                         Table "public.changes"
    Column     |         Type          | Collation | Nullable |                 Default
---------------+-----------------------+-----------+----------+------------------------------------------
 counter       | integer               |           | not null | nextval('changes_counter_seq'::regclass)
 id            | character varying(32) |           | not null |
 item_type     | integer               |           | not null |
 item_id       | character varying(32) |           | not null |
 item_name     | text                  |           | not null | ''::text
 type          | integer               |           | not null |
 updated_time  | bigint                |           | not null |
 created_time  | bigint                |           | not null |
 previous_item | text                  |           | not null | ''::text
 user_id       | character varying(32) |           | not null | ''::character varying
Indexes:
    "changes_pkey" PRIMARY KEY, btree (counter)
    "changes_id_unique" UNIQUE CONSTRAINT, btree (id)
    "changes_id_index" btree (id)
    "changes_item_id_index" btree (item_id)
    "changes_user_id_index" btree (user_id)

                                      Table "public.user_items"
    Column    |         Type          | Collation | Nullable |                Default
--------------+-----------------------+-----------+----------+----------------------------------------
 id           | integer               |           | not null | nextval('user_items_id_seq'::regclass)
 user_id      | character varying(32) |           | not null |
 item_id      | character varying(32) |           | not null |
 updated_time | bigint                |           | not null |
 created_time | bigint                |           | not null |
Indexes:
    "user_items_pkey" PRIMARY KEY, btree (id)
    "user_items_user_id_item_id_unique" UNIQUE CONSTRAINT, btree (user_id, item_id)
    "user_items_item_id_index" btree (item_id)
    "user_items_user_id_index" btree (user_id)

Contagem de tipos

postgres=> select count(*) from changes where type = 1;
  count
---------
 1201839
(1 row)

postgres=> select count(*) from changes where type = 2;
 count
--------
 888269
(1 row)

postgres=> select count(*) from changes where type = 3;
 count
-------
 83849
(1 row)

Quantos item_id por user_id

postgres=> SELECT min(ct), max(ct), avg(ct), sum(ct) FROM (SELECT count(*) AS ct FROM user_items GROUP BY user_id) x;
 min |  max   |          avg          |   sum
-----+--------+-----------------------+---------
   6 | 146991 | 2253.0381526104417671 | 1122013
(1 row)