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.saffron@xxxxxxxxx> wrote: > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
