No postgres, estou fazendo uma consulta em 2 colunas

• string typecom apenas 3 valores possíveis
• carimbo de data/horathe_timestamp

a consulta é

select id from my_table where type = 'a-value' and the_timestamp < '2024-03-01'

1. Qual é a melhor ordem do índice para esta consulta? Acredito que existam essas sugestões, que entram em conflito
   • igualdade primeiro, intervalos depois
   • "maior cardinalidade primeiro" (apenas para correspondência, não para intervalo?)
2. Faz muita diferença se o índice em the_timestamp for descor asc, ou isso só faz diferença ao percorrer várias colunas com consultas de intervalo... ou fazer BETWEENalgo assim?

Digamos que eu tenha uma tabela fooscom coluna thing_id.

Qual é o menor índice que eu poderia fazer para essas consultas?

select count(*) from foos where thing_id is null;
select id from foos where thing_id is null;

Conceitualmente, precisamos apenas do índice para acompanhar uma lista de chaves primárias que correspondem aos critérios. É possível/útil criar um índice parcial em... sem colunas?

Eu tentei isso e o postgres considerou um erro de sintaxe:

create index on foos where thing_id is null;

Isso funcionou

create index on foos (thing_id) where thing_id is null;

Mas isso resultará em escrever desnecessariamente o valor de thing_id (sempre NULL) para cada linha?

Eu sei que quando um índice está sendo criado simultaneamente, apenas um bloqueio SHARE ShareUpdateExclusiveLock é necessário.

Mas existe um tipo mais rigoroso de bloqueio necessário no início do processo por um pequeno período de tempo? Ou é necessário apenas um bloqueio SHARE ShareUpdateExclusiveLock para toda a operação?

Estou perguntando porque estou querendo saber quais tipos de tempos limite são vantajosos para definir ao criar um índice simultaneamente.

É possível ver o tempo de execução de um alter tablecomando?

Eu acho que com \timingo psql, ele mostra mais do que o tempo de execução, mas também a viagem de ida e volta do cliente e talvez outras coisas também. O que eu quero é o "tempo de execução" mostrado por explain analyze. Mas eu não posso alter tablepercorrer explain analyze(certo?).

Eu adicionei restrições NOT NULL a algumas colunas como esta, esperando que o comando fosse muito rápido por causa do NOT VALID. No entanto, demorou cerca de 60 a 100 segundos e pareceu travar a mesa o tempo todo (com base em outras coisas que falharam durante esse tempo). Isso está em uma tabela com cerca de 8.600.000 linhas. Por quê isso aconteceu? Teria sido melhor fazer 4 comandos em vez de 1?

Postgres 12

ALTER TABLE my_table
  ADD CONSTRAINT my_table_column1_not_null CHECK (column1 IS NOT NULL) NOT VALID,
  ADD CONSTRAINT my_table_column2_not_null CHECK (column2 IS NOT NULL) NOT VALID,
  ADD CONSTRAINT my_table_column3_not_null CHECK (column3 IS NOT NULL) NOT VALID,
  ADD CONSTRAINT my_table_column4_not_null CHECK (column4 IS NOT NULL) NOT VALID;

(A pergunta anterior era: o Postgres usará um índice de várias colunas ao definir várias colunas não nulas?)

Normalmente, quando defino uma coluna como não nula, se ela não tiver um índice, eu a adiciono primeiro, para que o postgres possa (espero) usar o índice enquanto faz a varredura da tabela enquanto bloqueia a tabela, para que a tabela seja bloqueada por um menor período de tempo.

Eu quero definir várias colunas não nulas, assim:

alter table foos
  alter column bar1 set not null
  alter column bar2 set not null
  alter column bar3 set not null
  alter column bar4 set not null;

Se eu fizer um índice de várias colunas para essas colunas, o postgres o usará ao verificar a tabela bloqueada antes de fazer essa alteração?

