explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LiSG

Settings
# exclusive inclusive rows x rows loops node
1. 10.397 10.397 ↑ 2.0 30 1

CTE Scan on rws (cost=4,143.98..4,307.26 rows=59 width=840) (actual time=1.398..10.397 rows=30 loops=1)

2.          

CTE gtab

3. 0.043 0.043 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.043..0.043 rows=1 loops=1)

4.          

CTE rws

5. 0.007 9.902 ↑ 2.0 30 1

Limit (cost=111.61..4,143.72 rows=59 width=703) (actual time=1.383..9.902 rows=30 loops=1)

6. 0.008 9.895 ↑ 2.0 30 1

Append (cost=111.61..4,143.72 rows=59 width=703) (actual time=1.382..9.895 rows=30 loops=1)

7. 0.004 4.678 ↑ 2.5 16 1

Limit (cost=111.61..3,014.68 rows=40 width=559) (actual time=1.382..4.678 rows=16 loops=1)

8. 2.673 4.674 ↑ 2.5 16 1

Hash Join (cost=111.61..3,014.68 rows=40 width=559) (actual time=1.381..4.674 rows=16 loops=1)

  • Hash Cond: (d.responsible_org_orgref = resporg.swa_org_ref)
9. 0.021 1.510 ↑ 2.5 16 1

Hash Join (cost=58.29..2,909.96 rows=40 width=719) (actual time=0.547..1.510 rows=16 loops=1)

  • Hash Cond: (d.publisher_orgref = publisher.swa_org_ref)
10. 0.013 1.072 ↑ 2.5 16 1

Nested Loop Left Join (cost=4.98..2,856.54 rows=40 width=697) (actual time=0.122..1.072 rows=16 loops=1)

  • Join Filter: (d.impact_score = ei_lang.impact_score)
11. 0.024 1.059 ↑ 2.5 16 1

Nested Loop (cost=4.98..2,854.87 rows=40 width=479) (actual time=0.117..1.059 rows=16 loops=1)

  • Join Filter: (d.impact_score = ei.impact_score)
  • Rows Removed by Join Filter: 26
12. 0.031 1.035 ↑ 2.5 16 1

Nested Loop (cost=4.98..2,851.21 rows=40 width=391) (actual time=0.112..1.035 rows=16 loops=1)

13. 0.860 0.860 ↑ 2.5 16 1

