explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xF4Cb : test

Settings
# exclusive inclusive rows x rows loops node
1. 6,346.159 78,943.631 ↓ 3.9 50,019 1

GroupAggregate (cost=1,684,555.86..1,756,426.53 rows=12,676 width=304) (actual time=72,506.625..78,943.631 rows=50,019 loops=1)

  • Group Key: sv.id, sv.name
2. 368.524 72,597.472 ↑ 1.3 100,654 1

Sort (cost=1,684,555.86..1,684,872.75 rows=126,756 width=92) (actual time=72,506.417..72,597.472 rows=100,654 loops=1)

  • Sort Key: sv.id, sv.name
  • Sort Method: external merge Disk: 8,096kB
3. 16.583 72,228.948 ↑ 1.3 100,654 1

Subquery Scan on sv (cost=96,254.11..1,667,312.71 rows=126,756 width=92) (actual time=295.049..72,228.948 rows=100,654 loops=1)

4. 22.593 72,212.365 ↑ 1.3 100,654 1

Append (cost=96,254.11..1,666,045.15 rows=126,756 width=1,223) (actual time=295.048..72,212.365 rows=100,654 loops=1)

5. 17.982 68,602.031 ↑ 1.9 50,898 1

Result (cost=96,254.11..1,480,122.78 rows=94,400 width=1,223) (actual time=295.048..68,602.031 rows=50,898 loops=1)

6. 4.453 68,584.049 ↑ 1.9 50,898 1

Append (cost=96,254.11..1,478,706.78 rows=94,400 width=1,215) (actual time=295.046..68,584.049 rows=50,898 loops=1)

7. 16.389 68,472.250 ↑ 1.8 50,883 1

Result (cost=96,254.11..1,382,739.13 rows=92,913 width=1,215) (actual time=295.045..68,472.250 rows=50,883 loops=1)

8. 4.459 68,455.861 ↑ 1.8 50,883 1

Append (cost=96,254.11..1,381,577.71 rows=92,913 width=1,211) (actual time=295.044..68,455.861 rows=50,883 loops=1)

9. 19.001 67,638.824 ↑ 1.8 50,753 1

Subquery Scan on *SELECT* 1 (cost=96,254.11..1,020,546.09 rows=90,917 width=997) (actual time=295.043..67,638.824 rows=50,753 loops=1)

10. 17.693 67,619.823 ↑ 1.8 50,753 1

Result (cost=96,254.11..1,019,409.63 rows=90,917 width=993) (actual time=295.042..67,619.823 rows=50,753 loops=1)

  • One-Time Filter: (0 = ANY ('{0,1,2,3}'::integer[]))
11. 58,249.014 67,602.130 ↑ 1.8 50,753 1

Hash Join (cost=96,254.11..1,019,409.63 rows=90,917 width=993) (actual time=295.038..67,602.130 rows=50,753 loops=1)

  • Hash Cond: (l.shopify_order_id = o.id)
12. 9,094.767 9,094.767 ↓ 1.0 5,658,697 1

Seq Scan on shopify_line_items l (cost=0.00..531,646.64 rows=5,619,264 width=244) (actual time=0.009..9,094.767 rows=5,658,697 loops=1)

13. 154.071 258.349 ↓ 1.3 50,013 1

Hash (cost=92,388.33..92,388.33 rows=39,262 width=673) (actual time=258.349..258.349 rows=50,013 loops=1)

  • Buckets: 8,192 Batches: 8 Memory Usage: 3,623kB
14. 104.278 104.278 ↓ 1.3 50,013 1