CREATE INDEX CONCURRENTLY my_index on foos (bar1, bar2, bar3, bar4);

E se eu fizer um índice parcial em IS NULL (ou IS NOT NULL)

CREATE INDEX CONCURRENTLY my_index on foos (bar1, bar2, bar3, bar4) where bar1 is null and bar2 is null and bar3 is null and bar4 is null;

tenho tabelas assim

# Things
id
user_id

# ThingUser
id
thing_id
user_id

Eu quero selecionar todas as coisas onde user_id=123 OU está relacionado ao usuário 123 por meio de ThingUser.

Acho que talvez eu esteja perto de algo assim:

select * from things
  left outer join thing_users on things.id=thing_users.thing_id and thing_users.user_id = 123
  where
    things.user_id = 123 OR
    thing_users.id IS NOT NULL

Mas no meu conjunto de dados muito grande, isso trava por vários segundos e depois o encerro.

No Postgres, essa consulta trava filmspara leituras e/ou gravações?

CREATE TABLE films2 AS
  TABLE films;

Eu tenho dois processos que executam código como este em paralelo:

begin;
update foos set unread=false where owner_id=123 and unread=true;
commit;

Isso resulta em impasses.

Meu entendimento do que causa deadlocks é como o cenário descrito nesta pergunta , com instruções UPDATE "entrelaçadas" atualizando duas linhas diferentes em uma ordem diferente. Eu não entendo como uma única instrução UPDATE pode resultar em um impasse. Não consigo replicar o cenário de deadlock usando duas sessões paralelas do psql no meu ambiente de desenvolvimento. Meus palpites de por que não posso replicá-lo:

1. Estou entendendo mal meu código que cria o erro de deadlock e, na verdade, existem várias instruções UPDATE em cada transação
2. O aspecto "entrelaçado" está acontecendo, mas "dentro" da instrução UPDATE que cobre várias linhas, por isso é difícil replicar.

É possível que este único UPDATE esteja criando o impasse?

Eu quero fazer algo assim:

begin;
select * from foos where owner_id=123 and unread=true order by id for update;
update foos set unread=false where owner_id=123 and unread=true;
commit;

O objetivo é evitar deadlock quando dois processos executam o UPDATE simultaneamente. Problema descrito mais aqui: Por que estou recebendo um impasse para uma única consulta UPDATE?

Na declaração onde adquiro o bloqueio, não preciso de nenhuma informação sobre as linhas. Eu só quero bloquear essas linhas específicas. Existe uma maneira de fazer isso (elegante ou hacky) que diz ao postgres para não fazer nenhum trabalho de realmente me fornecer os dados?

Eu quero fazer algo assim:

alter table foos
alter column bar type bool using bar::boolean;

baré atualmente uma coluna de texto. Algumas experiências mostram que o postgres fará coisas como converter uma string "t"para boolean true. No meu caso, isso é ótimo, mas gostaria de ler o comportamento completo antes de usar isso. Não consigo encontrar onde esta documentação.

Eu tenho uma tabela que representa intervalos de tempo.

   Column    |            Type             |                                Modifiers                                 
-------------+-----------------------------+--------------------------------------------------------------------------
 id          | bigint                      | not null default nextval('exploded_recurring_sessions_id_seq'::regclass)
 schedule_id | bigint                      | 
 start_time  | time without time zone      | 
 end_time    | time without time zone      | 

Vou então fazer uma consulta como esta:

select from my_table where localtime >= start_time and localtime < end_time;

A lógica de negócios aqui faz sentido no caso em que end_time é meia-noite, porque considero que um intervalo inclui start_time e não inclui end_time.

É claro que essa consulta não funciona, porque localtime nunca será < end_time quando end_time for 00:00:00.

Existem duas soluções para isso que eu posso pensar.

