So I ended up with the CTE as one of my optimization attempts. I started off with a regular sub-select so that's not it. However I have stumbled onto something - deleting the index (on last_seen) actually makes it faster!!! Somehow the combination of the index and query in the view was tripping up the query planner into selecting a bad plan? Is that possible?
Max Kremer <mkremer@trialfire.com> writes: > I've encountered a very strange result when using a view in a query VS the > underlying sql of the view. When accessing the view I get a different query > plan than running the sql that makes up the view. Using the view is 10x > slower and generates a strange query plan that seems to ignore > the existence of an index.
I believe the problem is that the WITH attached to the view's SELECT prevents the view sub-select from being flattened into the outer query (ie, this is another way in which WITH acts as an optimization fence). So the sub-select is planned without awareness of the ORDER BY/LIMIT that would encourage picking a suitably ordered join plan.
Try writing the CTE as a plain sub-select, instead.
(AFAIR, this behavior just stems from a lack of round tuits and not any fundamental difficulty: the WITHs could perfectly well be hoisted up to the outer query. But don't hold your breath waiting for that to happen.)