主页 / dba / 问题 / 115025
Accepted
Luan Huynh
Asked: 2015-09-15 19:50:29 +0800 CST

使用以下参数调整查询：索引、窗口函数？

  • 772

描述

环境：Centos 6-x64，Postgres Plus Advanced Server 9.3。
我有一个包含 4 列数据的表格，如下所示：

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"

我想获得所有符合条件的行：group by code_id和max(effective_date) < current_timestamp。所以，我想要的结果：

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"

我的编码

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 );

问题

  1. 我可以将索引与窗口函数一起使用吗？（对于查询 1，我尝试了一个索引，但 Postgres 仍然使用序列扫描而不是索引扫描）

  2. 我怎样才能改进我的编码？

postgresql optimization

1 个回答

  1. Best Answer

    最简单的- 并且每个行数很少DISTINCT ON的数据分布也很快：code_id

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

    并向该列添加NOT NULL约束effective_date，否则您必须这样做：

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

    并相应地调整索引：

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

    解释：

    另请注意，我添加了该id列！
    然而，Postgres 可能根本不使用索引。对于每行几行code_id，并且当您选择所有行时，顺序扫描在任何情况下都可能是最快的。

    对于许多行，code_id其他查询技术可能更快：

    对于你的情况 - 除非你有一个单独的表来容纳所有可能的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;

    这实际上是要使用索引。

    至于你的问题 1：只有 500 行的测试表不是很有用。Postgres 在任何情况下都会使用顺序扫描。对于这样的小表，索引不会更快。

    • 2

相关问题