1. em vez de 00:00:00, use '00:00:00'::time without time zone - interval '1 microsecond'para meia-noite. A probabilidade de isso causar um cálculo incorreto provavelmente é baixa o suficiente para nunca acontecer. E mesmo que isso aconteça algumas vezes por ano, as consequências são essencialmente inexistentes, pelo menos como meu sistema está agora. No entanto, é uma solução feia, e usar dados errados como esse pode ter outras consequências no futuro.
2. select from my_table where localtime >= start_time and (localtime < end_time or end_time == '00:00:00'). Isso parece bastante simples, mas torna necessário fazer um ou mais índices especiais. Não é um problema se for a única solução, mas parece uma desvantagem que vale a pena evitar, se possível.

Existe alguma outra maneira de fazer isso? Algum tipo de função especial de data e hora que acomoda esse cálculo? Uma maneira melhor de representar os dados?

tabela (simplificada)

                                        Table "public.events"
      Column      |            Type             |                       Modifiers                        
------------------+-----------------------------+--------------------------------------------------------
 id               | integer                     | not null default nextval('events_id_seq'::regclass)
 duration         | integer                     | not null
 start_at         | timestamp without time zone | 
Indexes:
    "events_pkey" PRIMARY KEY, btree (id)
    "my_idx" gist (tsrange(start_at, end_at(events.*), '[)'::text))

função

CREATE FUNCTION end_at(rec events)
  RETURNS timestamp without time zone
  IMMUTABLE
  LANGUAGE SQL
AS $$
  SELECT $1.start_at + ($1.duration * ('00:00:01'::interval));
$$;

o que já estou fazendo com sucesso

O índice é usado para consultas como esta:

-- check if current time is within the start and end times
-- of event
where localtimestamp <@ tsrange(start_at, events.end_at, '[)')

E funciona bem.

O que eu quero fazer

Quero consultar eventos em que a hora atual é depois que eles terminaram. Maneiras que eu conheço de como fazer isso:

• where tsrange(localtimestamp, localtimestamp, '[]') >> tsrange(start_at, events.end_at, '[)'). Tenho certeza de que essa é a semântica que quero e explain analyzediz que está usando o índice, mas é um pouco feio e estou imaginando se há uma maneira melhor de expressar isso (e também estou vagamente incerto é a semântica que quero, pois Eu sou novo em intervalos).
• where localtimestamp > upper(tsrange(start_at, events.end_at, '[)'))+ um índice btree em upper(tsrange(start_at, events.end_at, '[)')). Isso funcionará bem, mas requer manter outro índice por perto.
• where localtimestamp > events.end_at. + um índice btree em events.end_at. Mesma situação acima.

Existe uma maneira mais elegante (ou correta) de alcançar o primeiro ponto acima?

Alguma outra ideia de como fazer isso?

Eu quero ter uma matriz de timestamp que rastreie uma série de eventos. O primeiro evento sempre acontece quando a linha é criada, então eu quero que a matriz seja instanciada com um valor padrão inicial. Eu tentei essas duas coisas e ambas resultam em now()serem executadas no momento da instrução. É possível conseguir isso com um valor padrão simples ou preciso escrever um gancho?

# ALTER TABLE messages ADD COLUMN testarray TIMESTAMP[] DEFAULT '{NOW()}';
ALTER TABLE
# ALTER TABLE messages ADD COLUMN testarray2 TIMESTAMP[] DEFAULT '{"NOW()"}';
ALTER TABLE


freedom_development=# \d messages
                                                 Table "public.messages"
    Column     |             Type              |                                Modifiers                                
---------------+-------------------------------+-------------------------------------------------------------------------
 id            | integer                       | not null default nextval('messages_id_seq'::regclass)
 testarray     | timestamp without time zone[] | default '{"2017-12-30 21:00:09.622951"}'::timestamp without time zone[]
 testarray2    | timestamp without time zone[] | default '{"2017-12-30 21:00:19.936001"}'::timestamp without time zone[]