DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=lbrxA6rgQvbkqmIm7Z1/bfpb0VcYXtL1l4I3TRXi1rVpcKQTsPSR3eatt67R7ZfNMW
	CepnnUSM//EEIpLWc+5R937i1sYYIaqH6ejYM4nBZ2pG4jhcL35s161xek09Y+durKE5
	m+rSk9b2Jn2fl27bmAh54DG+teQU/EtShhibs=
MIME-Version: 1.0
In-Reply-To: <ca24673e1002241331l4099482aqc73649d93611767a@mail.gmail.com>
References: <ca24673e1002241331l4099482aqc73649d93611767a@mail.gmail.com>
Date: Tue, 9 Mar 2010 07:46:27 -0500
Message-ID: <b42b73151003090446w78bc8222hf49cc4e7e7b2e3ba@mail.gmail.com>
Subject: Re: Extracting superlatives - SQL design philosophy
From: Merlin Moncure <mmoncure@gmail.com>
To: Dave Crooke <dcrooke@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Wed, Feb 24, 2010 at 4:31 PM, Dave Crooke <dcrooke@gmail.com> wrote:
> This is a generic SQL issue and not PG specific, but I'd like to get
> an opinion from this list.
>
> Consider the following data:
>
> # \d bar
> =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Table "public.bar"
> =A0Column |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Type=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0 | Modifiers
> --------+-----------------------------+-----------
> =A0city=A0=A0 | character varying(255)=A0=A0=A0=A0=A0 |
> =A0temp=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0 |
> =A0date=A0=A0 | timestamp without time zone |
>
> # select * from bar order by city, date;
> =A0 city =A0 =A0| temp | =A0 =A0 =A0 =A0date
> -----------+------+---------------------
> =A0Austin =A0 =A0| =A0 75 | 2010-02-21 15:00:00
> =A0Austin =A0 =A0| =A0 35 | 2010-02-23 15:00:00
> =A0Edinburgh | =A0 42 | 2010-02-23 15:00:00
> =A0New York =A0| =A0 56 | 2010-02-23 15:00:00
> =A0New York =A0| =A0 78 | 2010-06-23 15:00:00
> (5 rows)
>
> If you want the highest recorded temperature for a city, that's easy
> to do, since the selection criteria works on the same column that we
> are extracing:
>
> # select city, max(temp) from bar group by city order by 1;
> =A0 city =A0 =A0| max
> -----------+-----
> =A0Austin =A0 =A0| =A075
> =A0Edinburgh | =A042
> =A0New York =A0| =A078
> (3 rows)
>
>
> However there is (AFAIK) no simple way in plain SQL to write a query
> that performs such an aggregation where the aggregation criteria is on
> one column and you want to return another, e.g. adding the the *date
> of* that highest temperature to the output above, or doing a query to
> get the most recent temperature reading for each city.
>
> What I'd like to do is something like the below (and I'm inventing
> mock syntax here, the following is not valid SQL):
>
> -- Ugly implicit syntax but no worse than an Oracle outer join ;-)
> select city, temp, date from bar where date=3Dmax(date) group by city,
> temp order by city;
>
> or perhaps
>
> -- More explicit
> select aggregate_using(max(date), city, temp, date) from bar group by
> city, temp order by city;
>
> Both of the above, if they existed, would be a single data access
> followed by and sort-merge.
>
> The only way I know how to do it involves doing two accesses to the data,=
 e.g.
>
> # select city, temp, date from bar a where date=3D(select max(b.date)
> from bar b where a.city=3Db.city) order by 1;
> =A0 city =A0 =A0| temp | =A0 =A0 =A0 =A0date
> -----------+------+---------------------
> =A0Austin =A0 =A0| =A0 35 | 2010-02-23 15:00:00
> =A0Edinburgh | =A0 42 | 2010-02-23 15:00:00
> =A0New York =A0| =A0 78 | 2010-06-23 15:00:00
> (3 rows)
>
>
> # explain select * from bar a where date=3D(select max(b.date) from bar
> b where a.city=3Db.city) order by 1;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0QUERY PLAN
> -------------------------------------------------------------------------=
-
> =A0Sort =A0(cost=3D1658.86..1658.87 rows=3D1 width=3D528)
> =A0 Sort Key: a.city
> =A0 -> =A0Seq Scan on bar a =A0(cost=3D0.00..1658.85 rows=3D1 width=3D528=
)
> =A0 =A0 =A0 =A0 Filter: (date =3D (subplan))
> =A0 =A0 =A0 =A0 SubPlan
> =A0 =A0 =A0 =A0 =A0 -> =A0Aggregate =A0(cost=3D11.76..11.77 rows=3D1 widt=
h=3D8)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Seq Scan on bar b =A0(cost=3D0.00..=
11.75 rows=3D1
> width=3D8) =A0 =A0 -- would be an index lookup in a real scenario
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Filter: (($0)::text =3D (city=
)::text)

Another cool way to do this is via a custom aggregate:
create function maxdata(data, data) returns data as
$$
  select case when ($1).date > ($2).date then $1 else $2 end;
$$ language sql;

create aggregate maxdata(data)
(
   sfunc=3Dmaxdata,
   stype=3Ddata
);

select (d).* from
(
  select maxdata(data) as d from data group by city
);

It does it in a single pass.  Where this approach can pay dividends is
when you have a very complicated 'max'-ing criteria to justify the
verbosity of creating the aggregate.  If you are not doing the whole
table, the self join is often faster.  I'm surprised custom aggregates
aren't used more...they seem very clean and neat to me.

merlin