Additionally in the plan which mysql makes and showing the highest response time, is it suffering because of differences of the speed of the underlying IO/storage or is it just because of the optimization features which are available in postgres and not there in mysql ? Trying to understand if it can be identified from the execution plan itself.
I think trying to compare postgres and mysql plans against each other is not a very useful endeavor. There are fundamental design decisions between the two. Focus on making your Postgres query the best it can be, full stop. Optimize your queries, make sure the database is analyzed, and tweak some configs as needed.
Also, you cannot accidentally forget a join condition.
Yes, this is the primary reason. Cartesian joins hurt.
Again , not able to clearly understand the third point you said below. Can you please clarify a bit more. Do you mean we should write it as exists /not exists rather IN and NOT IN and that will improve the performance?
It gives Postgres more options on how to do things, so yes, it can be better.
Cheers,
Greg
