explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sYg73 : query_original

Settings
# exclusive inclusive rows x rows loops node
1. 51.177 3,049.564 ↓ 2.0 60,204 1

Unique (cost=787,145.60..793,984.52 rows=29,769 width=492) (actual time=2,825.436..3,049.564 rows=60,204 loops=1)

2. 0.000 2,998.387 ↓ 2.1 62,111 1

Finalize GroupAggregate (cost=787,145.60..793,910.10 rows=29,769 width=492) (actual time=2,825.434..2,998.387 rows=62,111 loops=1)

  • Group Key: t.id, d.created_at, o.key_origin, oc.name, s.name, ts.name, (date_part('month'::text, t.created_at)), (date_part('year'::text, t.created_at)), ba.state, ba.city, ba.cep, p.name, p.email, p.phone_number, pc.code, invoices.status, (COALESCE((invoices.metadata ->> 'note_number'::text), (invoices.metadata ->> 'fiscal_note_number'::text))), (COALESCE((invoices.metadata ->> 'emission_date'::text), (invoices.metadata ->> 'cae_generation_date'::text))), invoices.fiscal_key, ds.name, d.carrier_name, d.tracking_code, (((ot.deliveries)::numeric / 100.00)), (((ot.discounts_payments)::numeric / 100.00)), (((ot.discounts_vouchers)::numeric / 100.00)), ((COALESCE((invoices.metadata ->> 'total'::text), (invoices.metadata ->> 'invoice_total'::text)))::double precision)
3. 5,613.587 8,513.945 ↓ 2.5 62,985 1

Gather Merge (cost=787,145.60..791,280.47 rows=24,808 width=492) (actual time=2,825.415..8,513.945 rows=62,985 loops=1)

  • Workers Planned: 2
  • Workers Launched: 1
4. 56.055 2,900.358 ↓ 2.5 31,492 2 / 2

Partial GroupAggregate (cost=786,145.58..787,416.99 rows=12,404 width=492) (actual time=2,816.977..2,900.358 rows=31,492 loops=2)

  • Group Key: t.id, d.created_at, o.key_origin, oc.name, s.name, ts.name, (date_part('month'::text, t.created_at)), (date_part('year'::text, t.created_at)), ba.state, ba.city, ba.cep, p.name, p.email, p.phone_number, pc.code, invoices.status, (COALESCE((invoices.metadata ->> 'note_number'::text), (invoices.metadata ->> 'fiscal_note_number'::text))), (COALESCE((invoices.metadata ->> 'emission_date'::text), (invoices.metadata ->> 'cae_generation_date'::text))), invoices.fiscal_key, ds.name, d.carrier_name, d.tracking_code, (((ot.deliveries)::numeric / 100.00)), (((ot.discounts_payments)::numeric / 100.00)), (((ot.discounts_vouchers)::numeric / 100.00)), ((COALESCE((invoices.metadata ->> 'total'::text), (invoices.metadata ->> 'invoice_total'::text)))::double precision)
5. 179.083 2,844.303 ↓ 3.6 44,550 2 / 2

Sort (cost=786,145.58..786,176.59 rows=12,404 width=488) (actual time=2,816.956..2,844.303 rows=44,550 loops=2)

  • Sort Key: t.id, d.created_at DESC, o.key_origin, oc.name, s.name, ts.name, (date_part('month'::text, t.created_at)), (date_part('year'::text, t.created_at)), ba.state, ba.city, ba.cep, p.name, p.email, p.phone_number, pc.code, invoices.status, (COALESCE((invoices.metadata ->> 'note_number'::text), (invoices.metadata ->> 'fiscal_note_number'::text))), (COALESCE((invoices.metadata ->> 'emission_date'::text), (invoices.metadata ->> 'cae_generation_date'::text))), invoices.fiscal_key, ds.name, d.carrier_name, d.tracking_code, (((ot.deliveries)::numeric / 100.00)), (((ot.discounts_payments)::numeric / 100.00)), (((ot.discounts_vouchers)::numeric / 100.00)), ((COALESCE((invoices.metadata ->> 'total'::text), (invoices.metadata ->> 'invoice_total'::text)))::double precision)
  • Sort Method: external merge Disk: 13,136kB
  • Worker 0: Sort Method: external merge Disk: 14,576kB
6. 65.521 2,665.220 ↓ 3.6 44,550 2 / 2

