explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qSg

Settings
# exclusive inclusive rows x rows loops node
1. 6,031.129 134,619.811 ↑ 2.7 18,656,085 1

Unique (cost=23,761,155.87..25,801,218.59 rows=51,001,568 width=124) (actual time=123,894.096..134,619.811 rows=18,656,085 loops=1)

2. 21,967.053 128,588.682 ↑ 2.5 20,728,403 1

Sort (cost=23,761,155.87..23,888,659.79 rows=51,001,568 width=124) (actual time=123,894.095..128,588.682 rows=20,728,403 loops=1)

  • Sort Key: challans.id, challans.remark, challans.challan_no, challans.challan_doc_no, challans.date_time, challans.challan_place, challans.ac
  • Sort Method: external merge Disk: 3078960kB
3. 5,145.973 106,621.629 ↑ 2.5 20,728,403 1

Hash Left Join (cost=3,666,025.43..14,866,127.87 rows=51,001,568 width=124) (actual time=23,029.508..106,621.629 rows=20,728,403 loops=1)

  • Hash Cond: (challans.officer_id = users.id)
4. 78,478.829 101,424.847 ↑ 1.0 20,728,403 1

Hash Left Join (cost=3,659,115.74..14,602,239.01 rows=20,794,828 width=113) (actual time=22,978.265..101,424.847 rows=20,728,403 loops=1)

  • Hash Cond: (challans.id = challan_status.challan_id)
  • -> Hash Right Join (cost=3096225.67..12786615.06 rows=18141057 width=104) (actual time=16619.994..76730.917 rows=18008143 loops
  • Hash Cond: ((challan_accused.challan_id = challans.id) AND ((challan_accused.type)::text = (challans.accused_type)::text))
  • -> Seq Scan on challan_accused (cost=0.00..8245609.87 rows=62310956 width=25) (actual time=2.170..26018.553 rows=60855747
5. 16,613.779 16,613.779 ↑ 1.0 18,008,140 1

Hash (cost=2,685,358.21..2,685,358.21 rows=18,141,057 width=90) (actual time=16,613.778..16,613.779 rows=18,008,140 loops=1)

  • Buckets: 1048576 Batches: 32 Memory Usage: 76348kB
  • -> Seq Scan on challans (cost=0.00..2685358.21 rows=18141057 width=90) (actual time=0.015..11513.124 rows=18008140
  • Filter: (is_active = 1)
  • Rows Removed by Filter: 64398
6. 6,332.239 6,332.239 ↑ 1.0 20,799,989 1

Hash (cost=346,244.32..346,244.32 rows=20,865,773 width=13) (actual time=6,332.238..6,332.239 rows=20,799,989 loops=1)

  • Buckets: 4194304 Batches: 16 Memory Usage: 93794kB
  • -> Seq Scan on challan_status (cost=0.00..346244.32 rows=20865773 width=13) (actual time=0.024..3517.019 rows=20799989 lo
7. 6.805 50.809 ↑ 2.4 37,105 1

Hash (cost=6,410.10..6,410.10 rows=90,835 width=11) (actual time=50.809..50.809 rows=37,105 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 2705kB
8. 39.457 44.004 ↑ 2.4 37,105 1

Merge Left Join (cost=138.45..6,410.10 rows=90,835 width=11) (actual time=3.147..44.004 rows=37,105 loops=1)

  • Merge Cond: ((users.location)::text = (((rto.id)::character varying)::text))
  • -> Index Scan using users_location_idx on users (cost=0.29..4813.31 rows=37036 width=8) (actual time=0.031..31.751 rows=3
9. 4.198 4.547 ↓ 19.3 38,105 1

Sort (cost=138.16..143.11 rows=1,977 width=7) (actual time=3.108..4.547 rows=38,105 loops=1)

  • Sort Key: (((rto.id)::character varying)::text)
  • Sort Method: quicksort Memory: 141kB
10. 0.349 0.349 ↑ 1.0 1,977 1

Seq Scan on rto (cost=0.00..29.93 rows=1,977 width=7) (actual time=0.013..0.349 rows=1,977 loops=1)

Planning time : 1.329 ms
Execution time : 135,687.744 ms