Awesome, thanks! I will give that a shot
On Wed, 24 May 2017 at 6:14 pm, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Jeff Janes <jeff.janes@xxxxxxxxx> writes:
> On Wed, May 24, 2017 at 1:42 PM, Sam Saffron <sam.saffron@xxxxxxxxx> 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