Hash Left Join (cost=456,258.43..782,586.20 rows=12,404 width=488) (actual time=2,118.411..2,665.220 rows=44,550 loops=2)

  • Hash Cond: (d.delivery_status_id = ds.id)
7. 27.006 2,599.682 ↓ 3.6 44,550 2 / 2

Nested Loop Left Join (cost=456,257.18..782,291.76 rows=12,404 width=378) (actual time=2,118.368..2,599.682 rows=44,550 loops=2)

8. 13.205 2,443.559 ↓ 3.5 43,039 2 / 2

Nested Loop Left Join (cost=456,256.75..677,185.63 rows=12,404 width=348) (actual time=2,118.352..2,443.559 rows=43,039 loops=2)

9. 41.877 2,301.237 ↓ 3.5 43,039 2 / 2

Nested Loop (cost=456,256.32..617,408.85 rows=12,404 width=223) (actual time=2,118.333..2,301.237 rows=43,039 loops=2)

10. 90.525 2,169.842 ↓ 3.6 44,759 2 / 2

Parallel Hash Left Join (cost=456,255.89..544,907.73 rows=12,404 width=215) (actual time=2,118.306..2,169.842 rows=44,759 loops=2)

  • Hash Cond: (t.promo_code_id = pc.id)
11. 962.719 1,953.009 ↓ 3.6 44,759 2 / 2

Parallel Hash Join (cost=444,358.04..531,233.32 rows=12,404 width=208) (actual time=1,661.335..1,953.009 rows=44,759 loops=2)

  • Hash Cond: (oi.transaction_id = t.id)
12. 570.227 570.227 ↓ 1.2 2,237,489 2 / 2

Parallel Seq Scan on order_items oi (cost=0.00..79,939.80 rows=1,835,680 width=20) (actual time=0.010..570.227 rows=2,237,489 loops=2)

13. 55.690 420.063 ↓ 1.8 31,344 2 / 2

Parallel Hash (cost=444,136.71..444,136.71 rows=17,706 width=196) (actual time=420.063..420.063 rows=31,344 loops=2)

  • Buckets: 16,384 (originally 65536) Batches: 8 (originally 1) Memory Usage: 1,888kB
14. 17.665 364.373 ↓ 1.8 31,344 2 / 2

Nested Loop (cost=4,084.75..444,136.71 rows=17,706 width=196) (actual time=15.480..364.373 rows=31,344 loops=2)

15. 23.389 252.677 ↓ 1.8 31,344 2 / 2

Nested Loop (cost=4,084.32..352,224.89 rows=17,706 width=140) (actual time=15.464..252.677 rows=31,344 loops=2)

16. 13.464 132.358 ↓ 1.8 32,310 2 / 2

Hash Join (cost=4,083.88..244,934.59 rows=17,706 width=114) (actual time=15.412..132.358 rows=32,310 loops=2)

  • Hash Cond: (t.transaction_status_id = ts.id)
17. 12.774 118.841 ↓ 1.8 32,310 2 / 2

Hash Join (cost=4,078.20..244,879.80 rows=17,706 width=96) (actual time=15.349..118.841 rows=32,310 loops=2)

  • Hash Cond: (t.store_id = s.id)
18. 11.783 106.043 ↓ 1.8 32,310 2 / 2

Hash Join (cost=4,073.43..244,824.87 rows=17,706 width=75) (actual time=15.294..106.043 rows=32,310 loops=2)

  • Hash Cond: (o.order_channel_id = oc.id)
19. 15.550 94.251 ↓ 1.8 32,310 2 / 2

Hash Join (cost=4,072.23..244,755.31 rows=17,706 width=75) (actual time=15.276..94.251 rows=32,310 loops=2)

  • Hash Cond: (t.transaction_origin_id = o.id)
20. 72.900 78.680 ↓ 1.8 32,310 2 / 2

Parallel Bitmap Heap Scan on transactions t (cost=4,070.60..244,700.17 rows=17,706 width=68) (actual time=15.243..78.680 rows=32,310 loops=2)

  • Recheck Cond: (created_at >= '2020-09-01'::date)
  • Filter: ((deleted_at IS NULL) AND (transaction_type_id = 1))
  • Rows Removed by Filter: 16,272
  • Heap Blocks: exact=13,669
21. 5.780 5.780 ↓ 1.4 114,939 1 / 2

