pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Joining to views & the query planner 2+ messages / 2 participants [nested] [flat]
* Joining to views & the query planner @ 2022-06-15 18:39 Wells Oliver <wells.oliver@gmail.com> 0 siblings, 1 reply; 2+ messages in thread From: Wells Oliver @ 2022-06-15 18:39 UTC (permalink / raw) To: 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.oliver@gmail.com <wellsoliver@gmail.com> ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Joining to views & the query planner @ 2022-06-15 19:04 Tom Lane <tgl@sss.pgh.pa.us> parent: Wells Oliver <wells.oliver@gmail.com> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2022-06-15 19:04 UTC (permalink / raw) To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-06-15 19:04 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-06-15 18:39 Joining to views & the query planner Wells Oliver <wells.oliver@gmail.com> 2022-06-15 19:04 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox