0

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.

6
  • 1
    Do you have an index on t1.t1_cod and index on t2.t1_cod?
    – J.D.
    Sep 8, 2022 at 13:33
  • Yep. In fact, join that tables with the t1_cod was even slower because of the number of rows. In that case, postgres performs a sequencial scan, even if the column is indexed. Sep 8, 2022 at 13:37
  • Using the filter before the join was a try to reduce rows to make the join faster, and worked pretty fine, but still got a problem setting up the filtered year Sep 8, 2022 at 13:44
  • 1
    Well really, my question should've been, do you have an index on (year, t1_cod) for both tables?
    – J.D.
    Sep 8, 2022 at 13:49
  • Yep, both columns on both tables. Sep 8, 2022 at 14:11

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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