@jcrist I can't remember, are we not already doing this?

We are not. t.cache(); t.cache() won't cache a table twice, but it's not a no-op if a table is already a physical table.

This is easy to do if we assume that PhysicalTable ops don't need to be cached (we might also expand this to simple column subselection on these tables like t.select("a", "b", "c") which should be cheap), while all other expressions get cached.

However, this assumption isn't true, since some backends (e.g. duckdb) return views for read_csv/read_parquet, which won't be as cheap to access for repeat queries as a temporary table produced by .cache(). Since these views are also mapped to DatabaseTable, it's not easy to determine whether a PhysicalTable is really "physical" or not. In #9931 I note that I (and some user's code like @lostmygithubaccount I've seen) have sometimes used read_csv(...).cache() to create a temp table in duckdb since by default read_csv(...) just creates a view. It'd be nice to keep that workflow working while reducing overhead for cases where it's not needed.

A few options:

• Add a new IR node for views (possibly a subclass of DatabaseTable) and use those instead for those apis. This would make determining whether an op is worth caching easier.
• Query inside _load_into_cache more info about a backing table to determine if it's cheap or not, and if it is take a fast path to avoid the temp-table creation.

Neither seems terribly onerous (mostly just plumbing), but the latter is much less invasive.

I'm going to try implementing the latter (probably for a small subset of backends to start) and see how it looks. I think this should be doable without being too invasive.