Re: Q on views and performance

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
*