Versão curta: quando executo uma transação no IntelliJ, o gráfico "transações por segundo" aumenta em 10 tx/s - só espero que aumente em 1. Portanto, parece que
minha compreensão do gráfico "transações por segundo" em PgAdmin está errado ou que PgAdmin está fazendo algo inesperado.

Aqui está uma descrição detalhada do meu teste e minhas expectativas:

Eu inicio o PgAdmin, espero um pouco para ver quantas "transações por segundo" temos quando apenas o PgAdmin está conectado (até no máximo 3) e, em seguida, executo uma consulta de teste simples 4 vezes.

Esta é a consulta de teste que executo no IntelliJ. Observe que o modo de transação está definido como "Auto", então espero 4 transações (já que executo esta consulta 4 vezes)

Isto é o que eu posso ver no PgAdmin:

Detalhes:

1. (no gráfico Server Sessions) é quando começo a executar a consulta-teste. Podemos ver que uma nova sessão do servidor é criada. Isso é esperado, pois o IntelliJ se conectará ao banco de dados.
2. (no gráfico "Transações por segundo") é o tempo em que executo minha consulta de teste (4 vezes)

• antes de começar, podemos ver que às vezes acontecem cerca de 3 transações por segundo (como apenas o PgAdmin está conectado, acho que essas são as consultas para mostrar os dados do gráfico)
• mas quando executo minha consulta de teste simples, as transações por segundo aumentam para 14/15 transações por segundo: Por que isso é tão alto? Eu esperaria apenas 1 transação a mais do que antes: então max. 4 no total

Outra coisa que parece estranha é que o tx-per seconds sobe para 10 quando desconecto o IntelliJ do banco de dados:

Alguma ideia de por que isso está acontecendo?

• O IntelliJ talvez mantenha algumas transações abertas enquanto está conectado?
• Também posso ver isso acontecer quando outros aplicativos são desconectados. Alguns até têm picos extremos de até 1000 tx/seg.
• talvez esteja relacionado ao banco de dados? Eu uso postgres-13 com timescale-db 2.7

Eu tenho uma tabela de série temporal grande (~ 100 milhões de linhas) t_16no Postgres 11.5 , onde a chave primária é um campo abs_date_timedo tipo timestamp.

Esta é uma continuação desta pergunta:

• Por que um CTE causa uma varredura completa da tabela?

Inicialmente pensei que estava relacionado a um CTE. Mas esta consulta é lenta, mesmo sem CTE.

Como posso fazer a consulta a seguir usar a chave primária index , para evitar uma verificação completa da tabela?

tsrange

Esta consulta leva ~ 20 segundos no meu PC de desenvolvimento:

SELECT t_16_gen.*
FROM t_16_gen,
     (VALUES (tsrange('["2019-11-26 12:00:00","2019-11-26 12:00:15")'))
           , (tsrange('["2019-11-26 13:00:00","2019-11-26 13:00:15")'))) as ranges (time_range)
WHERE (abs_date_time >= LOWER(ranges.time_range)
    AND abs_date_time <  UPPER(ranges.time_range));

Explique o plano:

Gather  (cost=1000.00..6185287.15 rows=20571433 width=80)
  Workers Planned: 2
  ->  Nested Loop  (cost=0.00..4127143.85 rows=8571430 width=80)
        Join Filter: ((t_16_gen.abs_date_time >= lower("*VALUES*".column1)) AND (t_16_gen.abs_date_time < upper("*VALUES*".column1)))
        ->  Parallel Seq Scan on t_16_gen  (cost=0.00..1620000.38 rows=38571438 width=80)
        ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=32)

Na produção, o conjunto de tsranges vem de uma UDF - mas sempre haverá apenas alguns intervalos (<200) e cada intervalo terá menos de 1500 linhas e os intervalos não se sobreporão.

Timestamps simples em vez detsrange

Quando usamos timestamps diretamente (ou seja, não usando tsrange LOWER()e UPPER()), a consulta já é mais rápida. Esta consulta leva ~ 7 segundos no meu PC de desenvolvimento:

