explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pyjL

Settings
# exclusive inclusive rows x rows loops node
1. 0.280 58.648 ↑ 8.9 2,239 1

Nested Loop (cost=688.98..641,502.44 rows=20,000 width=316) (actual time=1.836..58.648 rows=2,239 loops=1)

2.          

CTE project

3. 0.006 0.006 ↑ 1.0 1 1

Index Scan using projects_pkey on projects (cost=0.14..8.16 rows=1 width=20) (actual time=0.005..0.006 rows=1 loops=1)

  • Index Cond: (id = 270)
4.          

CTE dates

5. 0.037 0.045 ↑ 32.3 31 1

Result (cost=0.04..5.07 rows=1,000 width=4) (actual time=0.019..0.045 rows=31 loops=1)

6.          

Initplan (for Result)

7. 0.007 0.007 ↑ 1.0 1 1

CTE Scan on project project_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=1)

8. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on project project_2 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

9.          

CTE datestz

10. 0.133 0.133 ↑ 32.3 31 1

CTE Scan on dates (cost=0.04..35.04 rows=1,000 width=20) (actual time=0.024..0.133 rows=31 loops=1)

11.          

Initplan (for CTE Scan)

12. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on project project_3 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

13. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on project project_4 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

14. 0.150 0.150 ↑ 32.3 31 1

CTE Scan on datestz (cost=0.00..20.00 rows=1,000 width=20) (actual time=0.025..0.150 rows=31 loops=1)

15. 0.496 58.218 ↓ 3.6 72 31

Nested Loop Left Join (cost=640.71..641.23 rows=20 width=312) (actual time=1.843..1.878 rows=72 loops=31)

16. 0.000 0.000 ↑ 1.0 1 31

CTE Scan on project (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=31)

17. 0.434 57.722 ↓ 3.6 72 31

Unique (cost=640.71..640.81 rows=20 width=312) (actual time=1.842..1.862 rows=72 loops=31)

18.          

CTE signons

19. 2.821 48.856 ↓ 4.2 84 31

Bitmap Heap Scan on signon_events (cost=298.19..470.17 rows=20 width=1,052) (actual time=1.494..1.576 rows=84 loops=31)

  • Recheck Cond: ((created_at >= $8) AND (created_at <= $9) AND (project_id = $7))
  • Filter: (signon_type = 'plant'::text)
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=2,231
20. 1.023 46.035 ↓ 0.0 0 31

BitmapAnd (cost=298.19..298.19 rows=44 width=0) (actual time=1.485..1.485 rows=0 loops=31)

21. 2.728 2.728 ↑ 1.5 689 31

Bitmap Index Scan on signon_events_on_created_at (cost=0.00..31.05 rows=1,063 width=0) (actual time=0.088..0.088 rows=689 loops=31)

  • Index Cond: ((created_at >= $8) AND (created_at <= $9))
22. 42.284 42.284 ↓ 1.2 10,431 31

Bitmap Index Scan on signon_events_on_project_id (cost=0.00..266.88 rows=8,861 width=0) (actual time=1.364..1.364 rows=10,431 loops=31)

  • Index Cond: (project_id = $7)
23. 1.178 57.288 ↓ 4.2 84 31

Sort (cost=170.55..170.60 rows=20 width=312) (actual time=1.841..1.848 rows=84 loops=31)

  • Sort Key: signons.user_id
  • Sort Method: quicksort Memory: 93kB
24. 1.706 56.110 ↓ 4.2 84 31

Nested Loop Left Join (cost=169.45..170.11 rows=20 width=312) (actual time=1.770..1.810 rows=84 loops=31)

25. 46.562 46.562 ↓ 4.2 84 31

CTE Scan on signons (cost=0.00..0.40 rows=20 width=296) (actual time=1.495..1.502 rows=84 loops=31)

26. 0.000 7.842 ↑ 1.0 1 2,614

Materialize (cost=169.45..169.47 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=2,614)

27. 0.030 8.460 ↑ 1.0 1 30

Limit (cost=169.45..169.45 rows=1 width=8) (actual time=0.281..0.282 rows=1 loops=30)

28. 0.330 8.430 ↑ 20.0 1 30

Sort (cost=169.45..169.50 rows=20 width=8) (actual time=0.281..0.281 rows=1 loops=30)

  • Sort Key: signoff_events.created_at DESC
  • Sort Method: top-N heapsort Memory: 25kB
29. 2.216 8.100 ↓ 4.2 83 30

Nested Loop (cost=0.42..169.35 rows=20 width=8) (actual time=0.009..0.270 rows=83 loops=30)

30. 3.270 3.270 ↓ 4.3 87 30

CTE Scan on signons signons_1 (cost=0.00..0.40 rows=20 width=8) (actual time=0.000..0.109 rows=87 loops=30)

31. 2.614 2.614 ↑ 1.0 1 2,614

Index Scan using signoff_events_on_signon_id on signoff_events (cost=0.42..8.44 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=2,614)

  • Index Cond: (signon_id = signons_1.id)
Planning time : 0.335 ms
Execution time : 58.887 ms