Tuning a query with: index, window function?
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 &lt; 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 &lt; 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 &gt; c.code_id  -- lateral reference
      AND    effective_date &lt; 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.