Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 6B48EB5DC46 for ; Fri, 4 Nov 2011 17:15:03 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 61699-10 for ; Fri, 4 Nov 2011 20:14:56 +0000 (UTC) X-Greylist: delayed 00:05:49.013058 by SQLgrey-1.8.0-rc2 Received: from mx1.softcomputer.com (mx1.softcomputer.com [208.86.238.22]) by mail.postgresql.org (Postfix) with ESMTP id E30EEB5DC44 for ; Fri, 4 Nov 2011 17:14:55 -0300 (ADT) Received: from EXCH-CAS01.softcomputer.com (exch-cas01.softcomputer.com [10.100.65.131]) by mx1.softcomputer.com (8.13.8/8.13.8) with ESMTP id pA4K94gL020093 (version=TLSv1/SSLv3 cipher=AES128-SHA bits=128 verify=OK) for ; Fri, 4 Nov 2011 16:09:04 -0400 Received: from EXCH-MBX01.softcomputer.com ([fe80::19c:278c:af93:ab44]) by EXCH-CAS01.softcomputer.com ([::1]) with mapi id 14.01.0339.001; Fri, 4 Nov 2011 16:09:04 -0400 From: Olgierd Michalak To: "pgsql-sql@postgresql.org" Subject: How to implement Aggregate Awareness? Thread-Topic: How to implement Aggregate Awareness? Thread-Index: AcybLZeK6PUDU0jAQjSCN7FooIy9tQ== Date: Fri, 4 Nov 2011 20:09:03 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-cr-hashedpuzzle: J/I= A5Qa Bmvm CHkU CTLS CUV6 DaH8 E5F9 FOEx FV42 FiyT Gb0Y Hb46 IWhP J5pd KbM1; 1; cABnAHMAcQBsAC0AcwBxAGwAQABwAG8AcwB0AGcAcgBlAHMAcQBsAC4AbwByAGcA; Sosha1_v1; 7; {1DACAC8B-EA50-43F0-B7C9-9CC6EAFEE1BE}; bwBsAGcAaQBlAHIAZABtAEAAcwBvAGYAdABjAG8AbQBwAHUAdABlAHIALgBjAG8AbQA=; Fri, 04 Nov 2011 20:09:00 GMT; SABvAHcAIAB0AG8AIABpAG0AcABsAGUAbQBlAG4AdAAgAEEAZwBnAHIAZQBnAGEAdABlACAAQQB3AGEAcgBlAG4AZQBzAHMAPwA= x-cr-puzzleid: {1DACAC8B-EA50-43F0-B7C9-9CC6EAFEE1BE} x-originating-ip: [172.27.179.34] Content-Type: multipart/alternative; boundary="_000_F8991549C624E2429C578E86D68A4D027F42A970EXCHMBX01softco_" MIME-Version: 1.0 X-Greylist: Sender succeeded STARTTLS authentication, not delayed by milter-greylist-4.2.7 (mx1.softcomputer.com [208.86.238.26]); Fri, 04 Nov 2011 16:09:04 -0400 (EDT) X-Scanned-By: MIMEDefang 2.72 on 208.86.238.26 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-3.1 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RP_MATCHES_RCVD=-1.201 X-Spam-Level: X-Archive-Number: 201111/51 X-Sequence-Number: 36024 --_000_F8991549C624E2429C578E86D68A4D027F42A970EXCHMBX01softco_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable I wonder how to implement Aggregate Awareness or Transparent Aggregate Navi= gation in PostgreSQL? Simply put, when Transparent (to the reporting tool) Aggregate Navigator re= cognizes that a query would execute faster using aggregates, it automatical= ly rewrites the query so that the database hits the smaller aggregates rath= er than larger detail tables upon which the small aggregates are pre-comput= ed. The simple theory for BI/DWH was set out by Ralph Kimball (http://www.k= imballgroup.com/html/articles_search/articles1996/9608d54.html). Some tools= , including Oracle, provide such functionality, and I wonder if it is possi= ble to implement such Aggregate Awareness in PostgreSQL. Re-write rules in PostgreSQL come to mind first. After a few attempts, I re= alized that they may not be sophisticated enough to provide a viable soluti= on. But perhaps they are, or there are other features in PostgreSQL that wo= uld allow for such aggregate awareness. If you have good ideas about how to= implement this functionality, please respond. Thank you, Olgierd Michalak Soft Computer Consultants, Inc. --_000_F8991549C624E2429C578E86D68A4D027F42A970EXCHMBX01softco_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I wonder how to implement Aggregate Awareness or Tra= nsparent Aggregate Navigation in PostgreSQL?

 

Simply put, when Transparent (to the reporting tool)= Aggregate Navigator recognizes that a query would execute faster using agg= regates, it automatically rewrites the query so that the database hits the = smaller aggregates rather than larger detail tables upon which the small aggregates are pre-computed. The simple= theory for BI/DWH was set out by Ralph Kimball (http://www.kim= ballgroup.com/html/articles_search/articles1996/9608d54.html). Some tools, including Oracle, provide such functionality, and I wonder if = it is possible to implement such Aggregate Awareness in PostgreSQL.

 

Re-write rules in PostgreSQL come to mind first. Aft= er a few attempts, I realized that they may not be sophisticated enough to = provide a viable solution. But perhaps they are, or there are other feature= s in PostgreSQL that would allow for such aggregate awareness. If you have good ideas about how to implement th= is functionality, please respond.

 

 

Thank you,

Olgierd Michalak

Soft Computer Consultants, Inc.

 

--_000_F8991549C624E2429C578E86D68A4D027F42A970EXCHMBX01softco_--