Hi Try this way:
SELECT tbl.field1, tbl.field2, tbl.field3, ..., b.Thingy1Sum, ... repeat for multiply thingies ... FROM tbl LATERAL JOIN ( SELECT anothertbl.UserId,SUM(Duration) as Thingy1Sum FROM anothertbl WHERE anothertbl.UserId = tbl.UserId AND anothertbl.ThingyId = 1 group by 1) as b on tbl.UserId=b.UserId ORDER BY tbl.field1 LIMIT 20 El mié., 11 de jul. de 2018 a la(s) 09:25, Mathieu Fenniak ( mathieu.fenn...@replicon.com) escribió: > Hi pgsql-general! > > I'm currently looking at a query that is generally selecting a bunch of > simple columns from a table, and also performing some subqueries to > aggregate related data, and then sorting by one of the simple columns and > paginating the result. > > eg. > > SELECT > tbl.field1, tbl.field2, tbl.field3, ..., > (SELECT SUM(Duration) FROM anothertbl WHERE anothertbl.UserId = tbl.UserId > AND anothertbl.ThingyId = 1) as Thingy1Sum, > ... repeat for multiply thingies ... > FROM > tbl > ORDER BY tbl.field1 LIMIT 20 > > I'm finding that if "tbl" contains hundreds of thousands of rows, the > subqueries are being executed hundreds of thousands of times. Because of > the sorting and pagination, this is appears to be unnecessary, and the > result is slow performance. (PostgreSQL 9.5.9 server) > > I've only found one solution so far, which is to perform the sort & > pagination in a CTE, and the subqueries externally. Are there any other > approaches that can be taken to optimize this and prevent the unnecessary > computation? > > CTE rewrite: > > WITH cte AS ( > SELECT > tbl.field1, tbl.field2, tbl.field3 > FROM > tbl > ORDER BY tbl.field1 LIMIT 20 > ) > SELECT cte.*, > (SELECT SUM(Duration) FROM anothertbl WHERE anothertbl.UserId > = tbl.UserId AND anothertbl.ThingyId = 1) as Thingy1Sum, > ... repeat for multiply thingies ... > FROM cte; > > Thanks for any thoughts you have, > > Mathieu Fenniak > -- Cordialmente, Ing. Hellmuth I. Vargas S.
