p.matsinopoulos's questions

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?

Eu tenho uma consulta que filtra em uma booleancoluna que possui index. Mas, a consulta leva séculos para terminar. Quando não uso esse filtro, a consulta retorna muito rapidamente.

Aqui estão os planos de explicação. O primeiro tem o processed is truee demora séculos para terminar. O segundo não tem e retorna imediatamente.

explain select count(*) from listen_events where (started_at >='2021-12-26' and started_at <'2021-12-27') and processed is true;
                                                                                 QUERY PLAN                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=212405.62..212405.63 rows=1 width=8)
   ->  Bitmap Heap Scan on listen_events  (cost=187657.78..212390.09 rows=6213 width=0)
         Recheck Cond: ((started_at >= '2021-12-26 00:00:00'::timestamp without time zone) AND (started_at < '2021-12-27 00:00:00'::timestamp without time zone))
         Filter: (processed IS TRUE)
         ->  BitmapAnd  (cost=187657.78..187657.78 rows=6213 width=0)
               ->  Bitmap Index Scan on index_listen_events_on_started_at  (cost=0.00..17323.56 rows=813898 width=0)
                     Index Cond: ((started_at >= '2021-12-26 00:00:00'::timestamp without time zone) AND (started_at < '2021-12-27 00:00:00'::timestamp without time zone))
               ->  Bitmap Index Scan on listen_events_processed_idx  (cost=0.00..170330.87 rows=9125639 width=0)
                     Index Cond: (processed = true)
(9 rows)

=> explain select count(*) from listen_events where (started_at >='2021-12-26' and started_at <'2021-12-27');
                                                                                 QUERY PLAN                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=24549.13..24549.14 rows=1 width=8)
   ->  Gather  (cost=24548.92..24549.13 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=23548.92..23548.93 rows=1 width=8)
               ->  Parallel Index Only Scan using index_listen_events_on_started_at on listen_events  (cost=0.58..22701.11 rows=339124 width=0)
                     Index Cond: ((started_at >= '2021-12-26 00:00:00'::timestamp without time zone) AND (started_at < '2021-12-27 00:00:00'::timestamp without time zone))
(6 rows)

Segue a configuração da tabela:

Table "public.listen_events"
     Column     |            Type             | Collation | Nullable |                  Default                  | Storage  | Stats target | Description 
----------------+-----------------------------+-----------+----------+-------------------------------------------+----------+--------------+-------------
 id             | integer                     |           | not null | nextval('listen_events_id_seq'::regclass) | plain    |              | 
 event_type     | text                        |           |          |                                           | extended |              | 
 stream_type    | text                        |           |          |                                           | extended |              | 
 event_id       | text                        |           |          |                                           | extended |              | 
 broadcast_uid  | text                        |           |          |                                           | extended |              | 
 user_agent     | text                        |           |          |                                           | extended |              | 
 city           | text                        |           |          |                                           | extended |              | 
 country        | text                        |           |          |                                           | extended |              | 
 referrer       | text                        |           |          |                                           | extended |              | 
 country_code   | character varying(2)        |           |          |                                           | extended |              | 
 continent_code | character varying(2)        |           |          |                                           | extended |              | 
 user_id        | integer                     |           |          |                                           | plain    |              | 
 started_at     | timestamp without time zone |           |          |                                           | plain    |              | 
 created_at     | timestamp without time zone |           |          |                                           | plain    |              | 
 updated_at     | timestamp without time zone |           |          |                                           | plain    |              | 
 ip_address     | cidr                        |           |          |                                           | main     |              | 
 location       | point                       |           |          |                                           | plain    |              | 
 ended_at       | timestamp without time zone |           |          |                                           | plain    |              | 
 server_id      | text                        |           |          |                                           | extended |              | 
 channel_id     | integer                     |           |          |                                           | plain    |              | 
 id_bigint      | bigint                      |           |          |                                           | plain    |              | 
 processed      | boolean                     |           | not null | false                                     | plain    |              | 
Indexes:
    "listen_events_pkey" PRIMARY KEY, btree (id)
    "index_listen_events_event_id" btree (event_id)
    "index_listen_events_on_broadcast_uid" btree (broadcast_uid)
    "index_listen_events_on_started_at" btree (started_at)
    "index_listen_events_on_user_id" btree (user_id)
    "listen_events_processed_idx" btree (processed)
Options: autovacuum_enabled=true, autovacuum_vacuum_scale_factor=0, autovacuum_vacuum_threshold=30000, autovacuum_vacuum_cost_delay=0, autovacuum_analyze_scale_factor=0, autovacuum_analyze_threshold=30000, toast.autovacuum_enabled=true

Atualmente, a tabela possui 1,9 bilhão de linhas e a maioria possui processed = false.

Alguma pista de por que isso está acontecendo?

PostgreSQL 14 aqui.

No momento, estou executando um comando ALTER em uma mesa enorme. Já está funcionando há 13 horas e continua funcionando. Está ativo. Existe uma maneira de obter uma indicação de quando isso deve terminar?

Eu executei o seguinte comando e demorou cerca de 10 minutos para terminar:

ALTER TABLE "bookings" ALTER COLUMN "local_direct_booking" SET DEFAULT FALSE

para uma tabela bookingsque tinha 111.834 linhas (cerca de 111K linhas).

Não acredito que seja uma mesa grande.

Este é o tamanho da mesa:

lvd-am::DATABASE=> SELECT pg_size_pretty( pg_total_relation_size('bookings') );
 pg_size_pretty 
----------------
 213 MB
(1 row)

Você tem alguma dica de por que isso pode ter demorado tanto?