explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gGtx

Settings
# exclusive inclusive rows x rows loops node
1. 7.588 179.097 ↓ 1,335.0 1,335 1

Subquery Scan on sub (cost=1,121.93..1,875.92 rows=1 width=72) (actual time=135.835..179.097 rows=1,335 loops=1)

  • Buffers: shared hit=40145
2. 0.549 136.799 ↓ 1,335.0 1,335 1

Unique (cost=1,121.93..1,121.93 rows=1 width=20) (actual time=135.751..136.799 rows=1,335 loops=1)

  • Buffers: shared hit=26663
3. 12.125 136.250 ↓ 2,670.0 2,670 1

Sort (cost=1,121.93..1,121.93 rows=1 width=20) (actual time=135.749..136.25 rows=2,670 loops=1)

  • Sort Key: sao.external_ad_id
  • Sort Method: quicksort Memory: 305kB
  • Buffers: shared hit=26663
4. 0.475 124.125 ↓ 2,670.0 2,670 1

Nested Loop (cost=43.07..1,121.92 rows=1 width=20) (actual time=18.779..124.125 rows=2,670 loops=1)

  • Buffers: shared hit=26663
5. 0.008 0.262 ↓ 2.0 2 1

Nested Loop (cost=10.39..22.44 rows=1 width=8) (actual time=0.256..0.262 rows=2 loops=1)

  • Buffers: shared hit=15
6. 0.019 0.019 ↑ 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.016..0.019 rows=1 loops=1)

  • Index Cond: (cc.client_account_id = 11)
  • Filter: (cc.vertical = 'job'::enum_vertical)
  • Buffers: shared hit=3
7. 0.013 0.235 ↓ 2.0 2 1

Bitmap Heap Scan on client_contract_broadcast ccb (cost=10.11..14.12 rows=1 width=4) (actual time=0.232..0.235 rows=2 loops=1)

  • Filter: (ccb.site_id = 1)
  • Heap Blocks: exact=2
  • Buffers: shared hit=12
8. 0.006 0.222 ↓ 0.0 0 1

BitmapAnd (cost=10.11..10.11 rows=1 width=0) (actual time=0.222..0.222 rows=0 loops=1)

  • Buffers: shared hit=10
9. 0.013 0.013 ↑ 1.0 4 1

Bitmap Index Scan on idx_ccb_client_contract_id (cost=0..4.45 rows=4 width=0) (actual time=0.013..0.013 rows=4 loops=1)

  • Index Cond: (ccb.client_contract_id = cc.client_contract_id)
  • Buffers: shared hit=3
10. 0.203 0.203 ↓ 11.8 1,522 1

Bitmap Index Scan on idx_ccb_partner_id (cost=0..5.39 rows=129 width=0) (actual time=0.203..0.203 rows=1,522 loops=1)

  • Index Cond: (ccb.partner_id = 3)
  • Buffers: shared hit=7
11. 99.566 123.388 ↓ 21.5 1,335 2

Bitmap Heap Scan on stream_ad_online sao (cost=32.67..1,098.7 rows=62 width=24) (actual time=14.168..61.694 rows=1,335 loops=2)

  • Filter: (sao.status = ANY ('{online,pending}'::enum_stream_ad_online_status[]))
  • Heap Blocks: exact=25760
  • Buffers: shared hit=26648
12. 23.822 23.822 ↓ 95.4 161,425 2

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

  • Index Cond: (sao.client_contract_id = ccb.client_contract_id)
  • Buffers: shared hit=888
13.          

SubPlan (for Subquery Scan)

14. 1.335 18.690 ↑ 1.0 1 1,335

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

  • Buffers: shared hit=6741
15. 1.335 17.355 ↑ 93.0 1 1,335

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

  • Sort Key: sa.stream_ad_id DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=6741
16. 16.020 16.020 ↑ 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.011..0.012 rows=1 loops=1,335)

  • Index Cond: (sa.external_ad_id = sub.external_ad_id)
  • Buffers: shared hit=6741
17. 0.000 16.020 ↑ 1.0 1 1,335

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

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

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

  • Sort Key: sa_1.stream_ad_id DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=6741
19. 13.350 13.350 ↑ 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.01..0.01 rows=1 loops=1,335)

  • Index Cond: (sa_1.external_ad_id = sub.external_ad_id)
  • Buffers: shared hit=6741
Planning time : 2.885 ms
Execution time : 179.328 ms