explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5rNg

Settings
# exclusive inclusive rows x rows loops node
1. 2.612 113.456 ↓ 69.8 12,000 1

Unique (cost=1,913.42..1,916.00 rows=172 width=101) (actual time=108.677..113.456 rows=12,000 loops=1)

2. 11.654 110.844 ↓ 69.8 12,000 1

Sort (cost=1,913.42..1,913.85 rows=172 width=101) (actual time=108.677..110.844 rows=12,000 loops=1)

  • Sort Key: custodian.id, ((((custodian.first_name)::text || ' '::text) || (custodian.last_name)::text)), ((((supervisor.first_name)::text || ' '::text) || (supervisor.last_name)::text)), legalhold_custodian.last_modified_date, legalhold.legal_hold_name
  • Sort Method: external merge Disk: 1,000kB
3. 6.839 99.190 ↓ 69.8 12,000 1

Hash Left Join (cost=12.24..1,907.04 rows=172 width=101) (actual time=6.826..99.190 rows=12,000 loops=1)

  • Hash Cond: (pl.personlegalholds_id = pd.id)
  • Filter: ((NOT legalhold.confidential) OR ((pd.person_user_name)::text = 'admin'::text) OR ((legalhold.created_by)::text = 'admin'::text))
4. 1.963 92.334 ↓ 66.7 12,000 1

Merge Left Join (cost=1.56..1,891.27 rows=180 width=107) (actual time=6.794..92.334 rows=12,000 loops=1)

  • Merge Cond: (legalhold.id = pl.legalholds_id)
5. 3.148 90.361 ↓ 66.7 12,000 1

Merge Join (cost=1.41..1,881.52 rows=180 width=107) (actual time=6.781..90.361 rows=12,000 loops=1)

  • Merge Cond: (legalhold_custodian.legalhold_id = legalhold.id)
6. 3.671 83.596 ↓ 66.7 12,000 1

Nested Loop (cost=1.14..3,051.25 rows=180 width=82) (actual time=6.767..83.596 rows=12,000 loops=1)

7. 1.708 67.925 ↓ 66.7 12,000 1

Nested Loop (cost=0.99..3,020.64 rows=180 width=76) (actual time=6.763..67.925 rows=12,000 loops=1)

8. 3.447 42.217 ↓ 66.7 12,000 1

Merge Semi Join (cost=0.57..2,585.40 rows=180 width=32) (actual time=6.750..42.217 rows=12,000 loops=1)

  • Merge Cond: (legalhold_custodian.legalhold_id = legalhold_1.id)
9. 38.760 38.760 ↓ 59.6 24,000 1

Index Scan using legalhold_custodian_legalhold_id_idx on legalhold_custodian (cost=0.29..4,256.61 rows=403 width=24) (actual time=0.013..38.760 rows=24,000 loops=1)

  • Filter: (((custodian_status)::text = 'ACTIVE'::text) AND (date(last_modified_date) >= '2000-03-01'::date) AND (date(last_modified_date) <= '2020-04-01'::date))
  • Rows Removed by Filter: 64,106
10. 0.010 0.010 ↑ 220.7 3 1

Index Scan using pk_legalhold on legalhold legalhold_1 (cost=0.28..99.09 rows=662 width=8) (actual time=0.007..0.010 rows=3 loops=1)

  • Filter: ((legal_hold_status)::text <> 'DRAFT'::text)
  • Rows Removed by Filter: 4
11. 24.000 24.000 ↑ 1.0 1 12,000

Index Scan using pk_custodian on custodian (cost=0.42..2.41 rows=1 width=52) (actual time=0.002..0.002 rows=1 loops=12,000)

  • Index Cond: (id = legalhold_custodian.custodian_id)
12. 12.000 12.000 ↑ 1.0 1 12,000

Index Scan using pk_supervisor on supervisor (cost=0.14..0.16 rows=1 width=22) (actual time=0.001..0.001 rows=1 loops=12,000)

  • Index Cond: (id = custodian.supervisor_id)
13. 3.617 3.617 ↓ 8.1 12,007 1

Index Scan using pk_legalhold on legalhold (cost=0.28..95.40 rows=1,479 width=41) (actual time=0.009..3.617 rows=12,007 loops=1)

14. 0.010 0.010 ↑ 352.0 1 1

Index Only Scan using legalhold_personlegalhold_pkey on legalhold_personlegalhold pl (cost=0.15..9.77 rows=352 width=16) (actual time=0.010..0.010 rows=1 loops=1)

  • Heap Fetches: 1
15. 0.007 0.017 ↑ 1.8 17 1

Hash (cost=10.30..10.30 rows=30 width=524) (actual time=0.017..0.017 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.010 0.010 ↑ 1.8 17 1

Seq Scan on person_details pd (cost=0.00..10.30 rows=30 width=524) (actual time=0.008..0.010 rows=17 loops=1)

Planning time : 1.459 ms
Execution time : 114.322 ms