Index Scan using idx_entity_eton_agg_eastingnorthing on entity_eton_agg d (cost=0.42..2,507.45 rows=40 width=278) (actual time=0.093..0.860 rows=16 loops=1)

  • Index Cond: ((location_point_easting >= 491,488) AND (location_point_easting <= 506,426) AND (location_point_northing >= 302,278) AND (location_point_northing <= 313,631))
  • Filter: (active AND (publisher_swtype = 1) AND (impact_score = ANY ('{1,2,3,4}'::integer[])) AND (start_date <= to_timestamp('05/08/2020 23:59:59 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_timestamp('05/08/2020 00:00:00 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (works_state = ANY ('{0,2,3,4,5,6,7,8}'::integer[])))
  • Rows Removed by Filter: 37
14. 0.048 0.144 ↑ 1.0 1 16

Bitmap Heap Scan on entity e (cost=4.56..8.58 rows=1 width=123) (actual time=0.009..0.009 rows=1 loops=16)

  • Recheck Cond: (entity_id = d.entity_id)
  • Filter: (((d.publisher_orgref = 1,440) AND (COALESCE((permit_status)::integer, 0) <> ALL ('{1,2,14,15,24}'::integer[]))) OR (d.publisher_orgref <> 1440))
  • Heap Blocks: exact=16
15. 0.096 0.096 ↑ 1.0 1 16

Bitmap Index Scan on pk_entity (cost=0.00..4.56 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=16)

  • Index Cond: (entity_id = d.entity_id)
16. 0.000 0.000 ↑ 1.7 3 16

Materialize (cost=0.00..1.07 rows=5 width=92) (actual time=0.000..0.000 rows=3 loops=16)

17. 0.002 0.002 ↑ 1.2 4 1

Seq Scan on eton_impact ei (cost=0.00..1.05 rows=5 width=92) (actual time=0.002..0.002 rows=4 loops=1)

18. 0.000 0.000 ↓ 0.0 0 16

Materialize (cost=0.00..1.07 rows=1 width=222) (actual time=0.000..0.000 rows=0 loops=16)

19. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on eton_impact_lang ei_lang (cost=0.00..1.06 rows=1 width=222) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 5
20. 0.218 0.417 ↓ 1.1 1,353 1

Hash (cost=38.14..38.14 rows=1,214 width=26) (actual time=0.417..0.417 rows=1,353 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 84kB
21. 0.199 0.199 ↓ 1.1 1,353 1

Seq Scan on orgref publisher (cost=0.00..38.14 rows=1,214 width=26) (actual time=0.002..0.199 rows=1,353 loops=1)

22. 0.223 0.491 ↓ 1.1 1,353 1

Hash (cost=38.14..38.14 rows=1,214 width=26) (actual time=0.491..0.491 rows=1,353 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 84kB
23. 0.268 0.268 ↓ 1.1 1,353 1

Seq Scan on orgref resporg (cost=0.00..38.14 rows=1,214 width=26) (actual time=0.004..0.268 rows=1,353 loops=1)

24. 1.637 5.209 ↑ 1.4 14 1

Nested Loop Left Join (cost=1.25..1,128.45 rows=19 width=1,004) (actual time=0.448..5.209 rows=14 loops=1)

25. 0.021 3.516 ↑ 1.4 14 1

Nested Loop (cost=1.11..1,110.61 rows=19 width=743) (actual time=0.284..3.516 rows=14 loops=1)

26. 0.021 3.439 ↑ 1.4 14 1

Nested Loop Left Join (cost=0.97..1,107.56 rows=19 width=743) (actual time=0.273..3.439 rows=14 loops=1)

27. 0.026 3.404 ↑ 1.4 14 1

Nested Loop Left Join (cost=0.69..1,101.81 rows=19 width=721) (actual time=0.270..3.404 rows=14 loops=1)

28. 0.010 3.336 ↑ 1.4 14 1

Nested Loop Left Join (cost=0.41..1,096.07 rows=19 width=699) (actual time=0.262..3.336 rows=14 loops=1)

  • Join Filter: (d_1.impact_score = ei_lang_1.impact_score)
29. 0.008 3.326 ↑ 1.4 14 1

Nested Loop (cost=0.41..1,094.72 rows=19 width=481) (actual time=0.260..3.326 rows=14 loops=1)

  • Join Filter: (d_1.impact_score = ei_1.impact_score)
  • Rows Removed by Join Filter: 26
30. 0.006 3.304 ↑ 1.4 14 1

Nested Loop (cost=0.41..1,092.43 rows=19 width=393) (actual time=0.256..3.304 rows=14 loops=1)

31. 0.045 0.045 ↑ 1.0 1 1

CTE Scan on gtab (cost=0.00..0.02 rows=1 width=32) (actual time=0.044..0.045 rows=1 loops=1)

32. 3.204 3.253 ↓ 7.0 14 1

Index Scan using sidx_entity_location_bng on entity d_1 (cost=0.41..1,092.39 rows=2 width=393) (actual time=0.210..3.253 rows=14 loops=1)

  • Index Cond: (location_bng && gtab.bbox)
  • Filter: (active AND (tm__entity_id_sw_xref IS NULL) AND (entity_type > 1) AND ((entitygroupid)::text = 'RW'::text) AND (published = 1) AND (impact_score = ANY ('{1,2,3,4}'::integer[])) AND (start_date <= to_timestamp('05/08/2020 23:59:59 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_timestamp('05/08/2020 00:00:00 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (LOCALTIMESTAMP > publish_date) AND (((publisher_orgref = 1,440) AND (COALESCE((permit_status)::integer, 0) <> ALL ('{1,2,14,15,24}'::integer[]))) OR (publisher_orgref <> 1440) OR (publisher_orgref IS NULL)) AND (((alternatives: SubPlan 2 or hashed SubPlan 3) AND (entity_type = 2)) OR (entity_type > 2)))
  • Rows Removed by Filter: 1,031
33.          

SubPlan (for Index Scan)

34. 0.000 0.000 ↓ 0.0 0

Index Only Scan using organisation_extended_funcs_pkey on organisation_extended_funcs f (cost=0.28..8.29 rows=1 width=0) (never executed)

  • Index Cond: ((extended_function_id = 14) AND (organisation_id = d_1.publisher_organisation_id))
  • Heap Fetches: 0
35. 0.030 0.049 ↓ 1.0 123 1

Bitmap Heap Scan on organisation_extended_funcs f_1 (cost=5.22..11.75 rows=122 width=4) (actual time=0.031..0.049 rows=123 loops=1)

  • Recheck Cond: (extended_function_id = 14)
  • Heap Blocks: exact=5
36. 0.019 0.019 ↓ 1.0 123 1

Bitmap Index Scan on organisation_extended_funcs_pkey (cost=0.00..5.19 rows=122 width=0) (actual time=0.019..0.019 rows=123 loops=1)

  • Index Cond: (extended_function_id = 14)
37. 0.012 0.014 ↑ 1.7 3 14

Materialize (cost=0.00..1.07 rows=5 width=92) (actual time=0.000..0.001 rows=3 loops=14)

38. 0.002 0.002 ↑ 1.2 4 1

Seq Scan on eton_impact ei_1 (cost=0.00..1.05 rows=5 width=92) (actual time=0.001..0.002 rows=4 loops=1)

39. 0.000 0.000 ↓ 0.0 0 14

Materialize (cost=0.00..1.07 rows=1 width=222) (actual time=0.000..0.000 rows=0 loops=14)

40. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on eton_impact_lang ei_lang_1 (cost=0.00..1.06 rows=1 width=222) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 5
41. 0.042 0.042 ↑ 1.0 1 14

Index Scan using pk_orgref on orgref publisher_1 (cost=0.28..0.30 rows=1 width=26) (actual time=0.003..0.003 rows=1 loops=14)

  • Index Cond: (d_1.publisher_orgref = swa_org_ref)
42. 0.014 0.014 ↓ 0.0 0 14

Index Scan using pk_orgref on orgref resporg_1 (cost=0.28..0.30 rows=1 width=26) (actual time=0.001..0.001 rows=0 loops=14)

  • Index Cond: (d_1.responsible_org_orgref = swa_org_ref)
43. 0.056 0.056 ↑ 1.0 1 14

Index Only Scan using entity_category_pkey on entity_category ecat (cost=0.14..0.16 rows=1 width=118) (actual time=0.004..0.004 rows=1 loops=14)

  • Index Cond: (nsa_code = (d_1.entity_category)::text)
  • Heap Fetches: 0
44. 0.056 0.056 ↑ 1.0 1 14

Index Scan using tm_nsa_pkey on tm_nsa tmnsa (cost=0.14..0.16 rows=1 width=22) (actual time=0.004..0.004 rows=1 loops=14)

  • Index Cond: ((d_1.entity_category)::text = (tm_nsa_type_id)::text)
Planning time : 9.321 ms
Execution time : 10.838 ms