Optimizing execution of expensive subqueries

[Mathieu Fenniak]

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