OMOP: views wont scale for large data

The views utilize casts of the concept to varchar ... this will in turn cause a full table scan – the optimizer can’t take advantage of any indexes that may already exist on the underlying table because it has to do a real time conversion which disrupts the order (1, 2, 5, 10) goes to (1, 10, 2, 5). As a result, you’re going to have issues running this on anything sizable because the queries will perform as if unindexed. One option would be to implement indexed or materialized views which get tied to the underlying schema – these write the data to disk (essentially creating a mirror of the data in its transformed state). The trade off, of course, would be that you would need to twice the space to house these.