explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cVNp

Settings
# exclusive inclusive rows x rows loops node
1. 2.528 115.628 ↓ 69.8 12,000 1

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

2. 11.333 113.100 ↓ 69.8 12,000 1

Sort (cost=1,913.42..1,913.85 rows=172 width=101) (actual time=110.949..113.100 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. 7.339 101.767 ↓ 69.8 12,000 1

Hash Left Join (cost=12.24..1,907.04 rows=172 width=101) (actual time=6.759..101.767 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. 3.205 94.412 ↓ 66.7 12,000 1

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

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

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

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

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

7. 2.315 68.277 ↓ 66.7 12,000 1

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

8. 3.070 41.962 ↓ 66.7 12,000 1

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

  • Merge Cond: (legalhold_custodian.legalhold_id = legalhold_1.id)
9. 38.876 38.876 ↓ 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.023..38.876 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.016 0.016 ↑ 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.011..0.016 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.834 3.834 ↓ 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.006..3.834 rows=12,007 loops=1)

14. 0.007 0.007 ↑ 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.007..0.007 rows=1 loops=1)

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

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

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

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