I have a large set of interwoven views in Postgres that sometimes are make inefficient use of other views.
CREATE VIEW C AS SELECT A.a, B.b FROM A LEFT JOIN B on A.id = B.id
SELECT C.a, COUNT(*) FROM C GROUP BY C.a
It’s more complicated, but the idea is that the second query doesn’t make use of the JOIN from the VIEW. In the EXPLAIN, this is not optimized away by Postgres.
I’m looking for an automated way to “expand” the second query to something like,
SELECT C.a, COUNT(*) FROM ( SELECT A.a, B.b FROM A LEFT JOIN B on A.id = B.id ) C GROUP BY C.a
So I could then easily see the optimization and make the change my hand.