Можно ли как-то оптимизировать этот SQL запрос?

Question

[mr jeery]

Можно ли как-то оптимизировать этот запрос ?

SELECT Prop.*, coalesce("allUnits".count, 0) AS "countAllUnits", coalesce("vacantUnits".count, 0) AS "countVacantUnits"
                    FROM "Property" AS Prop
                    JOIN
                    (
                     SELECT "propertyId", COUNT(*) AS count
                     FROM "Unit"
                     GROUP BY "propertyId"
                    ) AS "allUnits" ON Prop."id" = "allUnits"."propertyId"
                     JOIN
                    (
                     SELECT "propertyId", COUNT(*) AS count
                     FROM "Unit"
                     WHERE "Unit".status = 'Vacant'
                     GROUP BY "propertyId"
                    ) AS "vacantUnits" ON Prop."id" = "vacantUnits"."propertyId"
                    WHERE  Prop."userId" IN (
                          SELECT id FROM "User" WHERE  "companyId" = 200001
                    )

                    ORDER BY Prop."userId" ASC LIMIT 10 OFFSET 0

Вот EXPLAIN ANALYZE

QUERY PLAN
Merge Left Join  (cost=1328267.49..1608392.69 rows=1 width=538) (actual time=8829.697..9516.500 rows=1 loops=1)	
  Merge Cond: (prop.id = "Unit_1"."propertyId")	
  ->  Merge Join  (cost=640598.57..670979.83 rows=1 width=530) (actual time=6533.859..7220.661 rows=1 loops=1)	
        Merge Cond: (prop.id = "Unit"."propertyId")	
        ->  Sort  (cost=24.82..24.83 rows=2 width=522) (actual time=0.405..0.406 rows=2 loops=1)	
              Sort Key: prop.id	
              Sort Method: quicksort  Memory: 27kB	
              ->  Nested Loop  (cost=0.85..24.81 rows=2 width=522) (actual time=0.339..0.380 rows=19 loops=1)	
                    ->  Index Scan using "userCompanyIdIndex" on "User"  (cost=0.42..8.44 rows=1 width=4) (actual time=0.268..0.268 rows=1 loops=1)	
                          Index Cond: ("companyId" = 200001)	
                    ->  Index Scan using "propertyUserIdIndex" on "Property" prop  (cost=0.43..16.34 rows=3 width=522) (actual time=0.068..0.101 rows=19 loops=1)	
                          Index Cond: ("userId" = "User".id)	
        ->  GroupAggregate  (cost=640573.75..661192.81 rows=780974 width=12) (actual time=6492.785..7173.418 rows=837901 loops=1)	
              Group Key: "Unit"."propertyId"	
              ->  Sort  (cost=640573.75..644843.52 rows=1707910 width=4) (actual time=6492.768..6857.919 rows=1682619 loops=1)	
                    Sort Key: "Unit"."propertyId"	
                    Sort Method: external merge  Disk: 23152kB	
                    ->  Bitmap Heap Scan on "Unit"  (cost=31972.73..440420.61 rows=1707910 width=4) (actual time=392.013..5277.053 rows=1682619 loops=1)	
                          Recheck Cond: ((status)::text = 'Vacant'::text)	
                          Rows Removed by Index Recheck: 2854995	
                          Heap Blocks: exact=55840 lossy=329108	
                          ->  Bitmap Index Scan on "unitStatusIndex"  (cost=0.00..31545.76 rows=1707910 width=0) (actual time=369.984..369.984 rows=1682619 loops=1)	
                                Index Cond: ((status)::text = 'Vacant'::text)	
  ->  Finalize GroupAggregate  (cost=687668.91..926713.97 rows=855910 width=12) (actual time=2106.042..2289.771 rows=49830 loops=1)	
        Group Key: "Unit_1"."propertyId"	
        ->  Gather Merge  (cost=687668.91..909595.77 rows=1711820 width=12) (actual time=2106.020..2263.616 rows=126993 loops=1)	
              Workers Planned: 2	
              Workers Launched: 2	
              ->  Partial GroupAggregate  (cost=686668.89..711009.24 rows=855910 width=12) (actual time=2037.828..2112.141 rows=43246 loops=3)	
                    Group Key: "Unit_1"."propertyId"	
                    ->  Sort  (cost=686668.89..691929.31 rows=2104167 width=4) (actual time=2037.818..2076.664 rows=97680 loops=3)	
                          Sort Key: "Unit_1"."propertyId"	
                          Sort Method: external merge  Disk: 22848kB	
                          ->  Parallel Seq Scan on "Unit" "Unit_1"  (cost=0.00..408140.67 rows=2104167 width=4) (actual time=0.029..785.149 rows=1683334 loops=3)	
Planning time: 3.233 ms	
Execution time: 9538.732 ms

Answer 1

[Melkij]

Попробуйте так:

SELECT Prop.*, coalesce("counters".countAllUnits, 0) AS "countAllUnits", coalesce("counters".countVacantUnits, 0) AS "countVacantUnits"
                    FROM "Property" AS Prop
                    INNER JOIN LATERAL
                    (
                     SELECT COUNT(*) AS countAllUnits,
count(*) filter(where "Unit".status = 'Vacant') as countVacantUnits
                     FROM "Unit"
                     WHERE Prop."id" = "Unit"."propertyId"
                    ) AS "counters" ON true
                    WHERE  Prop."userId" IN (
                          SELECT id FROM "User" WHERE  "companyId" = 200001
                    )
                    ORDER BY Prop."userId" ASC LIMIT 10 OFFSET 0

Ожидание: раннее отсечение limit 10 и nested loop именно от них.

Comments

[mr jeery]

@Melkij
Очень здорово, спасибо!
Я нашел решение, отписал ниже, но не такое изящное как у вас

[mr jeery]

Melkij Пошел читать про LATERAL

[Melkij]

идея в том, чтобы выбрать нужные 10 записей и только для них считать count по Unit, а не по всей таблице считать группировку и затем джойнить с нужными 10 записями. До появления lateral join это делалось как у вас в ответе дублированием условия, выносом условия в отдельный cte, или даже функцией. С lateral join немного удобнее это объяснять, в подзапросе в join появляется возможность сделать коррелированный запрос.

Answer 2

[mr jeery]

Нашел еще одно старое решение. Но с помощью LATERAL, как описал Melkij куда лучше.

select pr.*, coalesce("allUnits".count, 0) AS "countAllUnits",  coalesce("vacantUnits".count, 0) AS "countVacantUnits"
from "Property" as pr
left join (
    select "propertyId", COUNT(*) AS count from "Unit" where "propertyId" in (
		select id from "Property" where "userId" in (
			select id from "User" where "companyId" = 200001
		)
	) group by "propertyId"
) as "allUnits" ON pr."id" = "allUnits"."propertyId"
left join (
    select "propertyId", COUNT(*) AS count from "Unit" where "propertyId" in (
		select id from "Property" where "userId" in (
			select id from "User" where "companyId" = 200001
		)
	) group by "propertyId"
) AS "vacantUnits" ON pr."id" = "vacantUnits"."propertyId"

where "userId" in (
	select id from "User" where  "companyId" = 200001
)