Re: Unsolveable query?
От | greg@turnstep.com |
---|---|
Тема | Re: Unsolveable query? |
Дата | |
Msg-id | 99c00a59261d22d3bdb1468915566047@biglumber.com обсуждение исходный текст |
Ответ на | Unsolveable query? (Geert Bevin <gbevin@uwyn.com>) |
Ответы |
Re: Unsolveable query?
(Geert Bevin <gbevin@uwyn.com>)
|
Список | 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-----
В списке pgsql-general по дате отправления: