explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DnjX

Settings
# exclusive inclusive rows x rows loops node
1. 0.388 6,956.703 ↓ 20.0 20 1

Limit (cost=5,578.14..5,578.14 rows=1 width=612) (actual time=6,956.162..6,956.703 rows=20 loops=1)

2.          

CTE tmp

3. 0.058 15.543 ↑ 1.0 1 1

Aggregate (cost=1,636.63..1,636.64 rows=1 width=32) (actual time=15.533..15.543 rows=1 loops=1)

4. 0.108 15.485 ↓ 2.0 2 1

Nested Loop (cost=173.40..1,636.63 rows=1 width=8) (actual time=15.233..15.485 rows=2 loops=1)

5. 14.806 15.295 ↑ 1.5 2 1

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

  • Recheck Cond: (ninushi_id = ANY ((current_setting('shipper.id'::text))::integer[]))
  • Filter: (canceled_from > 0)
  • Rows Removed by Filter: 7906
  • Heap Blocks: exact=816
6. 0.489 0.489 ↓ 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.489..0.489 rows=7,908 loops=1)

  • Index Cond: (ninushi_id = ANY ((current_setting('shipper.id'::text))::integer[]))
7. 0.082 0.082 ↑ 1.0 1 2

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.030..0.041 rows=1 loops=2)

  • Index Cond: (order_id = cancel_order.id)
  • Filter: (ninushi_id = ANY ((current_setting('shipper.id'::text))::integer[]))
8. 148.241 6,956.315 ↓ 20.0 20 1

Sort (cost=3,941.50..3,941.50 rows=1 width=612) (actual time=6,956.140..6,956.315 rows=20 loops=1)

  • Sort Key: t0.created_at DESC
  • Sort Method: top-N heapsort Memory: 66kB
9. 462.462 6,808.074 ↓ 6,391.0 6,391 1

GroupAggregate (cost=3,941.40..3,941.49 rows=1 width=612) (actual time=6,180.156..6,808.074 rows=6,391 loops=1)

  • Group Key: t0.id, o.id
10. 388.027 6,345.612 ↓ 8,878.0 8,878 1

Sort (cost=3,941.40..3,941.41 rows=1 width=674) (actual time=6,180.069..6,345.612 rows=8,878 loops=1)

  • Sort Key: t0.id, o.id
  • Sort Method: external merge Disk: 6104kB
11. 770.982 5,957.585 ↓ 8,878.0 8,878 1

Nested Loop (cost=3,000.58..3,941.39 rows=1 width=674) (actual time=682.558..5,957.585 rows=8,878 loops=1)

12. 752.935 4,795.971 ↓ 8,878.0 8,878 1

Nested Loop (cost=3,000.58..3,941.36 rows=1 width=642) (actual time=666.972..4,795.971 rows=8,878 loops=1)

13. 627.411 3,670.160 ↓ 1,775.6 8,878 1

Nested Loop (cost=3,000.29..3,939.46 rows=5 width=601) (actual time=666.900..3,670.160 rows=8,878 loops=1)

14. 490.028 2,332.509 ↓ 341.5 8,878 1

Nested Loop (cost=2,999.87..3,856.40 rows=26 width=605) (actual time=666.833..2,332.509 rows=8,878 loops=1)

15. 432.742 1,558.385 ↓ 341.5 8,878 1

Hash Join (cost=2,999.57..3,835.53 rows=26 width=593) (actual time=666.751..1,558.385 rows=8,878 loops=1)

  • Hash Cond: (((t0.metadata ->> 'order_id'::text))::integer = od.order_id)
16. 278.912 683.701 ↓ 58.1 6,391 1

Hash Join (cost=1,291.22..2,103.54 rows=110 width=722) (actual time=224.672..683.701 rows=6,391 loops=1)

  • Hash Cond: (((t0.metadata ->> 'order_id'::text))::integer = o.id)
17. 180.204 180.204 ↓ 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.036..180.204 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
18. 113.673 224.585 ↓ 7.7 7,908 1

Hash (cost=1,278.46..1,278.46 rows=1,021 width=28) (actual time=224.585..224.585 rows=7,908 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 559kB
19. 110.368 110.912 ↓ 7.7 7,908 1

Bitmap Heap Scan on "order o (cost=53.36..1,278.46 rows=1,021 width=28) (actual time=0.688..110.912 rows=7,908 loops=1)

  • Recheck Cond: ((ninushi_id = ANY ((current_setting('shipper.id'::text))::integer[])) AND (ninushi_id = 3))
  • Heap Blocks: exact=816
20. 0.544 0.544 ↓ 7.7 7,908 1

Bitmap Index Scan on order_ninushi_id_idx (cost=0.00..53.11 rows=1,021 width=0) (actual time=0.544..0.544 rows=7,908 loops=1)

  • Index Cond: ((ninushi_id = ANY ((current_setting('shipper.id'::text))::integer[])) AND (ninushi_id = 3))
21. 210.670 441.942 ↑ 1.0 11,083 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 735kB
22. 230.421 231.272 ↑ 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.954..231.272 rows=11,083 loops=1)

  • Recheck Cond: (ninushi_id = ANY ((current_setting('shipper.id'::text))::integer[]))
  • Heap Blocks: exact=707
23. 0.851 0.851 ↑ 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.851..0.851 rows=11,083 loops=1)

  • Index Cond: (ninushi_id = ANY ((current_setting('shipper.id'::text))::integer[]))
24. 284.096 284.096 ↑ 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.032..0.032 rows=1 loops=8,878)

  • Index Cond: (id = od.cart_id)
25. 710.240 710.240 ↑ 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.054..0.080 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[]))
26. 372.876 372.876 ↑ 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.042..0.042 rows=1 loops=8,878)

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

CTE Scan on tmp sub_cancel_query (cost=0.00..0.02 rows=1 width=32) (actual time=0.024..0.044 rows=1 loops=8,878)

Planning time : 8.486 ms
Execution time : 6,959.052 ms