explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FgB7

Settings
# exclusive inclusive rows x rows loops node
1. 1.533 50.530 ↓ 4.3 1,207 1

MixedAggregate (cost=99,146.82..99,165.36 rows=283 width=156) (actual time=48.973..50.530 rows=1,207 loops=1)

  • Hash Key: kpis.segment
  • Group Key: (date_trunc('day'::text, (kpis.calendar_date)::timestamp with time zone)), kpis.segment
  • Group Key: (date_trunc('day'::text, (kpis.calendar_date)::timestamp with time zone))
  • Group Key: ()
2.          

CTE tax

3. 0.120 0.120 ↑ 1.0 397 1

Index Scan using idx_hoteltax_calendar on view_v2_hotel_tax (cost=0.28..13.54 rows=398 width=9) (actual time=0.018..0.120 rows=397 loops=1)

  • Index Cond: ((calendar_date >= '2019-10-01'::date) AND (calendar_date <= '2020-10-31'::date))
4.          

CTE kpis

5. 3.979 47.783 ↓ 8.8 825 1

GroupAggregate (cost=99,106.68..99,110.91 rows=94 width=92) (actual time=43.232..47.783 rows=825 loops=1)

  • Group Key: "*SELECT* 2".calendar_date, "*SELECT* 2".segment_id
6. 4.929 43.804 ↓ 133.3 12,533 1

Sort (cost=99,106.68..99,106.91 rows=94 width=55) (actual time=43.217..43.804 rows=12,533 loops=1)

  • Sort Key: "*SELECT* 2".calendar_date, "*SELECT* 2".segment_id
  • Sort Method: quicksort Memory: 1364kB
7. 1.443 38.875 ↓ 133.3 12,533 1

Result (cost=1,193.35..99,103.60 rows=94 width=55) (actual time=0.992..38.875 rows=12,533 loops=1)

8. 1.057 37.432 ↓ 133.3 12,533 1

Append (cost=1,193.35..99,102.66 rows=94 width=55) (actual time=0.992..37.432 rows=12,533 loops=1)

9. 1.353 36.375 ↓ 133.3 12,533 1

Subquery Scan on *SELECT* 2 (cost=1,193.35..99,102.19 rows=94 width=55) (actual time=0.991..36.375 rows=12,533 loops=1)

10. 3.687 35.022 ↓ 133.3 12,533 1

Gather (cost=1,193.35..99,101.25 rows=94 width=391) (actual time=0.990..35.022 rows=12,533 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
11. 31.033 31.335 ↓ 113.9 6,266 2

Parallel Index Scan using idx_reservation_calendar_reservationid on v2_reservation (cost=193.35..98,091.85 rows=55 width=391) (actual time=0.517..31.335 rows=6,266 loops=2)

  • Index Cond: ((calendar_date >= '2019-10-01'::date) AND (calendar_date <= '2020-10-31'::date))
  • Filter: ((record_date @> '2020-11-05'::date) AND (NOT (hashed SubPlan 2)) AND (NOT (hashed SubPlan 3)) AND (NOT (hashed SubPlan 4)) AND (NOT (hashed SubPlan 5)) AND (NOT (hashed SubPlan 6)) AND (NOT (hashed SubPlan 7)) AND (NOT (hashed SubPlan 8)) AND (NOT (hashed SubPlan 9)) AND (NOT (hashed SubPlan 10)) AND (NOT (hashed SubPlan 11)))
  • Rows Removed by Filter: 9104
12.          

SubPlan (for Parallel Index Scan)

13. 0.087 0.087 ↑ 1.0 1 2

Seq Scan on v2_reservation_agency (cost=0.00..22.25 rows=1 width=8) (actual time=0.079..0.087 rows=1 loops=2)

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 685
14. 0.008 0.008 ↑ 385.0 1 2

Seq Scan on v2_reservation_channel (cost=0.00..17.70 rows=385 width=8) (actual time=0.008..0.008 rows=1 loops=2)

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 40
15. 0.098 0.098 ↑ 1.0 1 2

Seq Scan on v2_reservation_company (cost=0.00..25.72 rows=1 width=8) (actual time=0.086..0.098 rows=1 loops=2)

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 882
16. 0.016 0.016 ↑ 385.0 1 2

Seq Scan on v2_reservation_cro (cost=0.00..17.70 rows=385 width=8) (actual time=0.015..0.016 rows=1 loops=2)

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 45
17. 0.065 0.065 ↑ 1.0 1 2

Seq Scan on v2_reservation_group (cost=0.00..14.30 rows=1 width=8) (actual time=0.064..0.065 rows=1 loops=2)

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 578
18. 0.006 0.006 ↑ 385.0 1 2

Seq Scan on v2_reservation_package (cost=0.00..17.70 rows=385 width=8) (actual time=0.006..0.006 rows=1 loops=2)

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 29
19. 0.008 0.008 ↑ 385.0 1 2

Seq Scan on v2_reservation_rate (cost=0.00..17.70 rows=385 width=8) (actual time=0.008..0.008 rows=1 loops=2)

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 34
20. 0.005 0.005 ↑ 385.0 1 2

Seq Scan on v2_reservation_room (cost=0.00..17.70 rows=385 width=8) (actual time=0.005..0.005 rows=1 loops=2)

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 13
21. 0.004 0.004 ↑ 385.0 1 2

Seq Scan on v2_reservation_segment (cost=0.00..17.70 rows=385 width=8) (actual time=0.004..0.004 rows=1 loops=2)

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 5
22. 0.005 0.005 ↑ 385.0 1 2

Seq Scan on v2_reservation_sub_segment (cost=0.00..17.70 rows=385 width=8) (actual time=0.005..0.005 rows=1 loops=2)

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 10
23. 0.272 48.997 ↓ 4.4 825 1

Sort (cost=22.37..22.84 rows=187 width=132) (actual time=48.958..48.997 rows=825 loops=1)

  • Sort Key: (date_trunc('day'::text, (kpis.calendar_date)::timestamp with time zone)), kpis.segment
  • Sort Method: quicksort Memory: 141kB
24. 0.372 48.725 ↓ 4.4 825 1

Hash Right Join (cost=3.06..15.31 rows=187 width=132) (actual time=48.194..48.725 rows=825 loops=1)

  • Hash Cond: (tax.calendar_date = kpis.calendar_date)
25. 0.202 0.202 ↑ 1.0 397 1

CTE Scan on tax (cost=0.00..7.96 rows=398 width=36) (actual time=0.019..0.202 rows=397 loops=1)

26. 0.100 48.151 ↓ 8.8 825 1

Hash (cost=1.88..1.88 rows=94 width=92) (actual time=48.151..48.151 rows=825 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 67kB
27. 48.051 48.051 ↓ 8.8 825 1

CTE Scan on kpis (cost=0.00..1.88 rows=94 width=92) (actual time=43.235..48.051 rows=825 loops=1)

Planning time : 3.561 ms
Execution time : 51.116 ms