Thread: Re: group by points

Jaime Casanova wrote:
> suppose we have something like this:
>
> upd_views=# create table tabla1 (col1 point);

[...]

> then, this select will give an error:
>
> upd_views=# select col1, count(*) from tabla1 group by col1;
> ERROR:  could not identify an ordering operator for type point
> HINT:  Use an explicit ordering operator or modify the query.
> upd_views=#
>
> i guess this is related to:
> http://archives.postgresql.org/pgsql-hackers/2003-08/msg00809.php
>
> so, what happened with this idea? there is another way to automagicaly
> identify an "equality operator" for datatypes like 'point'?

I don't think that there is a connection to the message you quote.

In order to (efficiently) process a GROUP BY clause, you need a
total ordering on the data type that you group by, i.e. an ordering
such that for any two data x and y you have either x < y or x > x
or x = y.

There is no reasonable total ordering on the real plain, so you will
have to roll your own. How about:

select point(col1[0], col1[1]), count(*) from tabla1 group by col1[0],
col1[1];

Yours,
Laurenz Albe

On Mon, May 22, 2006 at 10:23:28 +0200, Albe Laurenz <all@adv.magwien.gv.at> wrote:
> 
> In order to (efficiently) process a GROUP BY clause, you need a
> total ordering on the data type that you group by, i.e. an ordering
> such that for any two data x and y you have either x < y or x > x
> or x = y.

An equality operator is good enough if the number of unique groups isn't too
large, so that a hash aggregate plan works efficiently.

Bruno Wolff III <bruno@wolff.to> writes:
> On Mon, May 22, 2006 at 10:23:28 +0200,
>   Albe Laurenz <all@adv.magwien.gv.at> wrote:
>> In order to (efficiently) process a GROUP BY clause, you need a
>> total ordering on the data type that you group by, i.e. an ordering
>> such that for any two data x and y you have either x < y or x > x
>> or x = y.

> An equality operator is good enough if the number of unique groups isn't too
> large, so that a hash aggregate plan works efficiently.

Doesn't help for the case at hand, since point_eq isn't marked hashable
either.  It would be good to fix things so that the system doesn't
insist on having the sorting option available, though.
        regards, tom lane

On Mon, May 22, 2006 at 18:38:35 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
> > On Mon, May 22, 2006 at 10:23:28 +0200,
> >   Albe Laurenz <all@adv.magwien.gv.at> wrote:
> >> In order to (efficiently) process a GROUP BY clause, you need a
> >> total ordering on the data type that you group by, i.e. an ordering
> >> such that for any two data x and y you have either x < y or x > x
> >> or x = y.
> 
> > An equality operator is good enough if the number of unique groups isn't too
> > large, so that a hash aggregate plan works efficiently.
> 
> Doesn't help for the case at hand, since point_eq isn't marked hashable
> either.  It would be good to fix things so that the system doesn't
> insist on having the sorting option available, though.

Yeah, I thought about that later on my way home. You need to have a hash
function that maps equal values to the same hash bucket or things don't
work.