explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DRMx

Settings
# exclusive inclusive rows x rows loops node
1. 0.383 64,358.416 ↓ 20.0 20 1

Limit (cost=6,833.44..6,833.45 rows=1 width=612) (actual time=64,357.890..64,358.416 rows=20 loops=1)

2. 159.028 64,358.033 ↓ 20.0 20 1

Sort (cost=6,833.44..6,833.45 rows=1 width=612) (actual time=64,357.860..64,358.033 rows=20 loops=1)

  • Sort Key: t0.created_at DESC
  • Sort Method: top-N heapsort Memory: 66kB
3. 381.036 64,199.005 ↓ 6,391.0 6,391 1

GroupAggregate (cost=6,833.35..6,833.43 rows=1 width=612) (actual time=63,672.942..64,199.005 rows=6,391 loops=1)

  • Group Key: t0.id, o.id
4. 448.857 63,817.969 ↓ 8,878.0 8,878 1

Sort (cost=6,833.35..6,833.36 rows=1 width=674) (actual time=63,672.858..63,817.969 rows=8,878 loops=1)

  • Sort Key: t0.id, o.id
  • Sort Method: external merge Disk: 6104kB
5. 884.941 63,369.112 ↓ 8,878.0 8,878 1

Nested Loop (cost=5,033.80..6,833.34 rows=1 width=674) (actual time=829.642..63,369.112 rows=8,878 loops=1)

6. 476.202 4,519.709 ↓ 8,878.0 8,878 1

Nested Loop (cost=3,397.17..5,196.68 rows=1 width=642) (actual time=826.147..4,519.709 rows=8,878 loops=1)

7. 590.985 3,617.363 ↓ 253.7 8,878 1

Nested Loop (cost=3,396.88..5,183.36 rows=35 width=601) (actual time=826.091..3,617.363 rows=8,878 loops=1)

8. 475.504 2,413.796 ↓ 44.6 8,878 1

Nested Loop (cost=3,396.45..4,547.63 rows=199 width=605) (actual time=826.025..2,413.796 rows=8,878 loops=1)

9. 368.331 1,680.830 ↓ 44.6 8,878 1

Hash Join (cost=3,396.16..4,387.91 rows=199 width=593) (actual time=825.966..1,680.830 rows=8,878 loops=1)

  • Hash Cond: (((t0.metadata ->> 'order_id'::text))::integer = od.order_id)
10. 308.134 727.296 ↓ 7.7 6,391 1

Hash Join (cost=1,687.80..2,500.13 rows=835 width=722) (actual time=240.686..727.296 rows=6,391 loops=1)

  • Hash Cond: (((t0.metadata ->> 'order_id'::text))::integer = o.id)
11. 178.601 178.601 ↓ 1.0 6,391 1

Seq Scan on users_app_notification t0 (cost=0.00..729.76 rows=6,351 width=694) (actual time=0.043..178.601 rows=6,391 loops=1)

  • Filter: ((status = ANY ('{1,2}'::integer[])) AND (users_id = 70) AND (COALESCE((metadata ->> 'is_chat_noti'::text), 'false'::text) <> 'true'::text))
  • Rows Removed by Filter: 5
12. 109.340 240.561 ↓ 1.0 7,908 1

