Postgres Pro
Downloads
Home   >   mailing lists

Thread: Joining to views & the query planner

Joining to views & the query planner

From
Wells Oliver
Date:
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.

I know this is vague and without explicit technical detail, but it's a pattern we use a lot, and I am wondering if I am missing something as to how this kind of approach can be optimized, or wether it's just a bad idea generally.

--

Re: Joining to views & the query planner

From
Tom Lane
Date:
Wells Oliver <wells.oliver@gmail.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