postgresql query is fast with one filter value, very slow with other value

Question

I have two tables each with 100 million+ rows, table_1 and tabke_2.

We insert 60,000+ rows with the same date of "today" in one column of each table. This "date" field is indexed in each of the two tables. We're doing this insert every day.

Following the insert, if we run a query

select count(*)
from ((select field1 from table_1 where date_field = 'yyyy-mm-dd' --**yesterday's date** ) a 
INNER JOIN
     (select field1 from table_2 where date_field = 'yyyy-mm-dd' --**yesterday's date** ) b 
ON a.field1 = b.field1) c

runs in 1 second

select count(*)
from ((select field1 from table_1 where date_field = 'yyyy-mm-dd' --**today's date** ) a 
INNER JOIN
     (select field1 from table_2 where date_field = 'yyyy-mm-dd' --**today's date** ) b 
ON a.field1 = b.field1) c

runs in 6 hours!

Tomorrow, this query will run in 1 second, and the next day's date query runs for 6 hours.

I'm totally puzzled. Why does the same query runs for 1 second, but the recently inserted data query runs for 6 hours? The next day the 6 hours query runs in 1 second, and that day's date query runs for 6 hours...

Answer 1

I would check the explain plans for the queries, and I suspect that statistics are being automatically gathered after your slow-performing query that change the execution plan by the next day.

Edit: Oh so the fix would be to invoke analyse after loading new data.