Hi Kynn,
Lets take these up as cases :
Case A: keep one large table T and keep V1 .... V100
Case B: keep one large table T and store the the same data also in T1...T100
Case C: keep T1...T100 and store one V which is a UNION of T1 ... T100
1. The way I look at it, in case B although fetching data instead of evaluating VIEWs would help (when compared to case A), you are missing a small negative fact that your caching mechanism would be severely hit by having to cache two copies of the same data once in T1..T100 and the second time in T.
2. Case C seems to me like a particularly bad idea... and the indexing point that you make, seems all the more complicated... I don't know much about it, so I would try to avoid it.
3. Also, it seems you got the Postgresql VIEW mechanism wrong here. What Dean was trying to say was that PG flattens the VIEW (and its JOINS) directly into a *single* SELECT query *before* it hits even the first record. The per-record redirection is not how it approaches VIEWs which is pretty much why Dean's experience says that relying on the Parser to generate a better SQL (compared to our expertise at optimising it) is not really a bad idea.
4. Personally, Case A is a far far simpler approach to understability (as well as data storage) and if you ask my take ? I'll take Case A :)
Robins Tharakan
