Tuning a query with: index, window function?

Question

Description

Environment: Centos 6-x64, Postgres Plus Advanced Server 9.3.
I have a table with 4 columns with data as below:

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"

And I want to get all row with condition: group by code_id and max(effective_date) < current_timestamp. So, the result I want:

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"

My coding

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

Question

Can I use index with window function? (with query 1, I try an index but Postgres still uses a seq-scan instead of an index-scan)
How can I improve my coding?

score 2 · Accepted Answer · edited May 23 '17 at 12:40

Simplest with DISTINCT ON - and also fast for data distribution with few rows per code_id:

Select first row in each GROUP BY group?

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

And add a NOT NULL constraint to the column effective_date or you have to make that:

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

And adapt the index accordingly:

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

Explanation:

Unused index in range of dates query

Note also that I added the id column!
However, Postgres may not use the index at all. For few rows per code_id, and while you select all of them, a sequential scan may be fastest in any case.

For many rows per code_id other query techniques may be faster:

Optimize GROUP BY query to retrieve latest record per user

For your case - unless you have a separate table holding all possible 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;

This is actually going to use the index.

As for your question 1: a test table with just 500 rows is not very useful. Postgres will use a sequential scan in any case. An index is not going to be faster for a small table like that.

Tuning a query with: index, window function?

Description

My coding

Question

1 Answers1