Bitmap Index Scan on transactions_created_at_index (cost=0.00..4,059.97 rows=79,939 width=0) (actual time=11.560..11.560 rows=114,939 loops=1)

  • Index Cond: (created_at >= '2020-09-01'::date)
22. 0.008 0.021 ↑ 1.0 28 2 / 2

Hash (cost=1.28..1.28 rows=28 width=23) (actual time=0.021..0.021 rows=28 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
23. 0.013 0.013 ↑ 1.0 28 2 / 2

Seq Scan on transaction_origins o (cost=0.00..1.28 rows=28 width=23) (actual time=0.007..0.013 rows=28 loops=2)

24. 0.003 0.009 ↑ 1.0 9 2 / 2

Hash (cost=1.09..1.09 rows=9 width=12) (actual time=0.009..0.009 rows=9 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
25. 0.006 0.006 ↑ 1.0 9 2 / 2

Seq Scan on order_channels oc (cost=0.00..1.09 rows=9 width=12) (actual time=0.005..0.006 rows=9 loops=2)

26. 0.008 0.024 ↑ 1.0 34 2 / 2

Hash (cost=4.34..4.34 rows=34 width=33) (actual time=0.024..0.024 rows=34 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
27. 0.016 0.016 ↑ 1.0 34 2 / 2

Seq Scan on stores s (cost=0.00..4.34 rows=34 width=33) (actual time=0.007..0.016 rows=34 loops=2)

28. 0.015 0.053 ↓ 1.0 76 2 / 2

Hash (cost=4.75..4.75 rows=75 width=30) (actual time=0.053..0.053 rows=76 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
29. 0.038 0.038 ↓ 1.0 76 2 / 2

Seq Scan on transaction_statuses ts (cost=0.00..4.75 rows=75 width=30) (actual time=0.022..0.038 rows=76 loops=2)

30. 96.930 96.930 ↑ 1.0 1 64,620 / 2

Index Scan using billing_addresses_pkey on billing_addresses ba (cost=0.43..6.06 rows=1 width=38) (actual time=0.003..0.003 rows=1 loops=64,620)

  • Index Cond: (id = t.billing_address_id)
31. 94.031 94.031 ↑ 1.0 1 62,687 / 2

Index Scan using persons_pkey on persons p (cost=0.43..5.19 rows=1 width=72) (actual time=0.003..0.003 rows=1 loops=62,687)

  • Index Cond: (id = t.person_id)
32. 81.344 126.308 ↓ 1.2 213,818 2 / 2

Parallel Hash (cost=8,640.71..8,640.71 rows=177,371 width=19) (actual time=126.308..126.308 rows=213,818 loops=2)

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,392kB
33. 44.964 44.964 ↓ 1.2 213,818 2 / 2

Parallel Seq Scan on promo_codes pc (cost=0.00..8,640.71 rows=177,371 width=19) (actual time=0.008..44.964 rows=213,818 loops=2)

34. 89.518 89.518 ↑ 1.0 1 89,518 / 2

Index Scan using order_totals_pkey on order_totals ot (cost=0.43..5.84 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=89,518)

  • Index Cond: (id = t.order_total_id)
35. 129.117 129.117 ↓ 0.0 0 86,078 / 2

Index Scan using fiscal_notes_transactions_index on invoices (cost=0.43..4.81 rows=1 width=129) (actual time=0.003..0.003 rows=0 loops=86,078)

  • Index Cond: (transaction_id = t.id)
  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'outgoing'::text))
  • Rows Removed by Filter: 0
36. 129.117 129.117 ↑ 1.0 1 86,078 / 2

Index Scan using deliveries_transaction_id_index on deliveries d (cost=0.43..8.46 rows=1 width=34) (actual time=0.003..0.003 rows=1 loops=86,078)

  • Index Cond: (transaction_id = t.id)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 0
37. 0.004 0.017 ↑ 1.0 11 2 / 2

Hash (cost=1.11..1.11 rows=11 width=18) (actual time=0.017..0.017 rows=11 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
38. 0.013 0.013 ↑ 1.0 11 2 / 2

Seq Scan on delivery_statuses ds (cost=0.00..1.11 rows=11 width=18) (actual time=0.011..0.013 rows=11 loops=2)

Planning time : 5.164 ms
Execution time : 8,656.778 ms