explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CW4I

Settings
# exclusive inclusive rows x rows loops node
1. 30.253 1,763,549.690 ↑ 38.1 17,916 1

Unique (cost=116,070,485.25..116,172,754.65 rows=681,796 width=24) (actual time=1,763,504.618..1,763,549.690 rows=17,916 loops=1)

2. 36.646 1,763,519.437 ↑ 38.1 17,916 1

Sort (cost=116,070,485.25..116,104,575.05 rows=681,796 width=24) (actual time=1,763,504.615..1,763,519.437 rows=17,916 loops=1)

  • Sort Key: p.prs_id, u.usr_email
  • Sort Method: quicksort Memory: 2156kB
3. 1,808.477 1,763,482.791 ↑ 38.1 17,916 1

Hash Join (cost=114,175,293.59..114,748,884.54 rows=681,796 width=24) (actual time=1,759,111.398..1,763,482.791 rows=17,916 loops=1)

  • Hash Cond: (u.usr_id = p.prs_usr_id)
4. 2,570.815 2,570.815 ↑ 1.0 1,573,586 1

Seq Scan on eif_user u (cost=0.00..317,858.98 rows=1,584,559 width=24) (actual time=0.010..2,570.815 rows=1,573,586 loops=1)

5. 4,168.655 1,759,103.499 ↑ 38.1 17,916 1

Hash (cost=114,004,777.99..114,004,777.99 rows=681,796 width=8) (actual time=1,759,103.499..1,759,103.499 rows=17,916 loops=1)

  • Buckets: 524288 Batches: 4 Memory Usage: 4270kB
6. 1,471.585 1,754,934.844 ↑ 38.1 17,916 1

Merge Join (cost=19.90..114,004,777.99 rows=681,796 width=8) (actual time=6,663.393..1,754,934.844 rows=17,916 loops=1)

  • Merge Cond: (r.fk_person_id = p.prs_id)
7. 12,801.090 1,592,117.806 ↑ 38.1 17,916 1

GroupAggregate (cost=11.35..113,139,997.02 rows=681,796 width=40) (actual time=6,661.726..1,592,117.806 rows=17,916 loops=1)

  • Group Key: r.fk_person_id
  • Filter: ('reject'::text = ALL ((array_agg(DISTINCT r.status))::text[]))
  • Rows Removed by Filter: 683119
8. 100,967.513 1,579,316.716 ↑ 5.4 6,980,645 1

Subquery Scan on r (cost=11.35..109,216,642.12 rows=37,529,059 width=13) (actual time=0.024..1,579,316.716 rows=6,980,645 loops=1)

  • Filter: (r.n <= 10)
  • Rows Removed by Filter: 105587220
9. 234,709.398 1,478,349.203 ↑ 1.0 112,567,865 1

WindowAgg (cost=11.35..81,069,848.12 rows=112,587,176 width=29) (actual time=0.022..1,478,349.203 rows=112,567,865 loops=1)

10. 1,243,639.805 1,243,639.805 ↑ 1.0 112,567,865 1

Index Scan using eic_comm_eic_person_unique_index on eic_comm_eic_person (cost=11.35..41,664,336.52 rows=112,587,176 width=21) (actual time=0.015..1,243,639.805 rows=112,567,865 loops=1)

11. 161,345.453 161,345.453 ↑ 1.0 1,572,503 1

Index Scan using eic_person_pkey on eic_person p (cost=8.55..479,158.77 rows=1,576,280 width=8) (actual time=0.017..161,345.453 rows=1,572,503 loops=1)

Planning time : 0.588 ms
Execution time : 1,763,563.769 ms