Início / dba / Perguntas / 115025
Accepted
Luan Huynh
Asked: 2015-09-15 19:50:29 +0800 CST

Ajustando uma consulta com: índice, função de janela?

  • 772

Descrição

Ambiente: Centos 6-x64, Postgres Plus Advanced Server 9.3.
Tenho uma tabela com 4 colunas com dados conforme abaixo:

id    code_id            effective_date                 name
24      12       "2015-09-15 02:57:47.626751+00"      "dtsc_12"
429     215      "2015-09-15 02:57:47.626751+00"      "dtsc_215"
430     215      "2015-09-15 02:57:47.626751+00"      "dtsc_215"
465     233      "2015-09-15 02:57:47.626751+00"      "dtsc_233"
466     233      "2015-09-15 02:57:47.626751+00"      "dtsc_233"
468     234      "2015-09-15 02:57:47.626751+00"      "dtsc_234"

E eu quero obter todas as linhas com condição: group by code_ide max(effective_date) < current_timestamp. Então, o resultado que eu quero:

id     code_id             effective_date                name
24       12       "2015-09-15 02:57:47.626751+00"      "dtsc_12"
429      215      "2015-09-15 02:57:47.626751+00"      "dtsc_215"
465      233      "2015-09-15 02:57:47.626751+00"      "dtsc_233"
468      234      "2015-09-15 02:57:47.626751+00"      "dtsc_234"

minha codificação

create table win_a (
   id int not null primary key,
   code_id int,
   effective_date timestamp with time zone,
   name text
);

insert into win_a
select a,  a/2,  now() + trunc(random()  * 100) * '1 day'::interval, 'dtsc_' || (a/2)::int
from generate_series(1, 500) a
ORDER BY random() ;

create index win_a_indx on win_a using btree ( code_id, effective_date desc);

-- query 1
select a.*
from (
select id, code_id, effective_date, name
     , rank() over (partition by code_id order by effective_date desc, id) as rank
from win_a
where effective_date < current_timestamp
) a
where rank = 1 ;

-- query 2 -- false if in the same code_id -> have more than two same effective_date value
select a.*
from win_a a 
join (
    select code_id, max(effective_date) as max_ef
    from win_a 
    where effective_date < current_timestamp
    group by code_id ) b 
on a.code_id = b.code_id and a.effective_date = b.max_ef;

-- query 3 -- false if in the same code_id -> have more than two same effective_date value
select a.*
from win_a a 
where (code_id, effective_date) in
     (select code_id, max(effective_date) as max_ef
    from win_a 
    where effective_date < current_timestamp
    group by code_id );

Pergunta

  1. Posso usar o índice com a função de janela? (com a consulta 1, tento um índice, mas o Postgres ainda usa um seq-scan em vez de um index-scan)

  2. Como posso melhorar minha codificação?

postgresql optimization

1 respostas

  1. Best Answer

    Mais simples com DISTINCT ON- e também rápido para distribuição de dados com poucas linhas por code_id:

    SELECT DISTINCT ON (code_id) *
FROM   win_a
WHERE  effective_date < current_timestamp
ORDER  BY code_id, effective_date DESC, id;

    E adicione uma NOT NULLrestrição à coluna effective_dateou você terá que fazer isso:

    ...
ORDER  BY code_id, effective_date DESC NULLS LAST, id;

    E adapte o índice de acordo:

    CREATE INDEX win_a_idx on win_a (code_id, effective_date DESC NULLS LAST, id);

    Explicação:

    Observe também que adicionei a idcoluna!
    No entanto, o Postgres pode não usar o índice. Para poucas linhas por code_id, e enquanto você seleciona todas elas, uma varredura sequencial pode ser mais rápida em qualquer caso.

    Para muitas linhas code_id, outras técnicas de consulta podem ser mais rápidas:

    Para o seu caso - a menos que você tenha uma tabela separada contendo todos os possíveis code_id:

    WITH RECURSIVE cte AS (
   (  -- parentheses are required
   SELECT *
   FROM   win_a
   WHERE  effective_date < current_timestamp
   ORDER  BY code_id, effective_date DESC NULLS LAST, id
   LIMIT  1
   )
   UNION ALL
   SELECT w.*
   FROM   cte c
   ,      LATERAL (
      SELECT *
      FROM   win_a
      WHERE  code_id > c.code_id  -- lateral reference
      AND    effective_date < current_timestamp -- repeat condition
      ORDER  BY code_id, effective_date DESC NULLS LAST, id
      LIMIT  1
      ) w
   )
SELECT *
FROM   cte
ORDER  BY code_id;

    Isso realmente vai usar o index.

    Quanto à sua pergunta 1: uma tabela de teste com apenas 500 linhas não é muito útil. Postgres usará uma varredura sequencial em qualquer caso. Um índice não será mais rápido para uma tabela pequena como essa.

    • 2

relate perguntas