On Sun, Aug 18, 2002 at 12:49:18 +0100, Julian Scarfe <julian.scarfe@ntlworld.com> wrote:
> I'm struggling to find an appropriate efficient query for an aggregate-type
> problem and I'd appreciate suggestions.
>
> I have messages associated with a set of locations (zero or more messages
> per location). A cut down test is as follows:
>
> CREATE TABLE location (ident char(4), node point);
> INSERT INTO location values ('AAAA', '(1,1)');
> INSERT INTO location values ('BBBB', '(1,2)');
> INSERT INTO location values ('CCCC', '(2,1)');
> INSERT INTO location values ('DDDD', '(2,2)');
>
> CREATE TABLE message (ident char(4), message_text text);
> INSERT INTO message values ('AAAA', 'foo');
> INSERT INTO message values ('AAAA', 'bar');
> INSERT INTO message values ('AAAA', 'baz');
> INSERT INTO message values ('BBBB', 'abel');
> INSERT INTO message values ('BBBB', 'baker');
> INSERT INTO message values ('CCCC', 'charlie');
>
> I can't find anything in the doc.
>
> One alternative is to use a nested query:
>
Instead of a nested query, how about doing a join to a group'd version
of message? While you didn't use a unique constraint on the location
table it seems likely that there should only be one location for each
ident. So you can do the join after counting the number of messages.
While this is similar to your example, it might run faster, especially
in you don't have the message table indexed by ident, so that you can
do one sort instead of a bunch of sequential scans.
For example:
area=> select location.ident, location.node, howmany from location
area-> natural join (select ident, count(*) as howmany from message
area(> group by ident) as messgroup;ident | node | howmany
-------+-------+---------AAAA | (1,1) | 3BBBB | (1,2) | 2CCCC | (2,1) | 1
(3 rows)