explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6h8

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Merge Join (cost=810,985.92..1,213,663.04 rows=26,636,730 width=1,738) (actual rows= loops=)

  • Merge Cond: (fr.filter_version_id = cv.version_id)
2. 0.000 0.000 ↓ 0.0

Sort (cost=618,325.35..618,943.20 rows=247,139 width=1,746) (actual rows= loops=)

  • Sort Key: fr.filter_version_id
3. 0.000 0.000 ↓ 0.0

Hash Join (cost=187,988.74..221,134.92 rows=247,139 width=1,746) (actual rows= loops=)

  • Hash Cond: (fr.filter_id = f.filter_id)
4. 0.000 0.000 ↓ 0.0

Merge Anti Join (cost=187,575.49..217,323.51 rows=247,139 width=1,746) (actual rows= loops=)

  • Merge Cond: (fr.filter_revision_id = fo.filter_revision_id)
5. 0.000 0.000 ↓ 0.0

Index Scan using filter_revision_pkey on filter_revisions fr (cost=0.42..20,065.24 rows=356,971 width=1,754) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Materialize (cost=187,575.07..190,086.72 rows=502,330 width=8) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Sort (cost=187,575.07..188,830.89 rows=502,330 width=8) (actual rows= loops=)

  • Sort Key: fo.filter_revision_id
8. 0.000 0.000 ↓ 0.0

Hash Join (cost=110,380.99..133,138.24 rows=502,330 width=8) (actual rows= loops=)

  • Hash Cond: (fo.filter_overridden_by_version_id = current_versions.version_id)
9. 0.000 0.000 ↓ 0.0

Seq Scan on filters_overridden fo (cost=0.00..18,419.60 rows=1,004,660 width=16) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Hash (cost=110,378.49..110,378.49 rows=200 width=8) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

HashAggregate (cost=110,376.49..110,378.49 rows=200 width=8) (actual rows= loops=)

  • Group Key: current_versions.version_id
12. 0.000 0.000 ↓ 0.0

Subquery Scan on current_versions (cost=85,800.41..108,486.02 rows=756,187 width=8) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

CTE Scan on versions_cte vers (cost=85,800.41..100,924.15 rows=756,187 width=126) (actual rows= loops=)

14.          

CTE versions_cte

15. 0.000 0.000 ↓ 0.0

Recursive Union (cost=56.79..85,800.41 rows=756,187 width=144) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Seq Scan on versions record_to_find_1 (cost=56.79..1,953.55 rows=7,487 width=144) (actual rows= loops=)

  • Filter: (((version_id = (NULLIF(current_setting('dagp.user.currentversion'::text), ''::text))::bigint) OR (hashed SubPlan 4)) AND (((current_setting('dagp.instance_id'::text))::bigint = instance_id) OR ((current_setting('dagp.user.isSuperAdmin'::text))::smallint = 1) OR (hashed SubPlan 5)))
17.          

SubPlan (for Seq Scan)

18. 0.000 0.000 ↓ 0.0

Hash Join (cost=3.17..38.68 rows=240 width=8) (actual rows= loops=)

  • Hash Cond: (aip_1.version_tag_id = vt_1.version_tag_id)
19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.30..33.91 rows=240 width=8) (actual rows= loops=)

  • Join Filter: (aip_1.client_root_dag_context_id = versions_1.version_context_id)
20. 0.000 0.000 ↓ 0.0

Index Scan using versions_pkey on versions versions_1 (cost=0.30..8.31 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (version_id = (NULLIF(current_setting('dagp.user.currentversion'::text), ''::text))::bigint)
21. 0.000 0.000 ↓ 0.0

Seq Scan on asset_instance_permission aip_1 (cost=0.00..19.60 rows=480 width=16) (actual rows= loops=)

  • Filter: (NOT is_deleted)
22. 0.000 0.000 ↓ 0.0

Hash (cost=1.83..1.83 rows=83 width=16) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on version_tags vt_1 (cost=0.00..1.83 rows=83 width=16) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Function Scan on regexp_split_to_table assetinstanceid_1 (cost=0.01..15.01 rows=1,000 width=8) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,624.58..6,872.31 rows=74,870 width=144) (actual rows= loops=)

  • Hash Cond: (records_1.version_parent_id = parent_1.version_id)
26. 0.000 0.000 ↓ 0.0

WorkTable Scan on versions_cte records_1 (cost=0.00..1,497.40 rows=74,870 width=44) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Hash (cost=806.70..806.70 rows=28,070 width=108) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Seq Scan on versions parent_1 (cost=0.00..806.70 rows=28,070 width=108) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=260.89..260.89 rows=12,189 width=8) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on filters f (cost=0.00..260.89 rows=12,189 width=8) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Materialize (cost=192,660.57..196,441.51 rows=756,187 width=8) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Sort (cost=192,660.57..194,551.04 rows=756,187 width=8) (actual rows= loops=)

  • Sort Key: cv.version_id
33. 0.000 0.000 ↓ 0.0

Subquery Scan on cv (cost=85,800.41..108,486.02 rows=756,187 width=8) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

CTE Scan on versions_cte vers_1 (cost=85,800.41..100,924.15 rows=756,187 width=126) (actual rows= loops=)

35.          

CTE versions_cte

36. 0.000 0.000 ↓ 0.0

Recursive Union (cost=56.79..85,800.41 rows=756,187 width=144) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Seq Scan on versions record_to_find (cost=56.79..1,953.55 rows=7,487 width=144) (actual rows= loops=)

  • Filter: (((version_id = (NULLIF(current_setting('dagp.user.currentversion'::text), ''::text))::bigint) OR (hashed SubPlan 1)) AND (((current_setting('dagp.instance_id'::text))::bigint = instance_id) OR ((current_setting('dagp.user.isSuperAdmin'::text))::smallint = 1) OR (hashed SubPlan 2)))
38.          

SubPlan (for Seq Scan)

39. 0.000 0.000 ↓ 0.0

Hash Join (cost=3.17..38.68 rows=240 width=8) (actual rows= loops=)

  • Hash Cond: (aip.version_tag_id = vt.version_tag_id)
40. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.30..33.91 rows=240 width=8) (actual rows= loops=)

  • Join Filter: (aip.client_root_dag_context_id = versions.version_context_id)
41. 0.000 0.000 ↓ 0.0

Index Scan using versions_pkey on versions (cost=0.30..8.31 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (version_id = (NULLIF(current_setting('dagp.user.currentversion'::text), ''::text))::bigint)
42. 0.000 0.000 ↓ 0.0

Seq Scan on asset_instance_permission aip (cost=0.00..19.60 rows=480 width=16) (actual rows= loops=)

  • Filter: (NOT is_deleted)
43. 0.000 0.000 ↓ 0.0

Hash (cost=1.83..1.83 rows=83 width=16) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Seq Scan on version_tags vt (cost=0.00..1.83 rows=83 width=16) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Function Scan on regexp_split_to_table assetinstanceid (cost=0.01..15.01 rows=1,000 width=8) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,624.58..6,872.31 rows=74,870 width=144) (actual rows= loops=)

  • Hash Cond: (records.version_parent_id = parent.version_id)
47. 0.000 0.000 ↓ 0.0

WorkTable Scan on versions_cte records (cost=0.00..1,497.40 rows=74,870 width=44) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Hash (cost=806.70..806.70 rows=28,070 width=108) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Seq Scan on versions parent (cost=0.00..806.70 rows=28,070 width=108) (actual rows= loops=)