From: | Wells Oliver <wells(dot)oliver(at)gmail(dot)com> |
---|---|
To: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Joining to views & the query planner |
Date: | 2022-06-15 18:39:26 |
Message-ID: | CAOC+FBX1rXLZi9o+TjJWGfYWzZmAg6s2GUTUtqhDKA6cQtzaYA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-06-15 19:04:34 | Re: Joining to views & the query planner |
Previous Message | Peter Geoghegan | 2022-06-14 20:06:35 | Re: Xmax precedes relation freeze threshold errors |