explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g8Mw

Settings
# exclusive inclusive rows x rows loops node
1. 29,225.070 125,510.980 ↑ 2.7 18,664,890 1

HashAggregate (cost=14,754,027.74..14,907,135.47 rows=51,035,910 width=124) (actual time=114,758.174..125,510.980 rows=18,664,890 loops=1)

  • Group Key: challans.id, challans.remark, challans.challan_no, challans.challan_doc_no, challans.date_time, challans.challan_place, challans.accused
2. 4,524.483 96,285.910 ↑ 2.5 20,737,299 1

Hash Left Join (cost=3,298,714.86..12,712,591.34 rows=51,035,910 width=124) (actual time=29,082.552..96,285.910 rows=20,737,299 loops=1)

  • Hash Cond: (challans.officer_id = users.id)
3. 11,175.982 91,712.035 ↑ 1.0 20,737,299 1

Hash Left Join (cost=3,291,803.17..12,448,527.44 rows=20,808,830 width=113) (actual time=29,032.716..91,712.035 rows=20,737,299 loops=1)

  • Hash Cond: (challans.id = challan_status.challan_id)
4. 51,720.755 72,294.897 ↑ 1.0 18,016,767 1

Hash Right Join (cost=2,830,486.67..11,403,229.06 rows=18,141,057 width=104) (actual time=20,680.617..72,294.897 rows=18,016,767 loops=1)

  • 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.160..21367.695 rows=60889321 loops
5. 20,574.142 20,574.142 ↑ 1.0 18,016,764 1

Hash (cost=2,685,358.21..2,685,358.21 rows=18,141,057 width=90) (actual time=20,574.141..20,574.142 rows=18,016,764 loops=1)

  • Buckets: 33554432 Batches: 1 Memory Usage: 2440607kB
  • -> Seq Scan on challans (cost=0.00..2685358.21 rows=18141057 width=90) (actual time=0.089..13612.229 rows=18016764 loops=
  • Filter: (is_active = 1)
  • Rows Removed by Filter: 64415
6. 4,566.522 8,241.156 ↑ 1.0 20,808,902 1

Hash (cost=346,477.47..346,477.47 rows=20,879,823 width=13) (actual time=8,241.156..8,241.156 rows=20,808,902 loops=1)

  • Buckets: 33554432 Batches: 1 Memory Usage: 1237562kB
7. 3,674.634 3,674.634 ↑ 1.0 20,808,902 1

Seq Scan on challan_status (cost=0.00..346,477.47 rows=20,879,823 width=13) (actual time=0.045..3,674.634 rows=20,808,902 loops=1)

8. 6.029 49.392 ↑ 2.4 37,135 1

Hash (cost=6,412.10..6,412.10 rows=90,835 width=11) (actual time=49.392..49.392 rows=37,135 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 2706kB
9. 38.907 43.363 ↑ 2.4 37,135 1

Merge Left Join (cost=138.45..6,412.10 rows=90,835 width=11) (actual time=3.096..43.363 rows=37,135 loops=1)

  • Merge Cond: ((users.location)::text = (((rto.id)::character varying)::text))
  • -> Index Scan using users_location_idx on users (cost=0.29..4815.31 rows=37036 width=8) (actual time=0.035..31.003 rows=37135 l
10. 4.139 4.456 ↓ 19.3 38,135 1

Sort (cost=138.16..143.11 rows=1,977 width=7) (actual time=3.053..4.456 rows=38,135 loops=1)

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

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

Planning time : 2.293 ms
Execution time : 127,264.178 ms