Hi David,
Thank you for your reply!
You mean when you change the date equality filter to a more recent date? - This is correct we have faced this when we change equality filter to more recent date.
Will be sending explain analyze on the thread when I get that.
We ran analyze many time to see if that resolves but looks like it is not buzzing. Also for regular analyze we don't run manually, they are all left to autoanalyze process.
Regards,
Virendra
On Tuesday, May 5, 2020, 6:34:37 PM PDT, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
On Tue, May 5, 2020 at 6:15 PM Virendra Kumar <viru_7683@xxxxxxxxx> wrote:
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.
David J.
