maxTrialfire's questions

Estou tentando criar um índice que dará suporte a consultas que usam meu operador personalizado. Isso está no PostgreSQL 10.4.

O operador personalizado

Eu segui as dicas nesta resposta SO para criar um operador que executa a correspondência de estilo "LIKE" em elementos em um texto ARRAY.

CREATE FUNCTION reverse_like (text, text) returns boolean language sql 
as $$ select $2 like $1 $$;
    
CREATE OPERATOR <~~ ( function =reverse_like, leftarg = text, rightarg=text );

O operador acima me permite fazer coisas como

SELECT 'ab%' <~~ ANY('{"abc","def"}');

O esquema, índice e consulta

Eu tenho uma tabela com visitas de tráfego da web chamada sessionsque inclui uma coluna de matriz.

CREATE TABLE sessions
(
   session_id    varchar(24) NOT NULL,
   first_seen    timestamp,
   domains       varchar[]
);

Para consultar a coluna de domínios para ver se um determinado domínio (ou nome de domínio parcial/curinga) foi visitado, posso fazer o seguinte:

SELECT count(*)
FROM session_4070ba14_f081_41cb_9ef7_9dd385934da7
WHERE 'www.foo%' <~~ ANY(domains);

Eu quero acelerar as consultas acima com o índice GIN. Então eu criei o índice da seguinte forma:

CREATE INDEX idx_domains ON session USING GIN(domains);

A questão

Depois de executar analise na mesa e set enable_seqscan = false;não tenho sorte em fazer o Postgres empregar esse índice. Está sempre fazendo um seqscan. Ele usa o índice acima de operadores de matriz, @>mas não para o meu <~~operador personalizado.

Eu acho que é porque o índice GIN não sabe como lidar com meu operador personalizado - então eu preciso criar uma classe de operador e depois criar meu índice usando isso? Ou crio um índice funcional?

Estou consultando o catálogo do sistema no Postgresql 9.6.4

Obter um conjunto de resultados de tabelas e seus índices é simples, o que estou perdendo é o tipo de índice (BTREE, BRIN, etc.) Não consigo encontrar o tipo de índice em nenhum lugar nos catálogos do sistema.

Como posso consultar os catálogos para obter uma lista de índices junto com seu tipo?

Considere uma tabela que registra visitas

create table visits (
  person varchar(10),
  ts timestamp, 
  somevalue varchar(10) 
)

Considere estes dados de exemplo (timestamp simplificado como contador)

ts| person    |  somevalue
-------------------------
1 |  bob      |null
2 |  bob      |null
3 |  jim      |null
4 |  bob      |  A
5 |  bob      | null
6 |  bob      |  B
7 |  jim      |  X
8 |  jim      |  Y
9 |  jim      |  null

Estou tentando levar adiante o último somevalue não nulo da pessoa para todas as suas visitas futuras até que esse valor mude (ou seja, torne-se o próximo valor não nulo).

O conjunto de resultados esperados se parece com isto:

ts|  person   | somevalue | carry-forward 
-----------------------------------------------
1 |  bob      |null       |   null
2 |  bob      |null       |   null
3 |  jim      |null       |   null
4 |  bob      |  A        |    A
5 |  bob      | null      |    A
6 |  bob      |  B        |    B
7 |  jim      |  X        |    X
8 |  jim      |  Y        |    Y
9 |  jim      |  null     |    Y

Minha tentativa fica assim:

 select *, 
  first_value(somevalue) over (partition by person order by (somevalue is null), ts rows between UNBOUNDED PRECEDING AND current row  ) as carry_forward

 from visits  
 order by ts

Observação: o (somevalue is null) é avaliado como 1 ou 0 para fins de classificação, para que eu possa obter o primeiro valor não nulo na partição.

O acima não me dá o resultado que estou procurando.

Estou usando o postgres 9.5

Se eu tiver uma tabela com 2 colunas assim:

CREATE TABLE mystuff
(
  somestring character varying(256),
  timestamp_ timestamp without time zone NOT NULL
)

Esse índice de árvore b de várias colunas:

CREATE INDEX mystuff_idx
  ON mystuff
  USING btree
  (timestamp_ , somestring varchar_pattern_ops);