Hash (cost=1,590.82..1,590.82 rows=7,759 width=28) (actual time=240.561..240.561 rows=7,908 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 559kB
13. 130.434 131.221 ↓ 1.0 7,908 1

Bitmap Heap Scan on "order" o (cost=175.05..1,590.82 rows=7,759 width=28) (actual time=0.945..131.221 rows=7,908 loops=1)

  • Recheck Cond: (ninushi_id = ANY ((current_setting('shipper.id'::text))::integer[]))
  • Heap Blocks: exact=816
14. 0.787 0.787 ↓ 1.0 7,908 1

Bitmap Index Scan on order_ninushi_id_idx (cost=0.00..173.11 rows=7,759 width=0) (actual time=0.787..0.787 rows=7,908 loops=1)

  • Index Cond: (ninushi_id = ANY ((current_setting('shipper.id'::text))::integer[]))
15. 212.754 585.203 ↑ 1.0 11,083 1

Hash (cost=1,568.69..1,568.69 rows=11,173 width=18) (actual time=585.203..585.203 rows=11,083 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 735kB
16. 371.941 372.449 ↑ 1.0 11,083 1

Bitmap Heap Scan on order_detail od (cost=237.50..1,568.69 rows=11,173 width=18) (actual time=0.663..372.449 rows=11,083 loops=1)

  • Recheck Cond: (ninushi_id = ANY ((current_setting('shipper.id'::text))::integer[]))
  • Heap Blocks: exact=707
17. 0.508 0.508 ↑ 1.0 11,083 1

Bitmap Index Scan on order_detail_ninushi_id_idx (cost=0.00..234.71 rows=11,173 width=0) (actual time=0.508..0.508 rows=11,083 loops=1)

  • Index Cond: (ninushi_id = ANY ((current_setting('shipper.id'::text))::integer[]))
18. 257.462 257.462 ↑ 1.0 1 8,878

Index Scan using cart_pkey on cart c (cost=0.29..0.80 rows=1 width=20) (actual time=0.029..0.029 rows=1 loops=8,878)

  • Index Cond: (id = od.cart_id)
19. 612.582 612.582 ↑ 1.0 1 8,878

Index Scan using zaiko_orgid_ver_idx on zaiko zk (cost=0.43..3.18 rows=1 width=20) (actual time=0.044..0.069 rows=1 loops=8,878)

  • Index Cond: ((orgid = c.zaiko_id) AND (ver = c.zaiko_ver))
  • Filter: (ninushi_id = ANY ((current_setting('shipper.id'::text))::integer[]))
20. 426.144 426.144 ↑ 1.0 1 8,878

Index Scan using catalog_pkey on catalog ctl (cost=0.29..0.38 rows=1 width=53) (actual time=0.048..0.048 rows=1 loops=8,878)

  • Index Cond: (id = zk.catalog_id)
  • Filter: (ninushi_id = ANY ((current_setting('shipper.id'::text))::integer[]))
21. 834.532 57,964.462 ↑ 1.0 1 8,878

Aggregate (cost=1,636.63..1,636.64 rows=1 width=32) (actual time=6.516..6.529 rows=1 loops=8,878)

22. 1,677.942 57,129.930 ↓ 2.0 2 8,878

Nested Loop (cost=173.40..1,636.63 rows=1 width=8) (actual time=5.965..6.435 rows=2 loops=8,878)

23. 47,674.860 54,209.068 ↑ 1.5 2 8,878

Bitmap Heap Scan on "order" cancel_order (cost=173.11..1,608.28 rows=3 width=8) (actual time=5.830..6.106 rows=2 loops=8,878)

  • Recheck Cond: (ninushi_id = ANY ((current_setting('shipper.id'::text))::integer[]))
  • Filter: (canceled_from > 0)
  • Rows Removed by Filter: 7906
  • Heap Blocks: exact=7244448
24. 6,534.208 6,534.208 ↓ 1.0 7,908 8,878

Bitmap Index Scan on order_ninushi_id_idx (cost=0.00..173.11 rows=7,759 width=0) (actual time=0.736..0.736 rows=7,908 loops=8,878)

  • Index Cond: (ninushi_id = ANY ((current_setting('shipper.id'::text))::integer[]))
25. 1,242.920 1,242.920 ↑ 1.0 1 17,756

Index Scan using order_detail_order_id_idx on order_detail cancel_detail (cost=0.29..9.44 rows=1 width=16) (actual time=0.052..0.070 rows=1 loops=17,756)

  • Index Cond: (order_id = cancel_order.id)
  • Filter: (ninushi_id = ANY ((current_setting('shipper.id'::text))::integer[]))
Planning time : 2.926 ms
Execution time : 64,372.110 ms