explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LTJV

Settings
# exclusive inclusive rows x rows loops node
1. 0.075 0.519 ↑ 1.0 67 1

MixedAggregate (cost=6,503.59..6,507.32 rows=67 width=156) (actual time=0.453..0.519 rows=67 loops=1)

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

CTE tax

3. 0.016 0.016 ↑ 1.0 31 1

Index Scan using idx_hoteltax_calendar on view_v2_hotel_tax (cost=0.28..3.10 rows=31 width=9) (actual time=0.010..0.016 rows=31 loops=1)

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

CTE kpis

5. 0.026 0.374 ↓ 4.2 17 1

GroupAggregate (cost=6,498.48..6,498.66 rows=4 width=92) (actual time=0.356..0.374 rows=17 loops=1)

  • Group Key: "*SELECT* 2".calendar_date, "*SELECT* 2".segment_id
6. 0.009 0.348 ↓ 5.8 23 1

Sort (cost=6,498.48..6,498.49 rows=4 width=55) (actual time=0.347..0.348 rows=23 loops=1)

  • Sort Key: "*SELECT* 2".calendar_date, "*SELECT* 2".segment_id
  • Sort Method: quicksort Memory: 26kB
7. 0.004 0.339 ↓ 5.8 23 1

Result (cost=193.35..6,498.44 rows=4 width=55) (actual time=0.261..0.339 rows=23 loops=1)

8. 0.002 0.335 ↓ 5.8 23 1

Append (cost=193.35..6,498.40 rows=4 width=55) (actual time=0.260..0.335 rows=23 loops=1)

9. 0.003 0.333 ↓ 5.8 23 1

Subquery Scan on *SELECT* 2 (cost=193.35..6,498.38 rows=4 width=55) (actual time=0.260..0.333 rows=23 loops=1)

10. 0.131 0.330 ↓ 5.8 23 1

Index Scan using idx_reservation_calendar_reservationid on v2_reservation (cost=193.35..6,498.34 rows=4 width=391) (actual time=0.259..0.330 rows=23 loops=1)

  • Index Cond: ((calendar_date >= '2020-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: 6
11.          

SubPlan (for Index Scan)

12. 0.061 0.061 ↑ 1.0 1 1

Seq Scan on v2_reservation_agency (cost=0.00..22.25 rows=1 width=8) (actual time=0.056..0.061 rows=1 loops=1)

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

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

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 40
14. 0.069 0.069 ↑ 1.0 1 1

Seq Scan on v2_reservation_company (cost=0.00..25.72 rows=1 width=8) (actual time=0.059..0.069 rows=1 loops=1)

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 882
15. 0.005 0.005 ↑ 385.0 1 1

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

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 45
16. 0.042 0.042 ↑ 1.0 1 1

Seq Scan on v2_reservation_group (cost=0.00..14.30 rows=1 width=8) (actual time=0.041..0.042 rows=1 loops=1)

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 578
17. 0.004 0.004 ↑ 385.0 1 1

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

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 29
18. 0.004 0.004 ↑ 385.0 1 1

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

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 34
19. 0.003 0.003 ↑ 385.0 1 1

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

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 13
20. 0.003 0.003 ↑ 385.0 1 1

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

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 5
21. 0.003 0.003 ↑ 385.0 1 1

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

  • Filter: (NOT enabled)
  • Rows Removed by Filter: 10
22. 0.011 0.444 ↓ 1.0 32 1

Sort (cost=1.83..1.91 rows=31 width=132) (actual time=0.443..0.444 rows=32 loops=1)

  • Sort Key: (date_trunc('day'::text, (tax.calendar_date)::timestamp with time zone)), kpis.segment
  • Sort Method: quicksort Memory: 28kB
23. 0.023 0.433 ↓ 1.0 32 1

Hash Left Join (cost=0.13..1.06 rows=31 width=132) (actual time=0.406..0.433 rows=32 loops=1)

  • Hash Cond: (tax.calendar_date = kpis.calendar_date)
24. 0.024 0.024 ↑ 1.0 31 1

CTE Scan on tax (cost=0.00..0.62 rows=31 width=36) (actual time=0.011..0.024 rows=31 loops=1)

25. 0.004 0.386 ↓ 4.2 17 1

Hash (cost=0.08..0.08 rows=4 width=92) (actual time=0.386..0.386 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
26. 0.382 0.382 ↓ 4.2 17 1

CTE Scan on kpis (cost=0.00..0.08 rows=4 width=92) (actual time=0.357..0.382 rows=17 loops=1)

Planning time : 1.268 ms
Execution time : 0.659 ms