From: Olgierd Michalak <olgierdm@softcomputer.com>
To: Craig Ringer <ringerc@ringerc.id.au>
CC: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: How to implement Aggregate Awareness?
Thread-Topic: [SQL] How to implement Aggregate Awareness?
Thread-Index: AcybNT+TBQ1xYfVmTHWa+qRSBri6JgAWDGwAAH1YupA=
Date: Mon, 7 Nov 2011 19:25:07 +0000
Message-ID: 
 <F8991549C624E2429C578E86D68A4D027F42AB07@EXCH-MBX01.softcomputer.com>
References: 
 <F8991549C624E2429C578E86D68A4D027F42A9C9@EXCH-MBX01.softcomputer.com>
	<4EB4AEEC.6050408@ringerc.id.au>
In-Reply-To: <4EB4AEEC.6050408@ringerc.id.au>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0

> Simply put, when Transparent (to the reporting tool) Aggregate Navigator
> recognizes that a query would execute faster using aggregates, 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.

Assuming I understand what you want correctly: Before you could usefully=20
do anything like that, I think you'd need to implement full support for=20
materialized views.

Once that's in place, the optimizer could potentially be enhanced to=20
recognise queries against a base table and rewrite it to use a=20
materialized view of the table when it sees that the query only touches=20
data collected by the materialized view.

Right now, there isn't really anything for query rewriting like this to=20
/target/ .

--
Craig Ringer


---------------------------------------------------------------------------=
---

For Aggregate Navigation, materialized views are not required. But, let me =
give you a short example of how it could work. Let us assume we have a fact=
 table SALES with a single measure SALES.AMOUNT, and one dimension table DA=
TE with attributes: DATE.YEAR, DATE.MONTH, DATE.DAY. Assume we also have an=
 aggregate table AGG_SALES that stores aggregated sales amounts at the mont=
h level in the field AGG_SALES.AMOUNT. The corresponding shrunken dimension=
 DATE2MM has a subset of DATE's attributes, that is: DATE2MM.YEAR and DATE2=
MM.MONTH. Now, assume a reporting tool (oblivious of the existence of both =
AGG_SALES and DATE2MM) sends this query to PostgreSQL:

Query 1.
select d.month, sum(s.amount)=20
from sales s, date d=20
where s.date_key =3D d.date_key=20
group by d.month

The Aggregate Navigator recognizes that such query will execute faster if a=
ggregate table is used. So it substitutes the aggregate tables into the ori=
ginal query, so in effect the query is re-written as:

Query 2.
select d.month, sum(s.amount)=20
from agg_sales s, date2mm d=20
where s.date_key =3D d.date_key=20
group by d.month

On the other hand, if the following query arrives:

Query 3.
select d.day, sum(s.amount)=20
from sales s, date d=20
where s.date_key =3D d.date_key=20
group by d.day

The Aggregate Navigator will not be able to find DATE2MM.DAY, so no substit=
ution will take place.

Of course, there needs to be some kind of meta-data defining the relationsh=
ip between AGG_SALES and SALES that should help the Aggregate Navigator qui=
ckly choose the fastest query. In this case, I imagine, the pseudo code cou=
ld look like this.

CREATE RULE "_RETURN"
AS ON SELECT TO SALES DO INSTEAD=20
  { LANGUAGE PSEUDOCODE $$
    IF all the query fields are found in aggregate tables THEN
      Execute the query substituting the aggregate table names into the que=
ry, i.e.: AGG_SALES for SALES
    ELSE
      Execute the query unchanged, i.e.: fall back on the detail tables whe=
re all the fields are available.
  $$ }=20

Transparent Aggregate Navigation is simple in theory, but can it be impleme=
nted in PostgreSQL?=20

Thank you all for all your comments and suggestions,

---
Olgierd Michalak
Soft Computer Consultants, Inc.