Re: BUG #14646: performance hint to remove

On Wed, May 10, 2017 at 10:37 AM, <boshomi(at)gmail(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 14646
> Logged by: Boshomi
> Email address: boshomi(at)gmail(dot)com
> PostgreSQL version: 9.6.2
> Operating system: Linux opensuse
> Description:
>
> 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?

David J.