ajuda no desempenho da seguinte consulta?

Select count(*)
FROM mystuff
where timestamp_ > '01/01/2012' and somestring like 'foo%'

A respeito ?

Select count(*)
FROM mystuff
where timestamp_ > '01/01/2012' and somestring like '%foobar'

Para um ponto extra, explique como uma btree multicoluna é usada para pesquisa quando há uma cláusula semelhante na segunda (ou terceira etc...) coluna do índice.

Estou usando Postgre 9.5. Eu tenho uma tabela que registra acessos de página de vários sites. Esta tabela contém cerca de 32 milhões de linhas de 1º de janeiro de 2016 a 30 de junho de 2016.

CREATE TABLE event_pg (
   timestamp_        timestamp without time zone NOT NULL,
   person_id         character(24),
   location_host     varchar(256),
   location_path     varchar(256),
   location_query    varchar(256),
   location_fragment varchar(256)
);

Estou tentando ajustar uma consulta que conta o número de pessoas que realizaram uma determinada sequência de acessos à página. A consulta destina-se a responder a perguntas como "quantas pessoas visualizaram a página inicial, acessaram o site de ajuda e visualizaram a página de agradecimento"? O resultado se parece com isso

╔════════════╦════════════╦═════════════╗
║  home-page ║ help site  ║ thankyou    ║
╠════════════╬════════════╬═════════════╣
║ 10000      ║ 9800       ║1500         ║
╚════════════╩════════════╩═════════════╝

Observe que os números estão diminuindo, o que faz sentido, porque dos 10.000 que visualizaram a página inicial, 9.800 foram para o site de ajuda e desses 1.500 foram para a página de agradecimento.

O SQL para uma sequência de 3 etapas usa junções laterais da seguinte maneira:

SELECT 
  sum(view_homepage) AS view_homepage,
  sum(use_help) AS use_help,
  sum(thank_you) AS thank_you
FROM (
  -- Get the first time each user viewed the homepage.
  SELECT X.person_id,
    1 AS view_homepage,
    min(timestamp_) AS view_homepage_time
  FROM event_pg X 
  WHERE X.timestamp_ between '2016-04-23 00:00:00.0' and timestamp '2016-04-30 23:59:59.999'
  AND X.location_host like '2015.testonline.ca'
  GROUP BY X.person_id
) e1 
LEFT JOIN LATERAL (
  SELECT
    Y.person_id,
    1 AS use_help,
    timestamp_ AS use_help_time
  FROM event_pg Y 
  WHERE 
    Y.person_id = e1.person_id AND
    location_host = 'helpcentre.testonline.ca' AND
    timestamp_ BETWEEN view_homepage_time AND timestamp '2016-04-30 23:59:59.999'
  ORDER BY timestamp_
  LIMIT 1
) e2 ON true 
LEFT JOIN LATERAL (
  SELECT
    1 AS thank_you,
    timestamp_ AS thank_you_time
  FROM event_pg Z 
  WHERE Z.person_id = e2.person_id AND
    location_fragment =  '/file/thank-you' AND
    timestamp_ BETWEEN use_help_time AND timestamp '2016-04-30 23:59:59.999'
  ORDER BY timestamp_
  LIMIT 1
) e3 ON true;

Eu tenho um índice em timestamp_, person_ide as locationcolunas. As consultas em intervalos de alguns dias ou semanas são muito rápidas (1s a 10s). Onde fica lento é quando tento executar a consulta para tudo entre 1º de janeiro e 30 de julho. Demora mais de um minuto. Se você comparar as duas explicações abaixo, poderá ver que ele não usa mais o índice timestamp_ e, em vez disso, faz um Seq Scan porque o índice não compraria nada, pois estamos consultando "o tempo todo", portanto, praticamente todos os registros na tabela .

• Explique por um período de alguns dias (usa o índice): https://explain.depesz.com/s/2tOi
• Explique por 6 meses (seq scan): https://explain.depesz.com/s/c0yq

Agora percebo que a natureza do loop aninhado da junção lateral vai desacelerar quanto mais registros ele tiver que percorrer, mas existe alguma maneira de acelerar essa consulta para grandes intervalos de datas para que ela seja dimensionada melhor?