On Tue, Dec 10, 2019 at 3:40 AM Fahiz Mohamed <fahiz@netwidz.com> wrote:
Thank you very much for your prompt responses.
I have analysed more regarding this and found the long running query.
I ran "explain analyse" on this query and I got following result. (We have 2 identical DB instances and they consist of same data. Instane 1 took 20+ second to process and instance 2 took less than a second)
They do not consist of the same data. One returns 17 rows, the other 22.
One finds 5635 rows (scattered over 40765 blocks!) where qname_id = 251, the other find 85 rows for the same condition. It seems the first one is not very well vacuumed.
I don't know if these differences are enough to be driving the different plans (the estimation differences appear smaller than the actual differences), but clearly the data is not the same.
Your first query is using the index idx_alf_node_mdq in a way which seems to be counter-productive. Perhaps you could inhibit it to see what plan it chooses then. For example, specify in your query "type_qname_id+0 = 240" to prevent the use of that index. Or you could drop the index, if it is not vital.
But if the data has not be ANALYZEd recently, you should do that before anything else. Might as well make it a VACUUM ANALYZE.