explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O2OI : Optimization for: plan #LvZ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 4.986 ↑ 1.0 1 1

Limit (cost=397.02..397.03 rows=1 width=79) (actual time=4.985..4.986 rows=1 loops=1)

  • width=79) (actual time=0.004..0.027 rows=45 loops=1)
  • Planning time: 0.456 ms
  • Execution time: 5.052 ms
2. 0.017 4.984 ↑ 14.0 1 1

Sort (cost=397.02..397.06 rows=14 width=79) (actual time=4.984..4.984 rows=1 loops=1)

  • Sort Key: (CASE WHEN (clinical_system_order.clinical_system_order_pe IS NOT NULL) THEN clinical_system_order.clinical_system_or
  • Sort Method: top-N heapsort Memory: 25kB
3. 0.788 4.967 ↓ 3.1 43 1

HashAggregate (cost=396.81..396.95 rows=14 width=79) (actual time=4.961..4.967 rows=43 loops=1)

  • Group Key: CASE WHEN (clinical_system_order.clinical_system_order_pe IS NOT NULL) THEN clinical_system_order.clinical_sys
4. 0.644 4.179 ↓ 1.4 1,489 1

Hash Join (cost=312.23..378.35 rows=1,055 width=79) (actual time=3.334..4.179 rows=1,489 loops=1)

  • Hash Cond: (pe_element.pe_element_group_id = pe_element_group.pe_element_group_id)
5. 0.213 0.213 ↑ 1.4 1,469 1

Seq Scan on pe_element (cost=0.00..48.05 rows=2,005 width=4) (actual time=0.005..0.213 rows=1,469 loops=1)

6. 0.019 3.322 ↓ 2.0 60 1

Hash (cost=311.86..311.86 rows=30 width=83) (actual time=3.322..3.322 rows=60 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
7. 0.161 3.303 ↓ 2.0 60 1

Merge Join (cost=3.37..311.86 rows=30 width=83) (actual time=3.127..3.303 rows=60 loops=1)

  • Merge Cond: (clinical_system.clinical_system_pe_gwid = pe_element_group.pe_element_group_system_id)
  • -> Nested Loop Left Join (cost=0.14..545.86 rows=39 width=79) (actual time=3.093..3.245 rows=45 loops
  • Join Filter: (clinical_system_order.clinical_system_order_systemid = clinical_system.clinical_sys
  • Rows Removed by Join Filter: 714
  • -> Index Scan using clinical_system_pe_gwid_index1 on clinical_system (cost=0.14..12.88 rows=37
  • Index Cond: (clinical_system_pe_gwid IS NOT NULL)
  • Filter: (clinical_system_isactive IS TRUE)
8. 3.105 3.105 ↑ 6.1 16 45

Materialize (cost=0.00..478.84 rows=98 width=8) (actual time=0.068..0.069 rows=16 loops=45)

  • -> Seq Scan on clinical_system_order (cost=0.00..478.35 rows=98 width=8) (actual time=3.0
  • Filter: (clinical_system_order_templateid = 3840)
  • Rows Removed by Filter: 23381
9. 0.037 0.037 ↓ 1.1 61 1

Sort (cost=3.23..3.37 rows=57 width=8) (actual time=0.031..0.037 rows=61 loops=1)

  • Sort Key: pe_element_group.pe_element_group_system_id
  • Sort Method: quicksort Memory: 27kB
  • -> Seq Scan on pe_element_group (cost=0.00..1.57 rows=57 width=8) (actual time=0.004..0.012 row