sql: propagate filters to virtual table generators

[knz]

The overhead of producing the vtable data is already showing up in performance reports after the set of descriptor grows sufficiently large (#20753). Also ORMs like to use complex joins with virtual tables, and virtual tables can contain lots of data, we'd like to limit the amount of data generated in-memory if the outer query applies filters or a join condition.

A cache was mentioned previously (#12025) but it does not reduce the amount of data pulled into e.g. a complex join without filtering. Also the cache invalidation conditions for virtual tables is likely very complex (each vtable's contents reflects a different part of the state of the db).

A proper solution would be to have the vtable generator function take a filter as parameter, and only produce data for those that match the filter.

Also most vtables have a natural primary key; the generator function can take the struct that represents column constraints and use that to constrain the generation loops.

cc @petermattis

[nvanbenschoten]

The overhead of producing the vtable data is already showing up in performance reports after the set of descriptor grows sufficiently large (#20753).

I'm on-board with this proposal, but we should note that the overhead of vtable queries explored in #20753 was completely dominated by the repeated descriptor lookups. Now that we cache these, we should re-examine whether this is still an issue before investing significant effort.

[RoachietheSupportRoach]

Zendesk ticket #4349 has been linked to this issue.

[jordanlewis]

I forgot to close this, it got fixed by #47316.