Optimizer is behaving little weird in the sense that for change in one filter
You mean when you change the date equality filter to a more recent date?
it is using NESTED LOOP JOIN and running for over 30 seconds whereas for other filter is working fine using HASH JOIN and under mili seconds.
Which means it runs to completion which means EXPLAIN ANALYZE is possible and considerably more helpful.
We have played little bit around default_statistics_target, sometimes it worked when the setting is around 1500 other times it doesn't work even with setting as high as 5000. Is there anything community can suggest us in resolving this?
Given that adding more data causes the good plan to stop working I would have to say that increasing how often you run ANALYZE on the table, irrespective of default_statistics_target, is probably a good thing to try.
