Craig Ringer <cr...@2ndquadrant.com> writes: >> Showing that I'm still very much learning this area myself, a bit more >> looking around found: >> http://www.postgresql.org/docs/current/static/querytree.html >> which makes it clear that the range table for the query will contain >> what you want. I suspect you'll need to find CTEs and subqueries and >> extract the relations they touch; I haven't yet checked to see whether >> they're aggregated into the top level range table, but suspect not.
> Of course, having said that I was then curious enough to go digging. > It's trivial to dump the parse tree or query plan tree, as documented in > the above linked documentation, and doing so clearly shows that > subqueries and CTEs have their own range tables. > So you'll have to walk the node tree looking for range tables and check > to see whether any of the range table entries match one of the tables > you're looking for. > It's possible that this is a completely backwards approach; if so, > hopefully one of the more experienced people here will correct me with > the simple and obvious way to do it. That's correct if you're looking at the parser-output representation. However, a plan has a "flat" rangetable, so if you're looking at a plan rather than a raw Query it's much easier. I'm too lazy to go check right now, but I think in auto_explain it would be at least as easy to look at the plan. There are some definitional issues that might be worth considering: 1. should a view used in a query be a match? (probably) 2. how about a table that's accessed via a view? (almost certainly) 3. should an inheritance child table that's excluded by constraint exclusion, and thus not actually scanned, be a match? (debatable) If you believe you want #2 then you don't want to look at raw Query trees, because view expansion hasn't been done so you won't see anything hidden under a view. If you just blindly scan the flat rangetable of a plan then you will get hits for all three cases. The only way to exclude #3 would be to scan the plan tree and identify which rangetable entries are actually scanned by scan nodes. However, if you filter in that way then you'll also exclude #1 (since a view isn't referenced in the plan tree). You could have #1 and not #3 if you used some complicated ie fragile hybrid method, but I'm doubting it's worth it. regards, tom lane -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers