pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcounting related rows
4+ messages / 2 participants
[nested] [flat]
* counting related rows
@ 2010-10-08 22:02 James Cloos <cloos@jhcloos.com>
2010-10-08 23:09 ` Re: counting related rows Frank Bax <fbax@sympatico.ca>
0 siblings, 1 reply; 4+ messages in thread
From: James Cloos @ 2010-10-08 22:02 UTC (permalink / raw)
To: pgsql-sql
I have a table which includes a text column containing posix-style
paths. Ie, matching the regexp "^[^/]+(/[^/]+)*$".
I need to do a query of a number of columns from that table, plus the
count of rows which are "children" of the current row.
The query:
SELECT count(*) AS nch FROM m WHERE o = (SELECT o FROM m WHERE id=30016)
AND name ILIKE (SELECT name || '/%' FROM m WHERE id=30016);
selects that extra column given the id.
A view containing all of the columns from m plus a column matching the
above select would cover my needs well.
But I haven't been able to get the syntax right.
The current code (which I did not write) uses one select to get the
values of id it wants, and then iterates through them selecting four
columns from the table and then the above. As you may imagine, two
selects per row is *slow*. Maybe 20 rows per second. I expect a
single, complete select to take < 10 ms.
Again, to be clear, for each row I need the count of other rows which
have the same value for column o and whose name is a child path of the
current row's name.
-JimC
--
James Cloos <cloos@jhcloos.com> OpenPGP: 1024D/ED7DAEA6
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: counting related rows
2010-10-08 22:02 counting related rows James Cloos <cloos@jhcloos.com>
@ 2010-10-08 23:09 ` Frank Bax <fbax@sympatico.ca>
2010-10-09 07:49 ` Re: counting related rows James Cloos <cloos@jhcloos.com>
0 siblings, 1 reply; 4+ messages in thread
From: Frank Bax @ 2010-10-08 23:09 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
James Cloos wrote:
> I have a table which includes a text column containing posix-style
> paths. Ie, matching the regexp "^[^/]+(/[^/]+)*$".
>
> I need to do a query of a number of columns from that table, plus the
> count of rows which are "children" of the current row.
>
> The query:
>
> SELECT count(*) AS nch FROM m WHERE o = (SELECT o FROM m WHERE id=30016)
> AND name ILIKE (SELECT name || '/%' FROM m WHERE id=30016);
>
> selects that extra column given the id.
>
> A view containing all of the columns from m plus a column matching the
> above select would cover my needs well.
>
> But I haven't been able to get the syntax right.
It would help if you provided:
a) statements to create sample data
b) expected results from sample data
Does this do what you want?
select * from m, (SELECT count(*) AS nch FROM m WHERE o = (SELECT o FROM
m WHERE id=30016) AND name ILIKE (SELECT name || '/%' FROM m WHERE
id=30016)) om;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: counting related rows
2010-10-08 22:02 counting related rows James Cloos <cloos@jhcloos.com>
2010-10-08 23:09 ` Re: counting related rows Frank Bax <fbax@sympatico.ca>
@ 2010-10-09 07:49 ` James Cloos <cloos@jhcloos.com>
2010-10-15 09:57 ` Re: counting related rows James Cloos <cloos@jhcloos.com>
0 siblings, 1 reply; 4+ messages in thread
From: James Cloos @ 2010-10-09 07:49 UTC (permalink / raw)
To: pgsql-sql; +Cc: Frank Bax <fbax@sympatico.ca>
>>>>> "FB" == Frank Bax <fbax@sympatico.ca> writes:
FB> It would help if you provided:
FB> a) statements to create sample data
FB> b) expected results from sample data
FB> Does this do what you want?
FB> select * from m, (SELECT count(*) AS nch FROM m WHERE o = (SELECT o
FB> FROM m WHERE id=30016) AND name ILIKE (SELECT name || '/%' FROM m
FB> WHERE id=30016)) om;
That is almost right, except that it uses id=30016's nch value for every
row in the result, rather than computing each row's own nch.
As an example:
create TABLE m ( id integer primary key, o integer, name text,
f1 integer, f2 integer, f3 integer);
insert into m values (1, 3, 'a', 0, 1, 1);
insert into m values (2, 3, 'a/short', 1, 0, 1);
insert into m values (3, 3, 'a/short/path', 1, 0, 0);
insert into m values (4, 4, 'nothing', 0, 0, 1);
insert into m values (5, 2, 'nothing', 0, 1, 0);
insert into m values (6, 2, 'nothing/of', 1, 0, 0);
insert into m values (7, 2, 'nothing/of/value', 0, 0, 0);
The select should result in something like:
id | o | name | f1 | f2 | f3 | nch
----+---+------------------+----+----+----+-----
1 | 3 | a | 0 | 1 | 1 | 2
2 | 3 | a/short | 1 | 0 | 1 | 1
3 | 3 | a/short/path | 1 | 0 | 0 | 0
4 | 4 | nothing | 0 | 0 | 1 | 0
5 | 2 | nothing | 0 | 1 | 0 | 2
6 | 2 | nothing/of | 1 | 0 | 0 | 1
7 | 2 | nothing/of/value | 0 | 0 | 0 | 0
since rows 2 and 3 are children of row 1, row 3 is also a child of
row 2, rows 6 and 7 are children of row 5 and row 7 is also a child
of row 6.
-JimC
--
James Cloos <cloos@jhcloos.com> OpenPGP: 1024D/ED7DAEA6
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: counting related rows
2010-10-08 22:02 counting related rows James Cloos <cloos@jhcloos.com>
2010-10-08 23:09 ` Re: counting related rows Frank Bax <fbax@sympatico.ca>
2010-10-09 07:49 ` Re: counting related rows James Cloos <cloos@jhcloos.com>
@ 2010-10-15 09:57 ` James Cloos <cloos@jhcloos.com>
0 siblings, 0 replies; 4+ messages in thread
From: James Cloos @ 2010-10-15 09:57 UTC (permalink / raw)
To: pgsql-sql
After further though, I tried using a function:
CREATE OR REPLACE FUNCTION children ( ow integer, parent text) returns integer AS $$
select count(*) as children from m where o = $1 and name ilike $2 || '/%';
$$
LANGUAGE sql;
An example call is:
select o, name, f1, f2, (select children(o,name) as children) from m where o=3;
Which worked, but was no faster than selecting all of the matching ids
and iterating through them on the client finding each row's parent count
each in its own select. Ie, it took about 1 ks for about 20k rows.
So it looks like the real solution is to add a column to track the
number of children and update it, for each "parent" row via a trigger
whenever a row is added, removed or the path column of a row is changed.
-JimC
--
James Cloos <cloos@jhcloos.com> OpenPGP: 1024D/ED7DAEA6
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-10-15 09:57 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-08 22:02 counting related rows James Cloos <cloos@jhcloos.com>
2010-10-08 23:09 ` Frank Bax <fbax@sympatico.ca>
2010-10-09 07:49 ` James Cloos <cloos@jhcloos.com>
2010-10-15 09:57 ` James Cloos <cloos@jhcloos.com>
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