From: Robins Tharakan <tharakan@gmail.com>
To: Kynn Jones <kynnjo@gmail.com>
Cc: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Q on views and performance
Date: Sat, 23 Feb 2008 20:04:57 +0530
Message-ID: <36af4bed0802230634r545dd415sdb0972f5f074bb8c@mail.gmail.com> (raw)
In-Reply-To: <c2350ba40802230507i1ac6b9bexccf58f46ad685c1f@mail.gmail.com>
References: <c2350ba40802221249h37913d0cyaa6387a93c55fe69@mail.gmail.com>
<47BF7B80.1040309@ultimeth.com>
<c2350ba40802230507i1ac6b9bexccf58f46ad685c1f@mail.gmail.com>
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
*
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tharakan@gmail.com, kynnjo@gmail.com, postgresql@ultimeth.com
Subject: Re: Q on views and performance
In-Reply-To: <36af4bed0802230634r545dd415sdb0972f5f074bb8c@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox