explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qglL

Settings
# exclusive inclusive rows x rows loops node
1. 1.983 42.050 ↓ 1.8 1,250 1

MixedAggregate (cost=99,127.50..99,199.16 rows=693 width=156) (actual time=41.373..42.050 rows=1,250 loops=1)

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

CTE tax

3. 0.098 0.098 ↑ 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.011..0.098 rows=397 loops=1)

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

CTE kpis

5. 4.005 39.118 ↓ 8.8 825 1

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

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

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

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

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

8. 1.280 27.584 ↓ 133.3 12,533 1

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

9. 1.828 26.304 ↓ 133.3 12,533 1

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

10. 3.065 24.476 ↓ 133.3 12,533 1

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

  • Workers Planned: 1
  • Workers Launched: 1
11. 21.048 21.411 ↓ 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.539..21.411 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.103 0.103 ↑ 1.0 1 2

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Seq Scan on v2_reservation_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: 5
22. 0.006 0.006 ↑ 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.006 rows=1 loops=2)

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 10
23. 0.357 40.067 ↓ 2.1 846 1

Hash Left Join (cost=3.06..16.37 rows=398 width=132) (actual time=39.551..40.067 rows=846 loops=1)

  • Hash Cond: (tax.calendar_date = kpis.calendar_date)
24. 0.184 0.184 ↑ 1.0 397 1

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

25. 0.135 39.526 ↓ 8.8 825 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 64kB
26. 39.391 39.391 ↓ 8.8 825 1

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

Planning time : 1.238 ms
Execution time : 42.388 ms