explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ym64 : Optimization for: plan #IMYf

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.018 2,253.532 ↓ 10.0 10 1

Limit (cost=1,564.02..1,564.02 rows=1 width=180) (actual time=2,253.513..2,253.532 rows=10 loops=1)

2. 0.742 2,253.514 ↓ 10.0 10 1

Sort (cost=1,564.02..1,564.02 rows=1 width=180) (actual time=2,253.511..2,253.514 rows=10 loops=1)

  • Sort Key: executions.started_at DESC
  • Sort Method: top-N heapsort Memory: 27kB
3. 2.757 2,252.772 ↓ 751.0 751 1

Nested Loop (cost=3.35..1,564.01 rows=1 width=180) (actual time=76.180..2,252.772 rows=751 loops=1)

  • Join Filter: ((executions.company_uuid)::text = (users.company_uuid)::text)
4. 0.872 2,246.260 ↓ 751.0 751 1

Nested Loop Left Join (cost=2.94..1,555.73 rows=1 width=223) (actual time=76.150..2,246.260 rows=751 loops=1)

5. 1.048 2,239.380 ↓ 751.0 751 1

Nested Loop (cost=2.38..1,547.12 rows=1 width=222) (actual time=76.130..2,239.380 rows=751 loops=1)

6. 0.928 2,233.075 ↓ 751.0 751 1

Nested Loop Semi Join (cost=1.96..1,538.87 rows=1 width=167) (actual time=76.098..2,233.075 rows=751 loops=1)

  • Join Filter: (stores.uuid = activity_assignations.store_uuid)
7. 11.749 2,042.144 ↓ 751.0 751 1

Nested Loop (cost=1.40..456.90 rows=1 width=215) (actual time=75.892..2,042.144 rows=751 loops=1)

  • Join Filter: ((activities.uuid = executions.activity_uuid) AND (activity_assignations_1.user_uuid = executions.user_uuid))
  • Rows Removed by Join Filter: 1,525
8. 10.885 47.141 ↓ 8,333.0 8,333 1

Nested Loop (cost=1.40..25.36 rows=1 width=171) (actual time=0.066..47.141 rows=8,333 loops=1)

9. 4.348 11.257 ↓ 8,333.0 8,333 1

Nested Loop (cost=0.98..17.09 rows=1 width=124) (actual time=0.054..11.257 rows=8,333 loops=1)

10. 0.056 0.056 ↓ 11.0 11 1

Index Scan using idx_activities_activity_type on activities (cost=0.41..8.44 rows=1 width=60) (actual time=0.027..0.056 rows=11 loops=1)

  • Index Cond: ((company_uuid = '233e9d9c-2577-4ca1-a95a-d9b567adc72a'::uuid) AND (enum_activity_type = 2))
  • Filter: (active AND ((state)::text = ANY ('{published,unpublished}'::text[])))
  • Rows Removed by Filter: 13
11. 6.853 6.853 ↓ 252.7 758 11

Index Only Scan using activity_assignations_unique_active on activity_assignations activity_assignations_1 (cost=0.56..8.62 rows=3 width=64) (actual time=0.008..0.623 rows=758 loops=11)

  • Index Cond: ((company_uuid = '233e9d9c-2577-4ca1-a95a-d9b567adc72a'::uuid) AND (activity_uuid = activities.uuid))
  • Heap Fetches: 1,069
12. 24.999 24.999 ↑ 1.0 1 8,333

Index Scan using index_stores_on_uuid on stores (cost=0.42..8.28 rows=1 width=47) (actual time=0.003..0.003 rows=1 loops=8,333)

  • Index Cond: (uuid = activity_assignations_1.store_uuid)
  • Filter: active
13. 24.999 1,983.254 ↓ 0.0 0 8,333

Append (cost=0.00..431.47 rows=4 width=140) (actual time=0.198..0.238 rows=0 loops=8,333)

14. 0.000 0.000 ↓ 0.0 0 8,333

Seq Scan on executions (cost=0.00..0.00 rows=1 width=120) (actual time=0.000..0.000 rows=0 loops=8,333)

  • Filter: ((started_at >= '2020-05-01 03:00:00'::timestamp without time zone) AND (started_at <= '2020-08-04 03:59:59'::timestamp without time zone) AND (company_uuid = '233e9d9c-2577-4ca1-a95a-d9b567adc72a'::uuid) AND (activity_type_id = 2) AND (stores.uuid = store_uuid))
