Re: PostgreSQL seems to create inefficient plans in simple conditional joins

It may be more for -hackers, but I often hear "this wont be used because of
planning time increase". Now as I know we have statistics on real query
time after few runs that is used to decide if plan should be switched.
Can this statistics be used to apply advanced planning features for
relatively long running queries? E.g. a parameter like
sophisticated_planning_l1_threshold=500ms. If query runs over this
threshold, replan it with more sophisticated features taking few more
millis. Possibly different levels can be introduced. Also allow to set
threshold to 0, saying "apply to all queries right away".
Another good option is to threshold against cumulative query time. E.g. if
there was 10000 runs 0.5 millis each, it may be beneficial to spend few
millis to get 0.2 millis each.

Best regards, Vitalii Tymchyshyn

Сб, 30 січ. 2016 10:57 David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> пише:

> On 31 January 2016 at 01:30, Hedayat Vatankhah <hedayat(dot)fwd(at)gmail(dot)com>
> wrote:
> > Personally, I expect both queries below to perform exactly the same:
> >
> > SELECT
> > t1.id, *
> > FROM
> > t1
> > INNER JOIN
> > t2 ON t1.id = t2.id
> > where t1.id > -9223372036513411363;
> >
> > And:
> >
> > SELECT
> > t1.id, *
> > FROM
> > t1
> > INNER JOIN
> > t2 ON t1.id = t2.id
> > where t1.id > -9223372036513411363 and t2.id > -9223372036513411363;
> >
> > Unfortunately, they do not. PostgreSQL creates different plans for these
> > queries, which results in very poor performance for the first one
> compared
> > to the second (What I'm testing against is a DB with around 350 million
> > rows in t1, and slightly less in t2).
> >
> > EXPLAIN output:
> > First query: http://explain.depesz.com/s/uauk
> > Second query: link: http://explain.depesz.com/s/uQd
>
> Yes, unfortunately you've done about the only thing that you can do,
> and that's just include both conditions in the query. Is there some
> special reason why you can't just write the t2.id > ... condition in
> the query too? or is the query generated dynamically by some software
> that you have no control over?
>
> I'd personally quite like to see improvements in this area, and even
> wrote a patch [1] which fixes this problem too. The problem I had when
> proposing the fix for this was that I was unable to report details
> about how many people are hit by this planner limitation. The patch I
> proposed caused a very small impact on planning time for many queries,
> and was thought by many not to apply in enough cases for it to be
> worth slowing down queries which cannot possibly benefit. Of course I
> agree with this, I've no interest in slowing down planning on queries,
> but at the same time understand the annoying poor optimisation in this
> area.
>
> Although please remember the patch I proposed was merely a first draft
> proposal. Not for production use.
>
> [1]
> http://www.postgresql.org/message-id/flat/CAKJS1f9FK_X_5HKcPcSeimy16Owe3EmPmmGsGWLcKkj_rW9s6A(at)mail(dot)gmail(dot)com#CAKJS1f9FK_X_5HKcPcSeimy16Owe3EmPmmGsGWLcKkj_rW9s6A@mail.gmail.com
>
> --
> David Rowley http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>