OrmEugensson

  
QUERY PLAN

1
Aggregate  (cost=10110.53..10110.54 rows=1 width=16) (actual time=97.916..97.916 rows=1 loops=1)

2
  ->  Nested Loop  (cost=3.49..10055.28 rows=4420 width=52) (actual time=0.467..96.862 rows=4000 loops=1)

3
        CTE cte_holidays

4
          ->  Values Scan on "*VALUES*"  (cost=0.00..0.53 rows=30 width=4) (actual time=0.038..0.133 rows=30 loops=1)

5
        CTE cte_working_weekend

6
          ->  Values Scan on "*VALUES*_1"  (cost=0.00..0.05 rows=3 width=4) (actual time=0.006..0.014 rows=3 loops=1)

7
        CTE cte_holidays_improved

8
          ->  CTE Scan on cte_holidays h  (cost=0.00..0.83 rows=28 width=4) (actual time=0.105..0.248 rows=30 loops=1)

9
                Filter: (date_part('isodow'::text, (date)::timestamp without time zone) <> ALL ('{6,7}'::double precision[]))

10
        ->  Nested Loop Left Join  (cost=0.80..4202.90 rows=4420 width=36) (actual time=0.418..54.962 rows=4000 loops=1)

11
              ->  Nested Loop  (cost=0.00..368.55 rows=4420 width=28) (actual time=0.066..7.080 rows=4000 loops=1)

12
                    ->  Nested Loop  (cost=0.00..224.90 rows=4420 width=24) (actual time=0.062..4.996 rows=4000 loops=1)

13
                          ->  Seq Scan on periods p  (cost=0.00..70.20 rows=4420 width=16) (actual time=0.040..1.388 rows=4000 loops=1)

14
                          ->  Result  (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=4000)

15
                    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=4000)

16
              ->  Result  (cost=0.80..0.85 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=4000)

17
                    InitPlan 4 (returns $9)

18
                      ->  Aggregate  (cost=0.70..0.71 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=4000)

19
                            ->  CTE Scan on cte_holidays_improved tt_1  (cost=0.00..0.70 rows=1 width=0) (actual time=0.003..0.007 rows=9 loops=4000)

20
                                  Filter: ((date > $7) AND (date < $8))

21
                                  Rows Removed by Filter: 21

22
                    InitPlan 5 (returns $10)

23
                      ->  Aggregate  (cost=0.08..0.09 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=4000)

24
                            ->  CTE Scan on cte_working_weekend tt_2  (cost=0.00..0.08 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=4000)

25
                                  Filter: ((date > $7) AND (date < $8))

26
                                  Rows Removed by Filter: 2

27
        ->  Aggregate  (cost=1.28..1.29 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=4000)

28
              ->  Nested Loop  (cost=0.92..1.27 rows=1 width=32) (actual time=0.005..0.007 rows=1 loops=4000)

29
                    ->  Hash Anti Join  (cost=0.92..1.23 rows=1 width=20) (actual time=0.004..0.005 rows=1 loops=4000)

30
                          Hash Cond: ("*SELECT* 1".start_date = tt.date)

31
                          ->  Append  (cost=0.01..0.31 rows=3 width=20) (actual time=0.003..0.005 rows=1 loops=4000)

32
                                ->  Subquery Scan on "*SELECT* 1"  (cost=0.01..0.11 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=4000)

33
                                      Filter: ((date_part('isodow'::text, ("*SELECT* 1".start_date)::timestamp without time zone) <> ALL ('{6,7}'::double precision[])) OR (alternatives: SubPlan 6 or hashed SubPlan 7))

34
                                      Rows Removed by Filter: 0

35
                                      ->  Result  (cost=0.01..0.02 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=4000)

36
                                            One-Time Filter: ((((((p.stop_time)::date) - ((p.start_time)::date))) >= 1) AND (p.start_time <= (((p.start_time)::date) + '19:00:00'::time without time zone)))

37
                                      SubPlan 6

38
                                        ->  CTE Scan on cte_working_weekend tt_3  (cost=0.00..0.07 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1603)

39
                                              Filter: (date = "*SELECT* 1".start_date)

40
                                              Rows Removed by Filter: 3

41
                                      SubPlan 7

42
                                        ->  CTE Scan on cte_working_weekend tt_4  (cost=0.00..0.06 rows=3 width=4) (never executed)

43
                                ->  Subquery Scan on "*SELECT* 2"  (cost=0.01..0.11 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=4000)

44
                                      Filter: ((date_part('isodow'::text, ("*SELECT* 2".stop_date)::timestamp without time zone) <> ALL ('{6,7}'::double precision[])) OR (alternatives: SubPlan 6 or hashed SubPlan 7))

45
                                      Rows Removed by Filter: 0

46
                                      ->  Result  (cost=0.01..0.02 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=4000)

47
                                            One-Time Filter: ((((((p.stop_time)::date) - ((p.start_time)::date))) >= 1) AND ((((p.stop_time)::date) + '10:00:00'::time without time zone) <= p.stop_time))

48
                                ->  Subquery Scan on "*SELECT* 3"  (cost=0.01..0.10 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=4000)

49
                                      Filter: ((date_part('isodow'::text, ("*SELECT* 3".start_date)::timestamp without time zone) <> ALL ('{6,7}'::double precision[])) OR (alternatives: SubPlan 6 or hashed SubPlan 7))

50
                                      Rows Removed by Filter: 0

51
                                      ->  Result  (cost=0.01..0.01 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=4000)

52
                                            One-Time Filter: ((((((p.stop_time)::date) - ((p.start_time)::date))) = 0) AND (p.start_time <= p.stop_time))

53
                          ->  Hash  (cost=0.56..0.56 rows=28 width=4) (actual time=0.014..0.014 rows=30 loops=1)

54
                                Buckets: 1024  Batches: 1  Memory Usage: 10kB

55
                                ->  CTE Scan on cte_holidays_improved tt  (cost=0.00..0.56 rows=28 width=4) (actual time=0.000..0.005 rows=30 loops=1)

56
                    ->  Result  (cost=0.00..0.02 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=3677)

57
Planning time: 1.967 ms

58
Execution time: 98.727 ms