explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wQgx

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 30,951.656 ↓ 0.0 0 1

GroupAggregate (cost=9,789,901.35..9,789,901.37 rows=1 width=16) (actual time=30,951.656..30,951.656 rows=0 loops=1)

  • Group Key: opf.restaurant_id
  • Buffers: shared hit=1480367 read=1409712 written=80424
  • I/O Timings: read=4470.415 write=802.833
2.          

CTE daysforrestaurants

3. 11.539 11.539 ↓ 1.6 12,484 1

Index Scan using day_yyyymmdd on "Day" d (cost=0.43..12,966.57 rows=7,725 width=16) (actual time=0.021..11.539 rows=12,484 loops=1)

  • Index Cond: (yyyymmdd = 20200319)
  • Buffers: shared hit=7534 read=1
  • I/O Timings: read=0.005
4.          

CTE opforselection

5. 31.815 30,058.717 ↓ 135.3 205,695 1

Nested Loop (cost=1,259.88..9,763,832.36 rows=1,520 width=16) (actual time=0.130..30,058.717 rows=205,695 loops=1)

  • Buffers: shared hit=276715 read=1398378 written=80041
  • I/O Timings: read=4401.940 write=799.883
6. 15.366 15.366 ↓ 1.6 12,484 1

CTE Scan on daysforrestaurants dfr (cost=0.00..154.50 rows=7,725 width=16) (actual time=0.022..15.366 rows=12,484 loops=1)

  • Buffers: shared hit=7534 read=1
  • I/O Timings: read=0.005
7. 1,073.624 30,011.536 ↓ 16.0 16 12,484

Bitmap Heap Scan on "OrderPayment" op (cost=1,259.88..1,263.90 rows=1 width=24) (actual time=2.333..2.404 rows=16 loops=12,484)

  • Recheck Cond: ((paiddate_day_id = dfr.id) AND (restaurant_id = dfr.restaurant_id))
  • Heap Blocks: exact=313789
  • Buffers: shared hit=269181 read=1398377 written=80041
  • I/O Timings: read=4401.936 write=799.883
8. 32.078 28,937.912 ↓ 0.0 0 12,484

BitmapAnd (cost=1,259.88..1,259.88 rows=1 width=0) (actual time=2.318..2.318 rows=0 loops=12,484)

  • Buffers: shared hit=65028 read=1288741 written=75659
  • I/O Timings: read=3965.669 write=763.110
9. 74.904 74.904 ↑ 33.0 34 12,484

Bitmap Index Scan on orderpayment__paiddate_day_id (cost=0.00..44.36 rows=1,122 width=0) (actual time=0.006..0.006 rows=34 loops=12,484)

  • Index Cond: (paiddate_day_id = dfr.id)
  • Buffers: shared hit=47454 read=3599 written=174
  • I/O Timings: read=12.877 write=1.309
10. 28,830.930 28,830.930 ↑ 1.2 37,303 5,345

Bitmap Index Scan on orderpayment__restaurant_last4carddigits (cost=0.00..1,215.27 rows=45,100 width=0) (actual time=5.394..5.394 rows=37,303 loops=5,345)

  • Index Cond: (restaurant_id = dfr.restaurant_id)
  • Buffers: shared hit=17574 read=1285142 written=75485
  • I/O Timings: read=3952.793 write=761.801
11. 0.003 30,951.655 ↓ 0.0 0 1

Sort (cost=13,102.41..13,102.42 rows=1 width=8) (actual time=30,951.655..30,951.655 rows=0 loops=1)

  • Sort Key: opf.restaurant_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1480367 read=1409712 written=80424
  • I/O Timings: read=4470.415 write=802.833
12. 20.692 30,951.652 ↓ 0.0 0 1

Nested Loop (cost=0.57..13,102.40 rows=1 width=8) (actual time=30,951.652..30,951.652 rows=0 loops=1)

  • Buffers: shared hit=1480367 read=1409712 written=80424
  • I/O Timings: read=4470.415 write=802.833
13. 30,108.180 30,108.180 ↓ 135.3 205,695 1

CTE Scan on opforselection ofs (cost=0.00..30.40 rows=1,520 width=8) (actual time=0.130..30,108.180 rows=205,695 loops=1)

  • Buffers: shared hit=276715 read=1398378 written=80041
  • I/O Timings: read=4401.940 write=799.883
14. 822.780 822.780 ↓ 0.0 0 205,695

Index Scan using "OrderPayment_pkey" on "OrderPayment" opf (cost=0.57..8.60 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=205,695)

  • Index Cond: (id = ofs.id)
  • Filter: (("tipAmount" >= '400'::numeric) AND (("paymentStatus")::text = 'CAPTURED'::text) AND (("paymentType")::text = 'CREDIT'::text) AND (("tipAmount" / amount) >= 0.3))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1203652 read=11334 written=383
  • I/O Timings: read=68.474 write=2.950
Planning time : 0.486 ms
Execution time : 30,957.359 ms