Re: Joining to views & the query planner

Wells Oliver <wells(dot)oliver(at)gmail(dot)com> writes:
> Hi: we do this kind of thing a lot, where we CREATE VIEW v AS SELECT
> a.col1, b.col2 FROM a JOIN b ON a.b_id = b.id -- and then we write
> downstream queries to do something like SELECT * FROM v WHERE col2 = 123.
> Assuming here both a.col1 and a.col2 are indexed using BTREE.

> It seems from my experience that this bogs down, that the query planner
> doesn't necessarily know how to use indexes well from the joined table (b),
> and it ends up scanning a lot more rows than we might think necessary.

A simple test of that pattern gave me a reasonable-looking plan,
so I suspect you are leaving out critical details. How about
a concrete example?

(pgsql-performance would likely be a better venue, btw.)

regards, tom lane