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.
--
Wells Oliver
wells.oliver@xxxxxxxxx
wells.oliver@xxxxxxxxx
