2

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

  1. 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)

  2. How can I improve my coding?

2

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

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:

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:

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.