the order of tables in from clause or where clause can result in different query plans, with different execution time.
[...]
--fast query big table left explain analyze select * from testjoinperf.tempbig join testjoinperf.tempsmall using(idsmall) where idsmall between 48000 and 49000
-- slow query, small table left. explain analyze select * from testjoinperf.tempsmall join testjoinperf.tempbig using(idsmall) where idsmall between 48000 and 49000
Confirmed 9.6.2 on Ubuntu. Not sure this is properly classified as a bug but its definitely an area where improvement would seem desirable. I am a particularly heavy user of the join predicate "USING" and never really thought to look at this dynamic (without the WHERE clause it doesn't matter, both tables up end sequentially scanned).
Someone more knowledgeable than myself will need to comment on the technical aspects as to why "where idsmall" seems to be linked to the left relation instead of the one with a more favorable execution plan.
Boshomi, how did you stumble across this anyway - just with artificial data or did you come up with that after hitting the problem with real data?