> postgres -> returns results in 2.8 seconds
What kind of plan does it do ? seq scan on the big tables and hash join
on the small tables ?
> mysql -> takes around 16 seconds !!!! (This is with myisam ... with
> innodb it takes 220 seconds)
I'm not surprised at all.
Try the same Join query but with a indexed where + order by / limit on
the big table and you should get even worse for MySQL.
I found 3 tables in a join was the maximum the MySQL planner was able to
cope with before blowing up just like you experienced.
> I am all for postgres at this point, however just want to know why I am
> getting opposite results !!! Both DBs are on the same machine
Why do you say "opposite results" ?