On Tue, May 24, 2016 at 11:19:04AM -0400, Tom Lane wrote:
> 8.4 avoids this trap only because it doesn't consider injecting a
> materialize there.
>
> So a brute-force fix to restore the pre-9.0 behavior would be
> "set enable_material = off". But really the problem is that it's
> unobvious that all rows in the gs2 output would have matches in gs.
> I wonder if there's a way to reformulate the query to make that better.
> Do you even need the update_time check?
Thank you for the quick reply. Turning off enable_material does indeed
fix the problem on production, so it is good to know we have that
in our toolbox now. The update_time check is pretty buried in a view
inside a much more complicated query, and is needed for some other parts
of the query I left out to keep it simple.
> Assuming that the application's already been optimized for pre-9.0
> Postgres, turning off enable_material might not be a disastrous long
> term solution for it, though certainly it'd be better if you can move
> away from that eventually.
Yes, we are evaluating whether we should set this, or rewrite some queries
and see if this issue pops up anywhere else. Leaning towards the latter,
as I'm sure the planner *usually* does better with all options enabled. :)
--
Greg Sabino Mullane greg@endpoint.com
End Point Corporation
PGP Key: 0x14964AC8