SELECT t_16_gen.*
FROM t_16_gen,
     (VALUES ('2019-11-26 12:00:00'::timestamp,'2019-11-26 12:00:15'::timestamp)
           , ('2019-11-26 13:00:00','2019-11-26 13:00:15')) as ranges (start_incl, end_excl)
WHERE (abs_date_time >= ranges.start_incl
    AND abs_date_time <  ranges.end_excl);

Explique o plano:

Nested Loop  (cost=0.00..5400001.28 rows=20571433 width=80)
  Join Filter: ((t_16_gen.abs_date_time >= "*VALUES*".column1) AND (t_16_gen.abs_date_time < "*VALUES*".column2))
  ->  Seq Scan on t_16_gen  (cost=0.00..2160000.50 rows=92571450 width=80)
  ->  Materialize  (cost=0.00..0.04 rows=2 width=16)
        ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=16)

ORcondições = RÁPIDO

Quando reescrevo a consulta para usar condições OR , é rápido. Esta consulta leva ~ 200ms no meu PC de desenvolvimento:

SELECT t_16_gen.*
FROM t_16_gen
WHERE (abs_date_time >= '2019-11-26 12:00:00' AND abs_date_time < '2019-11-26 12:00:15')
   OR (abs_date_time >= '2019-11-26 13:00:00' AND abs_date_time < '2019-11-26 13:00:15');

Explique o plano:

Gather  (cost=13326.98..1533350.92 rows=923400 width=80)
  Workers Planned: 2
  ->  Parallel Bitmap Heap Scan on t_16_gen  (cost=12326.98..1440010.92 rows=384750 width=80)
        Recheck Cond: (((abs_date_time >= '2019-11-26 12:00:00'::timestamp without time zone) AND (abs_date_time < '2019-11-26 12:00:15'::timestamp without time zone)) OR ((abs_date_time >= '2019-11-26 13:00:00'::timestamp without time zone) AND (abs_date_time < '2019-11-26 13:00:15'::timestamp without time zone)))
        ->  BitmapOr  (cost=12326.98..12326.98 rows=925714 width=0)
              ->  Bitmap Index Scan on t_16_pkey  (cost=0.00..5932.64 rows=462857 width=0)
                    Index Cond: ((abs_date_time >= '2019-11-26 12:00:00'::timestamp without time zone) AND (abs_date_time < '2019-11-26 12:00:15'::timestamp without time zone))
              ->  Bitmap Index Scan on t_16_pkey  (cost=0.00..5932.64 rows=462857 width=0)
                    Index Cond: ((abs_date_time >= '2019-11-26 13:00:00'::timestamp without time zone) AND (abs_date_time < '2019-11-26 13:00:15'::timestamp without time zone))

UNION= RÁPIDO

Quando reescrevo a consulta para usar condições UNION , também é rápido. Esta consulta leva ~ 220ms no meu PC de desenvolvimento:

SELECT t_16_gen.*
FROM t_16_gen
WHERE (abs_date_time >= '2019-11-26 12:00:00' AND abs_date_time < '2019-11-26 12:00:15')
UNION
SELECT t_16_gen.*
FROM t_16_gen
WHERE (abs_date_time >= '2019-11-26 13:00:00' AND abs_date_time < '2019-11-26 13:00:15');

Explique o plano:

Unique  (cost=1032439.64..1069468.20 rows=925714 width=80)
  ->  Sort  (cost=1032439.64..1034753.93 rows=925714 width=80)
