explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9K0h

Settings
# exclusive inclusive rows x rows loops node
1. 24.239 2,527.449 ↓ 1.0 55,000 1

Unique (cost=283,680.10..287,531.09 rows=53,117 width=330) (actual time=2,469.662..2,527.449 rows=55,000 loops=1)

2. 197.003 2,503.210 ↓ 1.0 55,000 1

Sort (cost=283,680.10..283,812.90 rows=53,117 width=330) (actual time=2,469.661..2,503.210 rows=55,000 loops=1)

  • Sort Key: "*SELECT* 1".product_id, "*SELECT* 1".row_id, "*SELECT* 1".product_uom_qty, "*SELECT* 1".name, "*SELECT* 1".router_id, "*SELECT* 1".date, "*SELECT* 1".picking_done_date, "*SELECT* 1".funnel_id, "*SELECT* 1".product_group_id, "*SELECT* 1".product_group_name, "*SELECT* 1".brand_id, "*SELECT* 1".brand_name, "*SELECT* 1".revenue, "*SELECT* 1".revenue_with_tax, "*SELECT* 1".original_amount, ((0)::double precision), "*SELECT* 1".pages, "*SELECT* 1".checkout_line, "*SELECT* 1".upsell_line, "*SELECT* 1".id, "*SELECT* 1".line_count, "*SELECT* 1".cancel_order_id, "*SELECT* 1".go_id, "*SELECT* 1".line_id, "*SELECT* 1".medium_id, "*SELECT* 1".source_id, "*SELECT* 1".country_id, "*SELECT* 1".payment_acquirer_id
  • Sort Method: external merge Disk: 10672kB
3. 2.756 2,306.207 ↓ 1.0 55,000 1

Append (cost=232,680.30..271,338.75 rows=53,117 width=330) (actual time=1,024.905..2,306.207 rows=55,000 loops=1)

4. 6.586 1,076.135 ↓ 1.0 55,000 1

Subquery Scan on *SELECT* 1 (cost=232,680.30..234,934.93 rows=53,050 width=268) (actual time=1,024.905..1,076.135 rows=55,000 loops=1)

5. 37.550 1,069.549 ↓ 1.0 55,000 1

WindowAgg (cost=232,680.30..234,271.80 rows=53,050 width=333) (actual time=1,024.904..1,069.549 rows=55,000 loops=1)

6. 44.463 1,031.999 ↓ 1.0 55,000 1

Sort (cost=232,680.30..232,812.93 rows=53,050 width=258) (actual time=1,024.895..1,031.999 rows=55,000 loops=1)

  • Sort Key: s.id, l.id
  • Sort Method: external sort Disk: 10448kB
7. 27.135 987.536 ↓ 1.0 55,000 1

WindowAgg (cost=220,925.19..221,986.19 rows=53,050 width=258) (actual time=953.632..987.536 rows=55,000 loops=1)

8. 44.375 960.401 ↓ 1.0 55,000 1

Sort (cost=220,925.19..221,057.81 rows=53,050 width=250) (actual time=953.624..960.401 rows=55,000 loops=1)

  • Sort Key: s.id, (CASE WHEN l.is_checkout THEN '01 CHECKOUT'::text WHEN l.is_upsale_order_line THEN '02 UPSELL'::text ELSE '03 DIRECT SALES'::text END)
  • Sort Method: external sort Disk: 9800kB
9. 60.331 916.026 ↓ 1.0 55,000 1

Finalize GroupAggregate (cost=202,326.33..210,413.07 rows=53,050 width=250) (actual time=814.934..916.026 rows=55,000 loops=1)

  • Group Key: s.id, l.id, (COALESCE(CASE WHEN l.is_checkout THEN '01 CHECKOUT'::text WHEN l.is_upsale_order_line THEN '02 UPSELL'::text ELSE '03 DIRECT SALES'::text END)), um.name, us.name
10. 0.000 855.695 ↓ 1.2 55,000 1

