From: Bruno Wolff III <bruno@wolff.to>
To: Julian Scarfe <julian.scarfe@ntlworld.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Ordering with GROUPs
Date: Sun, 18 Aug 2002 07:45:01 -0500
Message-ID: <20020818124501.GB7589@wolff.to> (raw)
In-Reply-To: <046401c246ad$48dbdc10$0500a8c0@Wilbur>
References: <046401c246ad$48dbdc10$0500a8c0@Wilbur>
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) | 3
BBBB | (1,2) | 2
CCCC | (2,1) | 1
(3 rows)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, julian.scarfe@ntlworld.com
Subject: Re: Ordering with GROUPs
In-Reply-To: <20020818124501.GB7589@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox