explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rulC : test

Settings
# exclusive inclusive rows x rows loops node
1. 2.410 7,869.607 ↑ 1.0 10 1

Limit (cost=1,684.05..3,308.16 rows=10 width=136) (actual time=7,867.183..7,869.607 rows=10 loops=1)

2. 137.820 7,867.197 ↑ 7.7 20 1

MixedAggregate (cost=59.94..25,071.23 rows=154 width=136) (actual time=7,867.149..7,867.197 rows=20 loops=1)

  • Hash Key: to_char(timezone('America/New_York'::text, timezone('utc'::text, sv.created_at)), 'MM-DD-YYYY'::text)
  • Group Key: ()
3. 444.089 7,729.377 ↓ 1,720.1 263,182 1

Subquery Scan on sv (cost=59.94..25,059.31 rows=153 width=56) (actual time=14.529..7,729.377 rows=263,182 loops=1)

4. 110.133 7,285.288 ↓ 1,720.1 263,182 1

Append (cost=59.94..25,056.63 rows=153 width=1,223) (actual time=14.514..7,285.288 rows=263,182 loops=1)

5. 139.397 6,270.329 ↓ 1,317.0 197,554 1

Result (cost=59.94..18,819.32 rows=150 width=1,223) (actual time=14.513..6,270.329 rows=197,554 loops=1)

6. 81.495 6,130.932 ↓ 1,317.0 197,554 1

Append (cost=59.94..18,817.07 rows=150 width=1,215) (actual time=14.510..6,130.932 rows=197,554 loops=1)

7. 129.440 5,241.934 ↓ 2,247.1 184,262 1

Result (cost=59.94..14,978.52 rows=82 width=1,215) (actual time=14.509..5,241.934 rows=184,262 loops=1)

8. 76.858 5,112.494 ↓ 2,247.1 184,262 1

Append (cost=59.94..14,977.49 rows=82 width=1,211) (actual time=14.506..5,112.494 rows=184,262 loops=1)

9. 132.780 2,588.388 ↓ 28,288.7 169,732 1

Subquery Scan on *SELECT* 1 (cost=59.94..3,081.24 rows=6 width=1,105) (actual time=14.506..2,588.388 rows=169,732 loops=1)

10. 253.726 2,455.608 ↓ 28,288.7 169,732 1

Nested Loop (cost=59.94..3,081.17 rows=6 width=1,101) (actual time=14.489..2,455.608 rows=169,732 loops=1)

11. 751.545 764.122 ↓ 28,755.2 143,776 1

