explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Kmq1

Settings
# exclusive inclusive rows x rows loops node
1. 0.268 387,360.233 ↑ 1.0 500 1

Nested Loop (cost=136,761.91..138,058.84 rows=500 width=549) (actual time=387,355.916..387,360.233 rows=500 loops=1)

  • Buffers: shared hit=18,466 read=10,490,511
2. 0.383 387,355.965 ↑ 1.0 500 1

HashAggregate (cost=136,761.34..136,766.34 rows=500 width=8) (actual time=387,355.842..387,355.965 rows=500 loops=1)

  • Group Key: sr.id
  • Buffers: shared hit=15,966 read=10,490,511
3. 0.044 387,355.582 ↑ 1.0 500 1

Limit (cost=2.16..136,755.09 rows=500 width=8) (actual time=387,354.910..387,355.582 rows=500 loops=1)

  • Buffers: shared hit=15,966 read=10,490,511
4.          

Initplan (for Limit)

5. 0.025 0.025 ↑ 1.0 1 1

Index Scan using app_property_pkey on app_property (cost=0.14..2.16 rows=1 width=1) (actual time=0.024..0.025 rows=1 loops=1)

  • Index Cond: ((property_name)::text = 'rp.sc.common.enable_upload_operations_to_main_office'::text)
  • Buffers: shared hit=2
6. 209.348 387,355.513 ↑ 105,786.3 500 1

Seq Scan on session_rpi sr (cost=0.00..14,466,591,485.64 rows=52,893,169 width=8) (actual time=387,354.906..387,355.513 rows=500 loops=1)

  • Filter: ((created_at < '2019-05-01 00:00:00+03'::timestamp with time zone) AND ((NOT $0) OR (NOT (alternatives: SubPlan 2 or hashed SubPlan 3))))
  • Buffers: shared hit=15,966 read=10,490,511
7.          

SubPlan (for Seq Scan)

8. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.00..204.89 rows=1 width=0) (never executed)

  • Filter: ((o.register_main_office_id IS NULL) OR (register.response_date IS NULL))
9. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_rpi_operation_rpi_id on rpi_operation o (cost=0.57..35.49 rows=69 width=8) (never executed)

  • Index Cond: (sr.id = rpi_id)
10. 0.000 0.000 ↓ 0.0 0

Index Scan using register_operation_main_office_pkey on register_operation_main_office register (cost=0.43..2.45 rows=1 width=16) (never executed)

  • Index Cond: (o.register_main_office_id = id)
11. 163,802.676 387,146.165 ↑ 730.8 6,384 1

Hash Left Join (cost=44,595.26..15,935,331.30 rows=4,665,527 width=8) (actual time=290,134.072..387,146.165 rows=6,384 loops=1)

  • Hash Cond: (o_1.register_main_office_id = register_1.id)
  • Filter: ((o_1.register_main_office_id IS NULL) OR (register_1.response_date IS NULL))
  • Rows Removed by Filter: 427,733,828
  • Buffers: shared hit=15,953 read=10,490,511
12. 222,536.172 222,536.172 ↑ 1.0 427,740,212 1

Seq Scan on rpi_operation o_1 (cost=0.00..14,767,914.24 rows=427,740,224 width=16) (actual time=0.103..222,536.172 rows=427,740,212 loops=1)

  • Buffers: shared hit=1 read=10,490,511
13. 516.251 807.317 ↑ 1.0 1,273,034 1

Hash (cost=28,682.34..28,682.34 rows=1,273,034 width=16) (actual time=807.316..807.317 rows=1,273,034 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 76,058kB
  • Buffers: shared hit=15,952
14. 291.066 291.066 ↑ 1.0 1,273,034 1

Seq Scan on register_operation_main_office register_1 (cost=0.00..28,682.34 rows=1,273,034 width=16) (actual time=0.029..291.066 rows=1,273,034 loops=1)

  • Buffers: shared hit=15,952
15. 4.000 4.000 ↑ 1.0 1 500

Index Scan using session_rpi_pkey on session_rpi (cost=0.57..2.58 rows=1 width=549) (actual time=0.008..0.008 rows=1 loops=500)

  • Index Cond: (id = sr.id)
  • Buffers: shared hit=2,500
Planning time : 1.647 ms
Execution time : 387,411.396 ms