"        Sort Key: t_16_gen.abs_date_time, t_16_gen.c_422, t_16_gen.c_423, t_16_gen.c_424, t_16_gen.c_425, t_16_gen.c_426, t_16_gen.c_427, t_16_gen.c_428, t_16_gen.c_429, t_16_gen.c_430, t_16_gen.c_431, t_16_gen.c_432, t_16_gen.c_433, t_16_gen.c_434, t_16_gen.c_435"
        ->  Append  (cost=0.57..892513.13 rows=925714 width=80)
              ->  Index Scan using t_16_pkey on t_16_gen  (cost=0.57..439313.71 rows=462857 width=80)
                    Index Cond: ((abs_date_time >= '2019-11-26 12:00:00'::timestamp without time zone) AND (abs_date_time < '2019-11-26 12:00:15'::timestamp without time zone))
              ->  Index Scan using t_16_pkey on t_16_gen t_16_gen_1  (cost=0.57..439313.71 rows=462857 width=80)
                    Index Cond: ((abs_date_time >= '2019-11-26 13:00:00'::timestamp without time zone) AND (abs_date_time < '2019-11-26 13:00:15'::timestamp without time zone))

Reproduzindo o problema

Para reproduzir o problema, posso criar uma nova tabela e preenchê-la com dados fictícios. Em seguida, reinicie o banco de dados antes de cada teste, para que os dados não sejam armazenados em cache.
Nota: a consulta de inserção pode ser executada por vários minutos!

create table if not exists t_16_gen (
    abs_date_time timestamp constraint t_16_pkey primary key,
    c_422 bigint,
    c_423 bigint,
    c_424 real,
    c_425 real,
    c_426 real,
    c_427 real,
    c_428 real,
    c_429 real,
    c_430 bigint,
    c_431 real,
    c_432 real,
    c_433 real,
    c_434 bigint,
    c_435 real
);

INSERT INTO t_16_gen
SELECT ts, 1,2,3,4,5,6,7,8,9,10,11,12,13,14
FROM (SELECT generate_series('2019-11-26'::timestamp, '2019-11-27', '1 millisecond') as ts) as gs;

Como podemos passar o agrupamento para uma função definida pelo usuário?

por exemplo, eu posso usar um agrupamento codificado (por exemplo "de-AT-x-icu") assim:

CREATE OR REPLACE FUNCTION test(agg_1 jsonb, agg_2 jsonb) RETURNS jsonb
    LANGUAGE SQL
AS
$$
SELECT  jsonb_build_object(
  'count', (agg_1 -> 'count')::int8 + (agg_2 -> 'count')::int8
, 'max', greatest(agg_1 ->> 'max' collate "de-AT-x-icu", agg_2 ->> 'max' collate "de-AT-x-icu")
)
$$;

e o teste funciona:

select test(
  jsonb_build_object('count', 2 , 'max', 'H'),
  jsonb_build_object('count', 0, 'max', 'i')
);

Tentamos usar um argumento-texto,

CREATE OR REPLACE FUNCTION test2(agg_1 jsonb, agg_2 jsonb, text_collation text) RETURNS jsonb
    LANGUAGE SQL
AS
$$
SELECT  jsonb_build_object(
                'count', (agg_1 -> 'count')::int8 + (agg_2 -> 'count')::int8
            , 'max', greatest(agg_1 ->> 'max' collate text_collation, agg_2 ->> 'max' collate text_collation)
            )
$$;

mas isso falha com:

ERROR: collation "text_collation" for encoding "UTF8" does not exist

Qual é a melhor maneira de passar/usar um agrupamento em uma UDF nesse caso?

Não entendo por que o agrupamento padrão na minha instância do psqlserver 11 retorna um resultado diferente do uso com agrupamento explícito.

Detalhes:

show LC_CTYPE;        -- de-AT
show LC_COLLATE;      -- de-AT

Quando uso o agrupamento padrão, recebo uma saída inesperada:

demo=# SELECT unnest(array['a','B', 'A']) order by 1;
 unnest 
--------
 A
 B
 a
(3 rows)

Quando adiciono o agrupamento padrão explicitamente, recebo a saída esperada:

demo=# SELECT unnest(array['a','B', 'A']) collate "de-AT-x-icu" ORDER BY 1;
 unnest 
--------
 a
 A
 B
(3 rows)

A ordenação de-AT-x-icuusa exatamente os valores padrão para LC_COLLATEe LC_COLTYPEmostrados acima:

SELECT collname, collcollate, collctype
FROM pg_collation
where collname like '%de-AT%';

  collname   | collcollate | collctype 
-------------+-------------+-----------
 de-AT-x-icu | de-AT       | de-AT

Então acho que ambas as consultas devem retornar o mesmo resultado: O que estou perdendo?

Mais informações:

• versão do postgresql11.5
• SO dentro do contêiner do Docker:
  Linux 8660fb4cef84 4.9.184-linuxkit #1 SMP Tue Jul 2 22:58:16 UTC 2019 x86_64 Linux

Por exemplo, temos alguns dados em int8campos e queremos construir agregações: por exemplo, a soma dos quadrados. O resultado também deve ser int8(não um float).

Então, obviamente, calcular a soma dos quadrados para int8-valores pode estourar o int8intervalo e podemos obter bigint out of rangeerros.

Como podemos lidar com esse caso?

• por exemplo, existe uma maneira de "pegar" este erro e retornar um padrão (por exemplo, max- int8)?
• ou outras ideias, como evitar isso

Exemplo:

create table test
( val int8);

insert into test VALUES (9223372036854775807), (9223372036854775807);

Esta consulta falhará com bigint out of range:

select sum(val*val) from test;

Para fazer a soma funcionar, podemos converter para numeric:

select sum(val::numeric*val) from test;

mas nossa aplicação precisa de um resultado do tipo int8, então podemos usar leastegreatest

select greatest(+9223372036854775807, least(-9223372036854775808, sum(val::numeric*val))) from test;

Existe alguma maneira interna fácil de "converter"/"converter"/"grampear" de numericvolta ao int8intervalo?

link para exemplo de sqlfiddle

Temos algumas consultas complexas que foram muito lentas. Consegui reduzir a consulta a uma simples reprodução. Parece que a combinação de greateste logé a causa, mas não entendo o porquê.

Aqui está um exemplo completo do sql-fiddle para executar as consultas - e você também pode View the execution Plansdas consultas (pressione o link na parte inferior do resultado da consulta na página do sql-fiddle)

Então aqui está a consulta lenta :

select count(value)
from (
         SELECT  log(greatest(1e-9, x)) as value
         from (select generate_series(1, 20000, 1) as x) as d
     ) t;

Nós apenas geramos uma série de 20k números e usamos log(greatest()). Essa consulta leva cerca de 1,5 segundos.

Achei que calcular o log pode demorar, mas a consulta a seguir também é rápida ( ~5ms ):

select count(value)
from (
         SELECT  log(x) as value
         from (select generate_series(1, 20000, 1) as x) as d
     ) t;

Apenas como teste eu troquei greateste log- isso também é rápido ( ~5ms ):

select count(value)
from (
         SELECT  greatest(1e-9, log(x)) as value
         from (select generate_series(1, 20000, 1) as x) as d
     ) t;

O QUERY PLANSpara todas as 3 consultas são os mesmos:

Aggregate (cost=22.51..22.52 rows=1 width=8)
-> Result (cost=0.00..5.01 rows=1000 width=4)

Alguém pode explicar por que a primeira consulta é tão lenta - e talvez alguém conheça uma solução alternativa?

Mais detalhes

plataformas lentas

Eu recebo resultados semelhantes em todos eles (a primeira consulta é uma magnitude mais lenta):

• SQL Fiddle usa página 9.6
• meu PC local com resultados semelhantes: Win10 64bit, pg 11.5 rodando no Docker
• servidor remoto: Ubuntu 18.04 64 bits executando pg 11.5 no Docker
• rextester. com
  • consulta lenta ~ 3 segundos
  • consulta rápida ~ 0,5 segundos

contar

Quando mudo count(value)para count(*)ou count(1)(número um) a consulta é rápida

• mas isso não me ajuda porque a consulta de produção nem inclui uma contagem
• de qualquer forma, eu me pergunto por que há uma diferença neste caso (não há valores nulos nos dados)