explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hLPv

Settings
# exclusive inclusive rows x rows loops node
1. 8.325 85.871 ↓ 667.5 1,335 1

Result (cost=1,970.07..3,478.06 rows=2 width=76) (actual time=35.616..85.871 rows=1,335 loops=1)

  • Buffers: shared hit=14810 read=176
2.          

Initplan (for Result)

3. 0.015 0.015 ↑ 1.0 1 1

Seq Scan on partner p (cost=0..1.7 rows=1 width=4) (actual time=0.013..0.015 rows=1 loops=1)

  • Filter: (p.partner_id = 3)
  • Buffers: shared hit=1
4. 0.028 0.028 ↑ 1.0 1 1

Seq Scan on partner p_1 (cost=0..1.7 rows=1 width=4) (actual time=0.023..0.028 rows=1 loops=1)

  • Filter: (p_1.partner_id = 3)
  • Buffers: shared hit=1
5. 0.458 36.118 ↓ 667.5 1,335 1

Unique (cost=1,966.67..1,966.68 rows=2 width=20) (actual time=35.311..36.118 rows=1,335 loops=1)

  • Buffers: shared hit=1502
6. 7.429 35.660 ↓ 667.5 1,335 1

Sort (cost=1,966.67..1,966.68 rows=2 width=20) (actual time=35.302..35.66 rows=1,335 loops=1)

  • Sort Key: sao.external_ad_id
  • Sort Method: quicksort Memory: 153kB
  • Buffers: shared hit=1502
7. 0.187 28.231 ↓ 667.5 1,335 1

Nested Loop (cost=1,719.38..1,966.66 rows=2 width=20) (actual time=25.91..28.231 rows=1,335 loops=1)

  • Buffers: shared hit=1502
8. 0.030 0.030 ↑ 1.0 1 1

Index Scan using idx_cc_client_account_id on client_contract cc (cost=0.29..8.31 rows=1 width=4) (actual time=0.02..0.03 rows=1 loops=1)

  • Index Cond: (cc.client_account_id = 11)
  • Filter: (cc.vertical = 'job'::enum_vertical)
  • Buffers: shared hit=3
9. 2.386 28.014 ↓ 21.9 1,335 1

Bitmap Heap Scan on stream_ad_online sao (cost=1,719.09..1,957.74 rows=61 width=24) (actual time=25.883..28.014 rows=1,335 loops=1)

  • Filter: (sao.site_id = 1)
  • Heap Blocks: exact=797
  • Buffers: shared hit=1499
10. 0.968 25.628 ↓ 0.0 0 1

BitmapAnd (cost=1,719.09..1,719.09 rows=62 width=0) (actual time=25.628..25.628 rows=0 loops=1)

  • Buffers: shared hit=702
11. 17.788 17.788 ↓ 95.4 161,425 1

Bitmap Index Scan on idx_sao_client_contract_id (cost=0..33.12 rows=1,692 width=0) (actual time=17.788..17.788 rows=161,425 loops=1)

  • Index Cond: (sao.client_contract_id = cc.client_contract_id)
  • Buffers: shared hit=444
12. 6.872 6.872 ↓ 1.0 92,425 1

Bitmap Index Scan on idx_sao_status (cost=0..1,663.2 rows=90,446 width=0) (actual time=6.872..6.872 rows=92,425 loops=1)

  • Index Cond: (sao.status = ANY ('{online,pending}'::enum_stream_ad_online_status[]))
  • Buffers: shared hit=258
13.          

SubPlan (for Result)

14. 1.335 22.695 ↑ 1.0 1 1,335

Limit (cost=376.99..376.99 rows=1 width=16) (actual time=0.017..0.017 rows=1 loops=1,335)

  • Buffers: shared hit=6565 read=176
15. 2.670 21.360 ↑ 93.0 1 1,335

Sort (cost=376.99..377.22 rows=93 width=16) (actual time=0.016..0.016 rows=1 loops=1,335)

  • Sort Key: sa.stream_ad_id DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=6565 read=176
16. 18.690 18.690 ↑ 93.0 1 1,335

Index Scan using idx_stream_ad_external_ad_id on stream_ad sa (cost=0.56..376.52 rows=93 width=16) (actual time=0.014..0.014 rows=1 loops=1,335)

  • Index Cond: (sa.external_ad_id = sao.external_ad_id)
  • Buffers: shared hit=6565 read=176
17. 1.335 18.690 ↑ 1.0 1 1,335

Limit (cost=376.99..376.99 rows=1 width=58) (actual time=0.014..0.014 rows=1 loops=1,335)

  • Buffers: shared hit=6741
18. 2.670 17.355 ↑ 93.0 1 1,335

Sort (cost=376.99..377.22 rows=93 width=58) (actual time=0.013..0.013 rows=1 loops=1,335)

  • Sort Key: sa_1.stream_ad_id DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=6741
19. 14.685 14.685 ↑ 93.0 1 1,335

Index Scan using idx_stream_ad_external_ad_id on stream_ad sa_1 (cost=0.56..376.52 rows=93 width=58) (actual time=0.011..0.011 rows=1 loops=1,335)

  • Index Cond: (sa_1.external_ad_id = sao.external_ad_id)
  • Buffers: shared hit=6741
Planning time : 1.405 ms
Execution time : 86.127 ms