Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 265932E011C for ; Sat, 23 Feb 2008 10:35:03 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 82334-07 for ; Sat, 23 Feb 2008 10:34:51 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.158]) by postgresql.org (Postfix) with ESMTP id 915312E0104 for ; Sat, 23 Feb 2008 10:34:59 -0400 (AST) Received: by fg-out-1718.google.com with SMTP id 22so590189fge.23 for ; Sat, 23 Feb 2008 06:34:58 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=bIOiMa+1hnmRPEPcfUy2B0xgCDyc1aCl2SM4havAkjw=; b=h5PBbQ89ClH6MHZVhgb2wM0s5I59DUjbXcbMbwb0Thb06clXN5FDU9XsFy64az6dnITumgMgzaSW1iAm/ShEqatp/ADbz/ZNdQnz/0KGX0STk6EkiRhQ2E0IJ/m/+9g4VVkRDDYo+qJ6cGyLd3QDcrdiCZwGV92Q5+RzqMVD/8c= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=OS7ALx/GK469xhHwSO8dKiYlRUQpuk20bmU9p633WJt8YOolU4ErNku4bV2967qnBefSHRoF0+1qnT8qfP3ZJCNsDH+zwkkTxVhdxbZIGukM0ifcot+gbb1KyGUwWMTpiKmFa9HGX3ColJtJMq+d81FwzKzQ8Yi8ZDgNE94MKPE= Received: by 10.82.136.4 with SMTP id j4mr909311bud.31.1203777297647; Sat, 23 Feb 2008 06:34:57 -0800 (PST) Received: by 10.82.112.18 with HTTP; Sat, 23 Feb 2008 06:34:57 -0800 (PST) Message-ID: <36af4bed0802230634r545dd415sdb0972f5f074bb8c@mail.gmail.com> Date: Sat, 23 Feb 2008 20:04:57 +0530 From: "Robins Tharakan" To: "Kynn Jones" Subject: Re: Q on views and performance Cc: "Dean Gibson (DB Administrator)" , pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6141_3047324.1203777297616" References: <47BF7B80.1040309@ultimeth.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/294 X-Sequence-Number: 28836 ------=_Part_6141_3047324.1203777297616 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 * ------=_Part_6141_3047324.1203777297616 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
 ------=_Part_6141_3047324.1203777297616--