Re: Planner is ignoring index when view is queried but uses index when running underlying view's sql

Thanks Tom.
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?
ᐧ

Regards,

Max Kremer

http://www.trialfire.com
email: max(at)trialfire(dot)com
twitter: @maxtrialfire
skype: maxkremer

On Tue, Jun 7, 2016 at 5:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Max Kremer <mkremer(at)trialfire(dot)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.)
>
> (BTW, what PG version is that?)
>
> regards, tom lane
>