Hello,
I've noticed that we our queries have very bad estimates, which leads to
the planner using slow nested loops, here is a subset of the query without
tenant separation (correct estimates):
explain (ANALYZE, COSTS, BUFFERS, FORMAT text)
select *
from "Reservation"."Reservation" r
join "Rates"."TimeSliceDefinition" t on r."TimeSliceDefinitionId" =
t."Id"
join "Reservation"."NoShowFee" f on r."NoShowFeeId" = f."Id"
where r."DepartureUtc" > '2018-01-01' and r."ArrivalUtc" < '2018-09-30'
Gather (cost=14034.74..22788.40 rows=12346 width=793) (actual
time=23.815..57.178 rows=12263 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=18997
-> Hash Join (cost=13034.74..20553.80 rows=5144 width=793) (actual
time=20.869..49.029 rows=4088 loops=3)
Hash Cond: (r."TimeSliceDefinitionId" = t."Id")
Buffers: shared hit=18997
-> Parallel Hash Join (cost=12907.55..20413.09 rows=5144
width=662) (actual time=19.210..45.177 rows=4088 loops=3)
Hash Cond: (f."Id" = r."NoShowFeeId")
Buffers: shared hit=18683
-> Parallel Seq Scan on "NoShowFee" f (cost=0.00..7343.25
rows=61825 width=143) (actual time=0.006..15.481 rows=49460 loops=3)
Buffers: shared hit=6725
-> Parallel Hash (cost=12843.25..12843.25 rows=5144
width=519) (actual time=19.071..19.072 rows=4088 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 4832kB
Buffers: shared hit=11958
-> Parallel Seq Scan on "Reservation" r
(cost=0.00..12843.25 rows=5144 width=519) (actual time=0.971..14.919
rows=4088 loops=3)
Filter: (("DepartureUtc" > '2018-01-01
00:00:00'::timestamp without time zone) AND ("ArrivalUtc" < '2018-09-30
00:00:00'::timestamp without time zone))
Rows Removed by Filter: 43126
Buffers: shared hit=11958
-> Hash (cost=96.53..96.53 rows=2453 width=131) (actual
time=1.586..1.586 rows=2453 loops=3)
Buckets: 4096 Batches: 1 Memory Usage: 457kB
Buffers: shared hit=216
-> Seq Scan on "TimeSliceDefinition" t (cost=0.00..96.53
rows=2453 width=131) (actual time=0.009..0.697 rows=2453 loops=3)
Buffers: shared hit=216
Planning Time: 0.424 ms
Execution Time: 58.146 ms
and with tenant separation (wrong estimates):
explain (ANALYZE, COSTS, BUFFERS, FORMAT text)
select *
from "Reservation"."Reservation" r
join "Rates"."TimeSliceDefinition" t on r."TimeSliceDefinitionId" =
t."Id"
join "Reservation"."NoShowFee" f on r."NoShowFeeId" = f."Id"
where r."DepartureUtc" > '2018-01-01' and r."ArrivalUtc" < '2019-12-31'
and r."AccountCode" = 'Code1' and t."AccountCode" = 'Code1' and
f."AccountCode" = 'Code1'
Nested Loop (cost=419.37..6656.11 rows=3 width=793) (actual
time=1.367..95.051 rows=8992 loops=1)
Buffers: shared hit=41970
-> Nested Loop (cost=418.95..6504.77 rows=49 width=650) (actual
time=1.355..49.789 rows=8992 loops=1)
Buffers: shared hit=5980
-> Bitmap Heap Scan on "TimeSliceDefinition" t (cost=4.39..39.99
rows=14 width=131) (actual time=0.015..0.035 rows=14 loops=1)
Recheck Cond: ("AccountCode" = 'Code1'::text)
Heap Blocks: exact=7
Buffers: shared hit=9
-> Bitmap Index Scan on
"IX_TimeSliceDefinition_AccountCode_PropertyId_Name" (cost=0.00..4.39
rows=14 width=0) (actual time=0.010..0.010 rows=14 loops=1)
Index Cond: ("AccountCode" = 'Code1'::text)
Buffers: shared hit=2
-> Bitmap Heap Scan on "Reservation" r (cost=414.56..461.66
rows=11 width=519) (actual time=1.104..2.987 rows=642 loops=14)
Recheck Cond: (("TimeSliceDefinitionId" = t."Id") AND
("AccountCode" = 'Code1'::text))
Filter: (("DepartureUtc" > '2018-01-01 00:00:00'::timestamp
without time zone) AND ("ArrivalUtc" < '2019-12-31 00:00:00'::timestamp
without time zone))
Rows Removed by Filter: 14
Heap Blocks: exact=4776
Buffers: shared hit=5971
-> BitmapAnd (cost=414.56..414.56 rows=12 width=0) (actual
time=1.057..1.057 rows=0 loops=14)
Buffers: shared hit=1195
-> Bitmap Index Scan on
"IX_Reservation_TimeSliceDefinitionId" (cost=0.00..13.84 rows=189 width=0)
(actual time=0.063..0.063 rows=665 loops=14)
Index Cond: ("TimeSliceDefinitionId" = t."Id")
Buffers: shared hit=90
-> Bitmap Index Scan on
"IX_Reservation_AccountCode_EntityId" (cost=0.00..398.31 rows=8786
width=0) (actual time=1.056..1.056 rows=9225 loops=13)
Index Cond: ("AccountCode" = 'Code1'::text)
Buffers: shared hit=1105
-> Index Scan using "PK_NoShowFee" on "NoShowFee" f (cost=0.42..3.09
rows=1 width=143) (actual time=0.003..0.003 rows=1 loops=8992)
Index Cond: ("Id" = r."NoShowFeeId")
Filter: ("AccountCode" = 'Code1'::text)
Buffers: shared hit=35990
Planning Time: 0.766 ms
Execution Time: 95.687 ms
now, these 2 queries are fairly similar in execution time. The problem is,
that this is the kinda a base query and our ORM is building a monster query
around it which takes 2-3 minutes to execute (for one specific tenant - not
for others). I took the advise from
https://www.postgresql.org/message-id/CAFh58O_Myr6G3tcH3gcGrF-=oexb08pjdwzcsbcecovaips...@mail.gmail.com
and
tried the same query with SET enable_nestloop=OFF - it took 200ms when it
used HASH JOINs
How can I improve the estimates? Just as a catch all, I tried to set
default_statistics_target to 10000, re-analyzed the DB and it didn't help.
I tried to to create ndistinct statistics to include the account code in
the statistics:
CREATE STATISTICS MT_ReservationDepartureUtc (ndistinct) on "AccountCode",
"DepartureUtc" from "Reservation"."Reservation";
CREATE STATISTICS MT_ReservationArrivalUtc (ndistinct) on "AccountCode",
"ArrivalUtc" from "Reservation"."Reservation";
CREATE STATISTICS MT_ReservationNoShowFeeId (ndistinct) on "AccountCode",
"NoShowFeeId" from "Reservation"."Reservation";
CREATE STATISTICS MT_ReservationTimeSliceDefinitionId (ndistinct) on
"AccountCode", "TimeSliceDefinitionId" from "Reservation"."Reservation";
but that didn't help either
So I know the issue - it's using a nested loop instead of a hash join, for
that one account which has a ton of data sets - all others have less items,
that's why they are fine, although even other accounts would benefit from
hash joins - one test had 0,15ms execution time instead of 95,5ms
How can I help postgres to collect the correct data?
Thank you,
Peter
