explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2LTN

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

CTE Scan on diagnosticreports (cost=10,709.00..10,712.48 rows=116 width=416) (actual time=6.175..6.175 rows=0 loops=1)

2.          

CTE srpatientcte

3. 0.003 0.018 ↓ 0.0 0 1

Limit (cost=0.00..1.23 rows=1 width=832) (actual time=0.017..0.018 rows=0 loops=1)

4. 0.015 0.015 ↓ 0.0 0 1

Seq Scan on srpatient srp (cost=0.00..1.23 rows=1 width=832) (actual time=0.015..0.015 rows=0 loops=1)

  • Filter: ((("NHSNumber")::text = '6164252024'::text) AND (("DateBirth")::date = '1943-08-29'::date))
  • Rows Removed by Filter: 13
5.          

CTE laboratorytestobservations_cte

6. 3.088 6.121 ↑ 7,234.0 1 1

HashAggregate (cost=10,252.61..10,324.95 rows=7,234 width=6) (actual time=6.121..6.121 rows=1 loops=1)

  • Group Key: ctv3_hierarchy.child
7. 2.172 3.033 ↓ 1.0 7,683 1

Bitmap Heap Scan on ctv3_hierarchy (cost=97.90..10,233.93 rows=7,471 width=6) (actual time=0.885..3.033 rows=7,683 loops=1)

  • Recheck Cond: (tag && '{X76sW,X7A0B}'::text[])
  • Heap Blocks: exact=161
8. 0.861 0.861 ↓ 1.0 7,683 1

Bitmap Index Scan on ctv3_hierarchy_tag (cost=0.00..96.03 rows=7,471 width=0) (actual time=0.860..0.861 rows=7,683 loops=1)

  • Index Cond: (tag && '{X76sW,X7A0B}'::text[])
9.          

CTE diagnosticreport_observations

10. 0.000 6.170 ↓ 0.0 0 1

WindowAgg (cost=312.06..375.86 rows=116 width=243) (actual time=6.170..6.170 rows=0 loops=1)

11. 0.010 6.170 ↓ 0.0 0 1

Sort (cost=312.06..312.35 rows=116 width=164) (actual time=6.169..6.170 rows=0 loops=1)

  • Sort Key: c."IDPatient", c."IDEvent", c."DateEventRecorded
  • Sort Method: quicksort Memory: 25kB
12. 0.014 6.160 ↓ 0.0 0 1

Hash Join (cost=135.04..308.08 rows=116 width=164) (actual time=6.160..6.160 rows=0 loops=1)

  • Hash Cond: ((lto.ctv3_code)::text = (c."CTV3Code")::text)
13. 6.123 6.123 ↑ 7,234.0 1 1

CTE Scan on laboratorytestobservations_cte lto (cost=0.00..144.68 rows=7,234 width=118) (actual time=6.123..6.123 rows=1 loops=1)

14. 0.002 0.023 ↓ 0.0 0 1

Hash (cost=134.91..134.91 rows=10 width=164) (actual time=0.022..0.023 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
15. 0.001 0.021 ↓ 0.0 0 1

Nested Loop Left Join (cost=5.89..134.91 rows=10 width=164) (actual time=0.021..0.021 rows=0 loops=1)

16. 0.000 0.020 ↓ 0.0 0 1

Nested Loop Left Join (cost=5.61..131.40 rows=10 width=149) (actual time=0.020..0.020 rows=0 loops=1)

17. 0.001 0.020 ↓ 0.0 0 1

Nested Loop Anti Join (cost=5.18..48.39 rows=10 width=139) (actual time=0.019..0.020 rows=0 loops=1)

18. 0.000 0.019 ↓ 0.0 0 1

Nested Loop (cost=5.04..46.49 rows=11 width=139) (actual time=0.019..0.019 rows=0 loops=1)

  • Join Filter: (p."RowIdentifier" = c."IDPatient")
19. 0.001 0.019 ↓ 0.0 0 1

Nested Loop (cost=4.76..17.20 rows=63 width=78) (actual time=0.019..0.019 rows=0 loops=1)

20. 0.018 0.018 ↓ 0.0 0 1

CTE Scan on srpatientcte p (cost=0.00..0.02 rows=1 width=46) (actual time=0.017..0.018 rows=0 loops=1)

21. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on srevent e (cost=4.76..16.55 rows=63 width=32) (never executed)

  • Recheck Cond: ("IDPatient" = p."RowIdentifier")
22. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on "srevent_IDPatient_idx" (cost=0.00..4.75 rows=63 width=0) (never executed)

  • Index Cond: ("IDPatient" = p."RowIdentifier")
23. 0.000 0.000 ↓ 0.0 0

Index Scan using filtered_srcodes_idp_ide_dateeventrecorded_06072020071607 on filtered_srcodes c (cost=0.28..0.45 rows=1 width=85) (never executed)

  • Index Cond: (("IDPatient" = e."IDPatient") AND ("IDEvent" = e."RowIdentifier"))
24. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pointofcare_ctv3_codes_ctv3_code_key on pointofcare_ctv3_codes poc_ctv3 (cost=0.14..0.17 rows=1 width=6) (never executed)

  • Index Cond: (ctv3_code = (c."CTV3Code")::bpchar)
  • Heap Fetches: 0
25. 0.000 0.000 ↓ 0.0 0

Index Scan using srstaffmember_pkey on srstaffmember sm (cost=0.43..8.29 rows=1 width=26) (never executed)

  • Index Cond: (e."IDDoneBy" = "RowIdentifier")
26. 0.000 0.000 ↓ 0.0 0

Index Scan using srstaffmemberprofilerole_pkey on srstaffmemberprofilerole smp (cost=0.28..0.34 rows=1 width=31) (never executed)

  • Index Cond: (e."IDStaffMemberProfileRole" = "RowIdentifier")
27.          

CTE diagnosticreports

28. 0.002 6.173 ↓ 0.0 0 1

HashAggregate (cost=4.64..6.96 rows=116 width=238) (actual time=6.173..6.173 rows=0 loops=1)

  • Group Key: diagnosticreport_observations.observation_nhsnumber, diagnosticreport_observations.observation_effectivedatetime, diagnosticreport_observations.observation_issued, diagnosticreport_observations.observations_resultcount, diagnosticreport_observations.observations_performer_referrence
29. 6.171 6.171 ↓ 0.0 0 1

CTE Scan on diagnosticreport_observations (cost=0.00..2.32 rows=116 width=718) (actual time=6.171..6.171 rows=0 loops=1)

Planning time : 2.367 ms
Execution time : 6.655 ms