Re: Tricky SELECT question involving subqueries

On Thu, Sep 08, 2005 at 10:02:44AM -0700, Ben Hallert wrote:
> With this in mind, I want to write a query that will list the entries
> in the first table (easy) along with a count() of how many entries in
> the other table start with that path (the hard part).

[...]

> I tried handling this programmaticaly by having a loop that queries
> each path, then does another query below of "SELECT COUNT(*) FROM
> changehistory WHERE UPPER(filespec) LIKE UPPER('$pathspec%')". Each
> count query works fine, but the performance is crippling.

Do you have an expression index on upper(filespec)? That should
speed up queries such as the above. Another possibility might
involve using contrib/ltree. And instead of looping through each
path, you could use an inner or outer join.

CREATE TABLE trackedpaths (pathname ltree);
CREATE TABLE changehistory (filespec ltree);

INSERT INTO trackedpaths (pathname) VALUES ('abc.def');
INSERT INTO trackedpaths (pathname) VALUES ('ghi.jkl');
INSERT INTO trackedpaths (pathname) VALUES ('mno.pqr');

INSERT INTO changehistory (filespec) VALUES ('abc.def.123');
INSERT INTO changehistory (filespec) VALUES ('abc.def.123.456');
INSERT INTO changehistory (filespec) VALUES ('ghi.jkl.789');

SELECT t.pathname, count(c.*)
FROM trackedpaths AS t
LEFT OUTER JOIN changehistory AS c ON c.filespec <@ t.pathname
GROUP BY t.pathname
ORDER BY t.pathname;

pathname | count
----------+-------
abc.def | 2
ghi.jkl | 1
mno.pqr | 0
(3 rows)

--
Michael Fuhr