I am working on a project in PostgreSQL. I have a view which joins a couple of tables to have the columns case_id, cohort, status, assignedto, batch_number, fail_reason, hold_reason, complete_ts.
On top of this view, I have a query which looks at the batch_number which is a varchar(255) datatype which is created using the year and week number using an UPDATE statement to set batch_number = to_char(CURRENT_DATE, 'IYYY-IW') - so when a case is completed it includes the case in the batch number for that week. However I have hit a block where for certain batch numbers the select / where statement does not work. The stats are below:
Case 1: Select * from table_view Outputs: 40052 records in 3.66 seconds
Case 2: Select * from table_view where Batch_number= ‘2024-09’ Outputs: 2894 records in 3.15 seconds
Case 3: Select * from table_view Batch_number= ‘2024-10’ Outputs: 3765 records in 40.15 seconds.
Why is the script taking more time for '2024-10'? Note that the table_view is not a table or materialised view as this is dynamic and needs to be a view
This has not been an issue for any batch_numbers from 2023-48 until 2024-10 but it is now an issue for week 10 and 11 of 2024. Can someone please help?