Re: Unsolveable query? - Mailing list pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message


> Can anyone solve this query, or do I have to write a count query for
> each linked table (one for labels and one for legends)?

Basically, the latter. Your final query on the page is:

SELECT name, productaspectId,
  (SELECT count(*) from ProductaspectLabel P2
   WHERE P.productaspectId = P2.productaspectId) AS labelcount,
  (SELECT count(*) from ProductaspectLegend P3
   WHERE P.productaspectId = P3.productaspectId) AS legendcount
FROM Productaspect P ORDER BY name ASC;

but you say:

-- This is correct, but I'm fearing about the speed of those sub-selects
-- since I have other structures that have much more tables that are
-- linked for whom the count has to be calculated

I don't think the subselects will perform as bad as you fear, but it
is hard to tell without knowing more about your data. It depends on how
big each table is and on how often they get updated. I suspect the above
query is about as good as you are going to get if the data in those
tables changes rapidly.

However, in addition to making views (which should help), you could store
each count in another table and join off of that:

SELECT productaspectid, count(productaspectid)
  INTO labelcount FROM productaspectlabel GROUP BY 1;
SELECT productaspectid, count(productaspectid)
  INTO legendcount FROM productaspectlegend GROUP BY 1;

Slap on some indexes:

CREATE INDEX labelid  ON labelcount(productaspectid);
CREATE INDEX legendid ON legendcount(productaspectid);

and your query is now:

SELECT p.name, p.productaspectId,
       COALESCE(labelcount.count,0)  AS labelcount,
       COALESCE(legendcount.count,0) AS legendcount
FROM Productaspect p
LEFT OUTER JOIN labelcount  USING (productaspectId)
LEFT OUTER JOIN legendcount USING (productaspectId)
ORDER BY p.name ASC;

We've moved the aggregate functions out of the query and into a table:
the hard part is updating those tables. If this is a data warehouse
and the the data comes in by batch, just rerun the SELECT INTO
statements above, or have a more intelligent trigger that updates
the "count" tables as needed. As I said before, however, if those
tables are changing rapidly or they are fairly small, the original
query above is probably your best bet.

--
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200301161157

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+JuU5vJuQZxSWSsgRAjnBAJ9w8yubyPWMb50/QCANHuf7puuUwgCgv5Hp
OqbHDgKT57jXB6wrHqg3QrA=
=siUq
-----END PGP SIGNATURE-----