explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GTwB

Settings
# exclusive inclusive rows x rows loops node
1. 192.566 415.906 ↑ 1.0 1 1

CTE Scan on pending p (cost=406,519.98..406,520.11 rows=1 width=32) (actual time=415.904..415.906 rows=1 loops=1)

2.          

CTE documents

3. 166.225 174.967 ↓ 1.0 31,442 1

Bitmap Heap Scan on invoice i (cost=49.24..258,376.79 rows=29,987 width=81) (actual time=14.069..174.967 rows=31,442 loops=1)

  • Recheck Cond: (company_id = 11504)
  • Filter: ((ppr_id IS NULL) AND (deleted IS NULL) AND (total_final > '0'::double precision) AND (status = 0) AND (type = ANY ('{0,1,3}'::integer[])))
  • Heap Blocks: exact=23768
4. 8.742 8.742 ↓ 29.5 31,442 1

Bitmap Index Scan on test_2 (cost=0.00..41.74 rows=1,066 width=0) (actual time=8.742..8.742 rows=31,442 loops=1)

  • Index Cond: ((company_id = 11504) AND ((ppr_id IS NULL) = true) AND ((deleted IS NULL) = true) AND ((status = 0) = true) AND ((type = ANY ('{0,1,3}'::integer[])) = true) AND ((total_final > '0'::double precision) = true))
5.          

SubPlan (forBitmap Heap Scan)

6. 0.000 0.000 ↓ 0.0 0

Index Only Scan using test on invoice_log (cost=0.43..8.45 rows=1 width=0) (never executed)

  • Index Cond: ((invoice_id = i.id) AND (type = 'payment'::text))
  • Heap Fetches: 0
7.          

CTE pending

8. 1.013 192.487 ↑ 1.0 1 1

Aggregate (cost=1,799.26..1,799.27 rows=1 width=64) (actual time=192.486..192.487 rows=1 loops=1)

9. 191.474 191.474 ↑ 36.3 413 1

CTE Scan on documents d (cost=0.00..599.74 rows=14,994 width=44) (actual time=19.603..191.474 rows=413 loops=1)

  • Filter: has_reminder
  • Rows Removed by Filter: 31029
10.          

CTE factual

11. 2.702 181.725 ↑ 13.4 14 1

GroupAggregate (cost=144,485.97..144,493.96 rows=188 width=24) (actual time=178.619..181.725 rows=14 loops=1)

  • Group Key: (date_trunc('month'::text, l.date))
12. 3.134 179.023 ↓ 40.4 7,596 1

Sort (cost=144,485.97..144,486.44 rows=188 width=60) (actual time=178.380..179.023 rows=7,596 loops=1)

  • Sort Key: (date_trunc('month'::text, l.date))
  • Sort Method: quicksort Memory: 786kB
13. 12.318 175.889 ↓ 40.4 7,596 1

Nested Loop (cost=0.43..144,478.87 rows=188 width=60) (actual time=0.185..175.889 rows=7,596 loops=1)

14. 6.361 6.361 ↓ 1.0 31,442 1

CTE Scan on documents d_1 (cost=0.00..599.74 rows=29,987 width=48) (actual time=0.005..6.361 rows=31,442 loops=1)

15. 157.210 157.210 ↓ 0.0 0 31,442

Index Scan using test on invoice_log l (cost=0.43..4.79 rows=1 width=20) (actual time=0.005..0.005 rows=0 loops=31,442)

  • Index Cond: ((invoice_id = d_1.id) AND ((type)::text = 'payment'::text))
  • Filter: ((date_trunc('month'::text, date) >= date_trunc('year'::text, (now() - '1 year'::interval))) AND (date_trunc('month'::text, date) <= (((date_trunc('year'::text, now()) + '1 year'::interval) - '1 mon'::interval))::date))
  • Rows Removed by Filter: 1
16.          

CTE planned

17. 2.726 41.068 ↑ 6.6 16 1

GroupAggregate (cost=1,655.46..1,661.55 rows=106 width=24) (actual time=38.026..41.068 rows=16 loops=1)

  • Group Key: (date_trunc('month'::text, (d_2.date_payment)::timestamp with time zone))
18. 1.684 38.342 ↓ 51.5 7,719 1

Sort (cost=1,655.46..1,655.83 rows=150 width=60) (actual time=37.826..38.342 rows=7,719 loops=1)

  • Sort Key: (date_trunc('month'::text, (d_2.date_payment)::timestamp with time zone))
  • Sort Method: quicksort Memory: 796kB
19. 36.658 36.658 ↓ 51.5 7,719 1

CTE Scan on documents d_2 (cost=0.00..1,650.04 rows=150 width=60) (actual time=0.038..36.658 rows=7,719 loops=1)

  • Filter: ((date_trunc('month'::text, (date_payment)::timestamp with time zone) >= date_trunc('year'::text, (now() - '1 year'::interval))) AND (date_trunc('month'::text, (date_payment)::timestamp with time zone) <= (((date_trunc('year': (...)
  • Rows Removed by Filter: 23723
20.          

Initplan (forCTE Scan)

21. 0.264 223.340 ↑ 1.0 1 1

Aggregate (cost=188.39..188.41 rows=1 width=32) (actual time=223.340..223.340 rows=1 loops=1)

22.          

Initplan (forAggregate)

23. 0.031 0.031 ↑ 1.0 1 1

Result (cost=0.00..0.08 rows=1 width=32) (actual time=0.030..0.031 rows=1 loops=1)

24. 0.155 223.045 ↑ 41.7 24 1

Merge Left Join (cost=76.40..158.30 rows=1,000 width=116) (actual time=222.898..223.045 rows=24 loops=1)

  • Merge Cond: ((date_trunc('month'::text, dd.dd))::date = f.month)
25. 0.016 41.147 ↑ 41.7 24 1

Merge Left Join (cost=65.54..86.14 rows=1,000 width=24) (actual time=41.129..41.147 rows=24 loops=1)

  • Merge Cond: (((date_trunc('month'::text, dd.dd))::date) = p_1.month)
26. 0.015 0.049 ↑ 41.7 24 1

Sort (cost=59.85..62.35 rows=1,000 width=8) (actual time=0.044..0.049 rows=24 loops=1)

  • Sort Key: ((date_trunc('month'::text, dd.dd))::date)
  • Sort Method: quicksort Memory: 26kB
27. 0.034 0.034 ↑ 41.7 24 1

Function Scan on generate_series dd (cost=0.02..10.03 rows=1,000 width=8) (actual time=0.025..0.034 rows=24 loops=1)

28. 0.005 41.082 ↑ 6.6 16 1

Sort (cost=5.69..5.95 rows=106 width=24) (actual time=41.081..41.082 rows=16 loops=1)

  • Sort Key: p_1.month
  • Sort Method: quicksort Memory: 26kB
29. 41.077 41.077 ↑ 6.6 16 1

CTE Scan on planned p_1 (cost=0.00..2.12 rows=106 width=24) (actual time=38.028..41.077 rows=16 loops=1)

30. 0.014 181.743 ↑ 13.4 14 1

Sort (cost=10.86..11.33 rows=188 width=24) (actual time=181.741..181.743 rows=14 loops=1)

  • Sort Key: f.month
  • Sort Method: quicksort Memory: 26kB
31. 181.729 181.729 ↑ 13.4 14 1

CTE Scan on factual f (cost=0.00..3.76 rows=188 width=24) (actual time=178.621..181.729 rows=14 loops=1)

Planning time : 1.998 ms
Execution time : 416.633 ms