15. 424.983 424.983 ↓ 0.0 0 8,333

Index Scan using executions_233e9d9c_202005_company_date_activity_store_user on executions_233e9d9c_202005 (cost=0.28..80.43 rows=1 width=140) (actual time=0.046..0.051 rows=0 loops=8,333)

  • Index Cond: ((company_uuid = '233e9d9c-2577-4ca1-a95a-d9b567adc72a'::uuid) AND (started_at >= '2020-05-01 03:00:00'::timestamp without time zone) AND (started_at <= '2020-08-04 03:59:59'::timestamp without time zone) AND (activity_type_id = 2) AND (store_uuid = stores.uuid))
16. 833.300 833.300 ↓ 0.0 0 8,333

Index Scan using executions_233e9d9c_202006_company_date_activity_store_user on executions_233e9d9c_202006 (cost=0.28..190.66 rows=1 width=140) (actual time=0.097..0.100 rows=0 loops=8,333)

  • Index Cond: ((company_uuid = '233e9d9c-2577-4ca1-a95a-d9b567adc72a'::uuid) AND (started_at >= '2020-05-01 03:00:00'::timestamp without time zone) AND (started_at <= '2020-08-04 03:59:59'::timestamp without time zone) AND (activity_type_id = 2) AND (store_uuid = stores.uuid))
17. 699.972 699.972 ↓ 0.0 0 8,333

Index Scan Backward using executions_233e9d9c_202007_company_date_activity_store_user on executions_233e9d9c_202007 (cost=0.28..160.38 rows=1 width=140) (actual time=0.084..0.084 rows=0 loops=8,333)

  • Index Cond: ((company_uuid = '233e9d9c-2577-4ca1-a95a-d9b567adc72a'::uuid) AND (started_at >= '2020-05-01 03:00:00'::timestamp without time zone) AND (started_at <= '2020-08-04 03:59:59'::timestamp without time zone) AND (activity_type_id = 2))
  • Filter: (stores.uuid = store_uuid)
18. 190.003 190.003 ↑ 1.0 1 751

Index Only Scan using index_activity_assignations_on_company_and_user_and_store on activity_assignations (cost=0.56..1,081.96 rows=1 width=32) (actual time=0.253..0.253 rows=1 loops=751)

  • Index Cond: ((company_uuid = '233e9d9c-2577-4ca1-a95a-d9b567adc72a'::uuid) AND (store_uuid = activity_assignations_1.store_uuid))
  • Heap Fetches: 0
19. 5.257 5.257 ↑ 1.0 1 751

Index Scan using index_users_on_uuid on users users_1 (cost=0.42..8.25 rows=1 width=55) (actual time=0.007..0.007 rows=1 loops=751)

  • Index Cond: ((uuid)::text = (executions.user_uuid)::text)
20. 1.502 6.008 ↑ 1.0 1 751

Group (cost=0.56..8.59 rows=1 width=17) (actual time=0.007..0.008 rows=1 loops=751)

  • Group Key: execution_pictures.execution_uuid
21. 4.506 4.506 ↑ 1.0 1 751

Index Scan using idx_execution_pictures_execution_uuid on execution_pictures (cost=0.56..8.59 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=751)

  • Index Cond: ((company_uuid = '233e9d9c-2577-4ca1-a95a-d9b567adc72a'::uuid) AND (execution_uuid = executions.uuid))
  • Filter: ((execution_date >= '2020-05-01 00:00:00'::timestamp without time zone) AND (execution_date <= '2020-08-03 23:59:59'::timestamp without time zone))
22. 3.755 3.755 ↑ 1.0 1 751

Index Scan using index_users_on_uuid on users (cost=0.41..8.25 rows=1 width=74) (actual time=0.005..0.005 rows=1 loops=751)

  • Index Cond: ((uuid)::text = (users_1.uuid)::text)
  • Filter: (active AND ((permission)::text = ANY ('{analyst_auditor,admin,auditor,gatekeeper}'::text[])))
Planning time : 447.778 ms
Execution time : 2,253.837 ms