Querying large tables in postgresql

Question

Got a problem querying two large tables on postgres, both of them having a indexed column to identify the year, wich i'm using to reduce the number of rows, something like this:

WITH table_1 AS (SELECT * FROM table_1 t1 WHERE t1.year = 2022),
table_2 AS (SELECT * FROM table_2 t2 WHERE t2.year = 2022)
select * from table_1 t1
join table_2 t2 on (t1.t1_cod = t2.t1_cod)

Notice that table_1 still got 20M+ rows(more than 50M in total), in order to join with 300K+ rows(close to 1M in total) from table_2. Even doesn't look so nice, this is the fastest ways i could find. Now, in order to set the filtered year as the current year, we got a problem:

WITH table_1 AS (SELECT * FROM table_1 t1 WHERE t1.year = extract(year from current_date)),
table_2 AS (SELECT * FROM table_2 t2 WHERE t2.year = extract(year from current_date))
select * from table_1 t1
join table_2 t2 on (t1.t1_cod = t2.t1_cod)

The extract function(probably) executes for each row, making the query several times slower. I've tried even using a temporary table just to select the year, but querying time looks the same.

WITH aux_table as (select extract(year from current_date)as aux_year limit 1)
table_1 AS (SELECT * FROM table_1 t1 WHERE t1.year = (select aux_year from aux_table /*tryed limit 1 here too*/)),
table_2 AS (SELECT * FROM table_2 t2 WHERE t2.year = (select aux_year from aux_table))
select * from table_1 t1
join table_2 t2 on (t1.t1_cod = t2.t1_cod)

To compare, the whole query gets 4 mins to execute when filtering the year = 2022, but takes at least 3 times that when using the extract or other methods. If anyone could give me a hint, it would be helpful.

Answer 1

Trying different methods, discovered that extract(year from current_date) function was not using the index of the year column, so declared a cast to int and works pretty fine. Using extract(year from current_date)::int had returned results in 2.5 min, before was returning around 11-13 min. Tks to J.D. .