Index Scan using index_shopify_orders_on_shop_id on shopify_orders o (cost=0.43..92,388.33 rows=39,262 width=673) (actual time=0.017..104.278 rows=50,013 loops=1)

  • Index Cond: (shop_id = 80)
  • Filter: ((created_at >= '2019-06-26 23:00:00'::timestamp without time zone) AND (created_at <= '2019-09-24 22:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 87,313
15. 0.091 812.578 ↑ 15.4 130 1

Subquery Scan on *SELECT* 2 (cost=94,296.72..361,031.62 rows=1,996 width=1,165) (actual time=375.448..812.578 rows=130 loops=1)

16. 0.097 812.487 ↑ 15.4 130 1

Result (cost=94,296.72..361,001.68 rows=1,996 width=1,157) (actual time=375.445..812.487 rows=130 loops=1)

  • One-Time Filter: (2 = ANY ('{0,1,2,3}'::integer[]))
17. 322.291 812.390 ↑ 15.4 130 1

Hash Join (cost=94,296.72..361,001.68 rows=1,996 width=1,157) (actual time=375.438..812.390 rows=130 loops=1)

  • Hash Cond: (l_1.shopify_order_id = o_1.id)
18. 26.333 118.223 ↓ 1.0 36,430 1

Nested Loop (cost=0.43..264,449.94 rows=36,393 width=108) (actual time=0.027..118.223 rows=36,430 loops=1)

19. 19.030 19.030 ↓ 1.0 36,430 1

Seq Scan on shopify_refund_line_items rl (cost=0.00..3,929.09 rows=36,393 width=44) (actual time=0.010..19.030 rows=36,430 loops=1)

  • Filter: ((created_at >= '2019-06-26 23:00:00'::timestamp without time zone) AND (created_at <= '2019-09-24 22:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 84,313
20. 72.860 72.860 ↑ 1.0 1 36,430

Index Scan using shopify_line_items_pkey on shopify_line_items l_1 (cost=0.43..7.16 rows=1 width=72) (actual time=0.002..0.002 rows=1 loops=36,430)

  • Index Cond: (id = rl.shopify_line_item_id)
21. 98.535 371.876 ↓ 1.0 137,326 1

Hash (cost=91,723.02..91,723.02 rows=133,061 width=32) (actual time=371.875..371.876 rows=137,326 loops=1)

  • Buckets: 65,536 Batches: 4 Memory Usage: 2,946kB
22. 273.341 273.341 ↓ 1.0 137,326 1

Index Scan using index_shopify_orders_on_shop_id on shopify_orders o_1 (cost=0.43..91,723.02 rows=133,061 width=32) (actual time=0.020..273.341 rows=137,326 loops=1)

  • Index Cond: (shop_id = 80)
23. 0.020 107.346 ↑ 99.1 15 1

Subquery Scan on *SELECT* 3 (cost=2,041.93..95,967.66 rows=1,487 width=1,191) (actual time=78.720..107.346 rows=15 loops=1)

24. 0.021 107.326 ↑ 99.1 15 1

Result (cost=2,041.93..95,945.35 rows=1,487 width=1,183) (actual time=78.718..107.326 rows=15 loops=1)

  • One-Time Filter: (3 = ANY ('{0,1,2,3}'::integer[]))
25. 25.308 107.305 ↑ 99.1 15 1

Hash Join (cost=2,041.93..95,945.35 rows=1,487 width=1,183) (actual time=78.712..107.305 rows=15 loops=1)

  • Hash Cond: (o_2.id = a.shopify_order_id)
26. 67.161 67.161 ↓ 1.0 137,326 1

Index Scan using index_shopify_orders_on_shop_id on shopify_orders o_2 (cost=0.43..91,723.02 rows=133,061 width=32) (actual time=0.027..67.161 rows=137,326 loops=1)

  • Index Cond: (shop_id = 80)
27. 5.075 14.836 ↑ 1.0 27,059 1

Hash (cost=1,702.49..1,702.49 rows=27,120 width=56) (actual time=14.836..14.836 rows=27,059 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,983kB
28. 9.761 9.761 ↑ 1.0 27,059 1

Seq Scan on shopify_order_adjustments a (cost=0.00..1,702.49 rows=27,120 width=56) (actual time=0.010..9.761 rows=27,059 loops=1)

  • Filter: ((created_at >= '2019-06-26 23:00:00'::timestamp without time zone) AND (created_at <= '2019-09-24 22:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 21,230
29. 83.832 3,587.741 ↓ 1.5 49,756 1

Subquery Scan on *SELECT* 4 (cost=184,466.35..185,922.37 rows=32,356 width=1,199) (actual time=3,310.961..3,587.741 rows=49,756 loops=1)

30. 162.099 3,503.909 ↓ 1.5 49,756 1

GroupAggregate (cost=184,466.35..185,356.14 rows=32,356 width=1,187) (actual time=3,310.958..3,503.909 rows=49,756 loops=1)

  • Group Key: o_3.id
31. 119.369 3,341.810 ↓ 1.6 52,190 1

Sort (cost=184,466.35..184,547.24 rows=32,356 width=80) (actual time=3,310.946..3,341.810 rows=52,190 loops=1)

  • Sort Key: o_3.id
  • Sort Method: external merge Disk: 4,368kB
32. 17.055 3,222.441 ↓ 1.6 52,190 1

Result (cost=170,184.09..182,042.60 rows=32,356 width=80) (actual time=2,856.364..3,222.441 rows=52,190 loops=1)

  • One-Time Filter: (1 = ANY ('{0,1,2,3}'::integer[]))
33. 373.055 3,205.386 ↓ 1.6 52,190 1

Hash Right Join (cost=170,184.09..182,042.60 rows=32,356 width=80) (actual time=2,856.360..3,205.386 rows=52,190 loops=1)

  • Hash Cond: (s.shopify_shipping_line_id = sl.id)
34. 120.491 120.491 ↑ 1.0 379,323 1

Seq Scan on shopify_shipping_tax_lines s (cost=0.00..10,303.41 rows=397,541 width=16) (actual time=0.012..120.491 rows=379,323 loops=1)

35. 30.609 2,711.840 ↓ 1.5 49,756 1

Hash (cost=169,779.64..169,779.64 rows=32,356 width=80) (actual time=2,711.840..2,711.840 rows=49,756 loops=1)

  • Buckets: 65,536 (originally 32768) Batches: 2 (originally 1) Memory Usage: 3,647kB
36. 2,060.759 2,681.231 ↓ 1.5 49,756 1

Hash Join (cost=92,879.10..169,779.64 rows=32,356 width=80) (actual time=179.178..2,681.231 rows=49,756 loops=1)

  • Hash Cond: (sl.shopify_order_id = o_3.id)
37. 441.669 441.669 ↓ 1.0 2,021,599 1

Seq Scan on shopify_shipping_lines sl (cost=0.00..71,651.04 rows=1,999,804 width=48) (actual time=0.009..441.669 rows=2,021,599 loops=1)

38. 20.859 178.803 ↓ 1.3 50,013 1

Hash (cost=92,388.33..92,388.33 rows=39,262 width=40) (actual time=178.803..178.803 rows=50,013 loops=1)

  • Buckets: 65,536 (originally 65536) Batches: 2 (originally 1) Memory Usage: 3,585kB
39. 157.944 157.944 ↓ 1.3 50,013 1

Index Scan using index_shopify_orders_on_shop_id on shopify_orders o_3 (cost=0.43..92,388.33 rows=39,262 width=40) (actual time=0.023..157.944 rows=50,013 loops=1)

  • Index Cond: (shop_id = 80)
  • Filter: ((created_at >= '2019-06-26 23:00:00'::timestamp without time zone) AND (created_at <= '2019-09-24 22:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 87,313
Planning time : 2.935 ms
Execution time : 78,954.151 ms