Re: Why is posgres picking a suboptimal plan for this query?

OK, I committed a fix to Discourse, the suggested pattern by Tom works
like a charm, in my particular user case it cuts a query down from
200-500ms to 8ms.

Thank you heaps

https://github.com/discourse/discourse/commit/29fac1ac18acdc1f0d2c1650d33d2d4a1aab0a0b

On Wed, May 24, 2017 at 6:33 PM, Sam Saffron <sam(dot)saffron(at)gmail(dot)com> wrote:
> Awesome, thanks! I will give that a shot
>
> On Wed, 24 May 2017 at 6:14 pm, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>> Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
>> > On Wed, May 24, 2017 at 1:42 PM, Sam Saffron <sam(dot)saffron(at)gmail(dot)com>
>> > wrote:
>> >> I have this query that is not picking the right index unless I hard
>> >> code
>> >> dates:
>> >> ...
>>
>> > Maybe it should first execute the subquery and then re-plan the rest of
>> > the
>> > query based on the results. But there is no provision for it to do
>> > that,
>> > and no concrete plans (that I know of) to implement such a thing.
>>
>> I don't know of any plans for that, either.
>>
>> >> The results here simply do not make sense to me, should I be piping
>> >> dates in here to avoid this issue and running 2 queries instead of 1?
>>
>> > That is the most pragmatic approach. It isn't very nice, but the
>> > alternatives are worse.
>>
>> You could probably get the behavior you want by replacing the subquery
>> with a "stable" function:
>>
>> create function first_topic_unread_for(userid int) returns timestamp as
>> 'select first_topic_unread_at from user_stats us where us.user_id = $1'
>> language sql stable;
>>
>> SELECT "topics".* FROM "topics"
>> WHERE topics.last_unread_at >= first_topic_unread_for(1);
>>
>> This should convince the planner to pre-run the function to get an
>> estimated result at plan time.
>>
>> regards, tom lane