Bitmap Heap Scan on shopify_orders o (cost=59.52..3,031.39 rows=5 width=779) (actual time=14.473..764.122 rows=143,776 loops=1)

  • Recheck Cond: (shop_id = 5)
  • Filter: ((created_at >= '2018-01-01 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 30586
  • Heap Blocks: exact=13357
12. 12.577 12.577 ↓ 185.2 175,193 1

Bitmap Index Scan on index_shopify_orders_on_shop_id (cost=0.00..59.52 rows=946 width=0) (actual time=12.577..12.577 rows=175,193 loops=1)

  • Index Cond: (shop_id = 5)
13. 1,437.760 1,437.760 ↑ 2.0 1 143,776

Index Scan using index_shopify_line_items_on_shopify_order_id on shopify_line_items l (cost=0.42..9.94 rows=2 width=246) (actual time=0.008..0.010 rows=1 loops=143,776)

  • Index Cond: (shopify_order_id = o.id)
14. 13.769 2,447.248 ↓ 191.2 14,530 1

Subquery Scan on *SELECT* 2 (cost=60.46..11,895.84 rows=76 width=1,193) (actual time=44.764..2,447.248 rows=14,530 loops=1)

15. 115.818 2,433.479 ↓ 191.2 14,530 1

Nested Loop (cost=60.46..11,894.89 rows=76 width=1,189) (actual time=44.749..2,433.479 rows=14,530 loops=1)

16. 295.606 1,906.377 ↓ 176.5 205,642 1

Nested Loop (cost=60.17..11,504.59 rows=1,165 width=131) (actual time=41.600..1,906.377 rows=205,642 loops=1)

17. 1,048.014 1,087.685 ↓ 184.3 174,362 1

Bitmap Heap Scan on shopify_orders o_1 (cost=59.75..3,026.89 rows=946 width=56) (actual time=41.570..1,087.685 rows=174,362 loops=1)

  • Recheck Cond: (shop_id = 5)
  • Heap Blocks: exact=13357
18. 39.671 39.671 ↓ 185.2 175,193 1

Bitmap Index Scan on index_shopify_orders_on_shop_id (cost=0.00..59.52 rows=946 width=0) (actual time=39.671..39.671 rows=175,193 loops=1)

  • Index Cond: (shop_id = 5)
19. 523.086 523.086 ↑ 2.0 1 174,362

Index Scan using index_shopify_line_items_on_shopify_order_id on shopify_line_items l_1 (cost=0.42..8.94 rows=2 width=83) (actual time=0.003..0.003 rows=1 loops=174,362)

  • Index Cond: (shopify_order_id = o_1.id)
20. 411.284 411.284 ↓ 0.0 0 205,642

Index Scan using index_shopify_refund_line_items_on_shopify_line_item_id on shopify_refund_line_items rl (cost=0.29..0.32 rows=1 width=44) (actual time=0.002..0.002 rows=0 loops=205,642)

  • Index Cond: (shopify_line_item_id = l_1.id)
  • Filter: ((created_at >= '2018-01-01 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 0
21. 11.878 807.503 ↓ 195.5 13,292 1

Subquery Scan on *SELECT* 3 (cost=3,038.72..3,837.80 rows=68 width=1,215) (actual time=735.904..807.503 rows=13,292 loops=1)

22. 41.709 795.625 ↓ 195.5 13,292 1

Hash Join (cost=3,038.72..3,836.78 rows=68 width=1,207) (actual time=735.154..795.625 rows=13,292 loops=1)

  • Hash Cond: (a.shopify_order_id = o_2.id)
23. 20.649 20.649 ↑ 1.0 13,566 1

Seq Scan on shopify_order_adjustments a (cost=0.00..762.18 rows=13,607 width=56) (actual time=0.020..20.649 rows=13,566 loops=1)

  • Filter: ((created_at >= '2018-01-01 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 646
24. 84.726 733.267 ↓ 184.3 174,362 1

Hash (cost=3,026.89..3,026.89 rows=946 width=56) (actual time=733.267..733.267 rows=174,362 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3585kB
25. 567.858 648.541 ↓ 184.3 174,362 1

Bitmap Heap Scan on shopify_orders o_2 (cost=59.75..3,026.89 rows=946 width=56) (actual time=82.594..648.541 rows=174,362 loops=1)

  • Recheck Cond: (shop_id = 5)
  • Heap Blocks: exact=13357
26. 80.683 80.683 ↓ 185.2 175,193 1

Bitmap Index Scan on index_shopify_orders_on_shop_id (cost=0.00..59.52 rows=946 width=0) (actual time=80.683..80.683 rows=175,193 loops=1)

  • Index Cond: (shop_id = 5)
27. 53.908 904.826 ↓ 21,876.0 65,628 1

Subquery Scan on *SELECT* 4 (cost=6,236.41..6,236.54 rows=3 width=1,223) (actual time=754.927..904.826 rows=65,628 loops=1)

28. 77.758 850.918 ↓ 21,876.0 65,628 1

GroupAggregate (cost=6,236.41..6,236.49 rows=3 width=1,211) (actual time=754.213..850.918 rows=65,628 loops=1)

  • Group Key: o_3.id
29. 65.942 773.160 ↓ 21,876.0 65,628 1

Sort (cost=6,236.41..6,236.42 rows=3 width=104) (actual time=751.534..773.160 rows=65,628 loops=1)

  • Sort Key: o_3.id
  • Sort Method: external merge Disk: 3736kB
30. 35.047 707.218 ↓ 21,876.0 65,628 1

Nested Loop Left Join (cost=3,031.59..6,236.39 rows=3 width=104) (actual time=397.519..707.218 rows=65,628 loops=1)

31. 118.967 606.543 ↓ 21,876.0 65,628 1

Hash Join (cost=3,031.45..6,235.88 rows=3 width=104) (actual time=397.037..606.543 rows=65,628 loops=1)

  • Hash Cond: (sl.shopify_order_id = o_3.id)
32. 92.230 92.230 ↓ 1.0 96,339 1

Seq Scan on shopify_shipping_lines sl (cost=0.00..2,952.33 rows=96,033 width=48) (actual time=1.332..92.230 rows=96,339 loops=1)

33. 64.941 395.346 ↓ 28,755.2 143,776 1

Hash (cost=3,031.39..3,031.39 rows=5 width=64) (actual time=395.346..395.346 rows=143,776 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3585kB
34. 318.012 330.405 ↓ 28,755.2 143,776 1

Bitmap Heap Scan on shopify_orders o_3 (cost=59.52..3,031.39 rows=5 width=64) (actual time=14.295..330.405 rows=143,776 loops=1)

  • Recheck Cond: (shop_id = 5)
  • Filter: ((created_at >= '2018-01-01 04:00:00'::timestamp without time zone) AND (created_at <= '2019-09-10 03:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 30586
  • Heap Blocks: exact=13357
35. 12.393 12.393 ↓ 185.2 175,193 1

Bitmap Index Scan on index_shopify_orders_on_shop_id (cost=0.00..59.52 rows=946 width=0) (actual time=12.392..12.393 rows=175,193 loops=1)

  • Index Cond: (shop_id = 5)
36. 65.628 65.628 ↓ 0.0 0 65,628

Index Scan using ix_shipping_title on shopify_shipping_tax_lines s (cost=0.14..0.16 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=65,628)

  • Index Cond: (sl.id = shopify_shipping_line_id)
Planning time : 6.812 ms
Execution time : 7,873.858 ms