Gather Merge (cost=202,326.33..208,534.23 rows=44,208 width=218) (actual time=814.920..855.695 rows=55,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 54.609 2,155.071 ↑ 1.2 18,333 3

Partial GroupAggregate (cost=201,326.30..202,431.50 rows=22,104 width=218) (actual time=697.858..718.357 rows=18,333 loops=3)

  • Group Key: s.id, l.id, (COALESCE(CASE WHEN l.is_checkout THEN '01 CHECKOUT'::text WHEN l.is_upsale_order_line THEN '02 UPSELL'::text ELSE '03 DIRECT SALES'::text END)), um.name, us.name
12. 154.176 2,100.462 ↑ 1.2 18,333 3

Sort (cost=201,326.30..201,381.56 rows=22,104 width=174) (actual time=697.841..700.154 rows=18,333 loops=3)

  • Sort Key: s.id, l.id, (COALESCE(CASE WHEN l.is_checkout THEN '01 CHECKOUT'::text WHEN l.is_upsale_order_line THEN '02 UPSELL'::text ELSE '03 DIRECT SALES'::text END)), um.name, us.name
  • Sort Method: external sort Disk: 2584kB
13. 27.132 1,946.286 ↑ 1.2 18,333 3

Hash Left Join (cost=17.49..197,841.28 rows=22,104 width=174) (actual time=12.623..648.762 rows=18,333 loops=3)

  • Hash Cond: (s.medium_id = um.id)
14. 25.665 1,917.870 ↑ 1.2 18,333 3

Hash Left Join (cost=9.07..197,773.81 rows=22,104 width=119) (actual time=12.154..639.290 rows=18,333 loops=3)

  • Hash Cond: (s.source_id = us.id)
15. 65.211 1,891.200 ↑ 1.2 18,333 3

Nested Loop (cost=0.87..197,706.52 rows=22,104 width=113) (actual time=11.656..630.400 rows=18,333 loops=3)

16. 340.989 340.989 ↑ 1.2 18,333 3

Parallel Index Scan using sale_order_confirmation_date_pst_tz_index on sale_order s (cost=0.43..26,415.57 rows=22,410 width=83) (actual time=0.088..113.663 rows=18,333 loops=3)

  • Index Cond: (confirmation_date_pst_tz = '2019-11-06'::date)
  • Filter: ((NOT test_order) AND ((state)::text <> ALL ('{draft,sent}'::text[])))
17. 1,485.000 1,485.000 ↑ 1.0 1 55,000

Index Scan using sale_order_line_order_id_index on sale_order_line l (cost=0.43..7.63 rows=1 width=34) (actual time=0.027..0.027 rows=1 loops=55,000)

  • Index Cond: (order_id = s.id)
18. 0.342 1.005 ↑ 1.0 231 3

Hash (cost=5.31..5.31 rows=231 width=14) (actual time=0.335..0.335 rows=231 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
19. 0.663 0.663 ↑ 1.0 231 3

Seq Scan on utm_source us (cost=0.00..5.31 rows=231 width=14) (actual time=0.140..0.221 rows=231 loops=3)

20. 0.360 1.284 ↑ 1.0 241 3

Hash (cost=5.41..5.41 rows=241 width=31) (actual time=0.428..0.428 rows=241 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
21. 0.924 0.924 ↑ 1.0 241 3

Seq Scan on utm_medium um (cost=0.00..5.41 rows=241 width=31) (actual time=0.125..0.308 rows=241 loops=3)

22. 0.000 1,227.316 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=36,392.74..36,403.82 rows=67 width=268) (actual time=1,227.316..1,227.316 rows=0 loops=1)

23. 0.000 1,227.316 ↓ 0.0 0 1

Finalize GroupAggregate (cost=36,392.74..36,402.64 rows=67 width=301) (actual time=1,227.316..1,227.316 rows=0 loops=1)

  • Group Key: s_1.id, ail.product_id, av.date_invoice, um_1.name, us_1.name
24. 0.000 1,227.352 ↓ 0.0 0 1

Gather Merge (cost=36,392.74..36,400.18 rows=56 width=160) (actual time=1,227.314..1,227.352 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
25. 0.006 2,452.137 ↓ 0.0 0 3

Partial GroupAggregate (cost=35,392.72..35,393.70 rows=28 width=160) (actual time=817.378..817.379 rows=0 loops=3)

  • Group Key: s_1.id, ail.product_id, av.date_invoice, um_1.name, us_1.name
26. 0.258 2,452.131 ↓ 0.0 0 3

Sort (cost=35,392.72..35,392.79 rows=28 width=132) (actual time=817.377..817.377 rows=0 loops=3)

  • Sort Key: s_1.id, ail.product_id, av.date_invoice, um_1.name, us_1.name
  • Sort Method: quicksort Memory: 25kB
27. 0.003 2,451.873 ↓ 0.0 0 3

Nested Loop (cost=8,181.01..35,392.04 rows=28 width=132) (actual time=817.291..817.291 rows=0 loops=3)

28. 0.003 2,451.870 ↓ 0.0 0 3

Hash Left Join (cost=8,180.59..35,272.24 rows=17 width=120) (actual time=817.290..817.290 rows=0 loops=3)

  • Hash Cond: (s_1.medium_id = um_1.id)
29. 0.003 2,451.867 ↓ 0.0 0 3

Hash Left Join (cost=8,172.16..35,263.77 rows=17 width=97) (actual time=817.289..817.289 rows=0 loops=3)

  • Hash Cond: (s_1.source_id = us_1.id)
30. 20.517 2,451.864 ↓ 0.0 0 3

Hash Join (cost=8,163.97..35,255.53 rows=17 width=91) (actual time=817.288..817.288 rows=0 loops=3)

  • Hash Cond: (s_1.id = av.sale_order_id)
31. 1,481.757 1,481.757 ↑ 1.2 18,333 3

Parallel Index Scan using sale_order_confirmation_date_pst_tz_index on sale_order s_1 (cost=0.43..26,359.21 rows=22,542 width=83) (actual time=34.262..493.919 rows=18,333 loops=3)

  • Index Cond: (confirmation_date_pst_tz = '2019-11-06'::date)
  • Filter: (NOT test_order)
32. 18.378 949.590 ↑ 1.1 11,620 3

Hash (cost=8,007.53..8,007.53 rows=12,480 width=12) (actual time=316.530..316.530 rows=11,620 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 628kB
33. 931.212 931.212 ↑ 1.1 11,689 3

Index Scan using account_invoice_type_index on account_invoice av (cost=0.42..8,007.53 rows=12,480 width=12) (actual time=10.347..310.404 rows=11,689 loops=3)

  • Index Cond: ((type)::text = 'out_refund'::text)
  • Filter: ((state)::text = 'paid'::text)
  • Rows Removed by Filter: 968
34. 0.000 0.000 ↓ 0.0 0

Hash (cost=5.31..5.31 rows=231 width=14) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Seq Scan on utm_source us_1 (cost=0.00..5.31 rows=231 width=14) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Hash (cost=5.41..5.41 rows=241 width=31) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Seq Scan on utm_medium um_1 (cost=0.00..5.41 rows=241 width=31) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Index Scan using account_invoice_line_invoice_id_index on account_invoice_line ail (cost=0.42..7.03 rows=2 width=20) (never executed)

  • Index Cond: (invoice_id = av.id)
Planning time : 624.609 ms
Execution time : 2,534.912 ms