explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iWYw

Settings
# exclusive inclusive rows x rows loops node
1. 1.710 34,021.363 ↑ 11.6 548 1

GroupAggregate (cost=872,366.69..901,671.08 rows=6,368 width=426) (actual time=32,103.344..34,021.363 rows=548 loops=1)

2. 591.361 34,019.653 ↑ 116.2 548 1

GroupAggregate (cost=872,366.69..899,267.48 rows=63,671 width=420) (actual time=32,103.101..34,019.653 rows=548 loops=1)

3. 20,870.316 33,428.292 ↑ 3.6 176,608 1

Sort (cost=872,366.69..873,958.45 rows=636,704 width=420) (actual time=32,102.563..33,428.292 rows=176,608 loops=1)

  • Sort Key: a.taxname, a.um_hsncode, a.hsn_description, a.cgstrate, a.sgstrate, a.rate
  • Sort Method: external merge Disk: 45528kB
4. 183.188 12,557.976 ↑ 3.6 176,608 1

Subquery Scan on a (cost=364,090.57..445,377.58 rows=636,704 width=420) (actual time=9,851.878..12,557.976 rows=176,608 loops=1)

5. 1,981.511 12,374.788 ↑ 3.6 176,608 1

Merge Left Join (cost=364,090.57..439,010.54 rows=636,704 width=340) (actual time=9,851.877..12,374.788 rows=176,608 loops=1)

  • Merge Cond: ((("substring"((c.um_hsncode)::text, 1, 4))::text) = (hd.um_hsncode)::text)
6.          

CTE hsndesc

7. 89.615 189.483 ↑ 10.1 1,247 1

GroupAggregate (cost=2,943.27..3,227.07 rows=12,613 width=229) (actual time=89.558..189.483 rows=1,247 loops=1)

8. 87.534 99.868 ↑ 1.0 12,613 1

Sort (cost=2,943.27..2,974.81 rows=12,613 width=229) (actual time=89.525..99.868 rows=12,613 loops=1)

  • Sort Key: (("substring"((h.um_hsncode)::text, 1, 4))::character varying)
  • Sort Method: external merge Disk: 3024kB
9. 12.334 12.334 ↑ 1.0 12,613 1

Seq Scan on um_hsncode h (cost=0.00..701.66 rows=12,613 width=229) (actual time=0.022..12.334 rows=12,613 loops=1)

10. 1,896.403 10,106.303 ↓ 17.5 176,608 1

Sort (cost=359,276.14..359,301.38 rows=10,096 width=308) (actual time=9,659.224..10,106.303 rows=176,608 loops=1)

  • Sort Key: (("substring"((c.um_hsncode)::text, 1, 4))::text)
  • Sort Method: external merge Disk: 15552kB
11. 292.610 8,209.900 ↓ 17.5 176,608 1

Hash Left Join (cost=210,183.82..357,152.18 rows=10,096 width=308) (actual time=5,980.012..8,209.900 rows=176,608 loops=1)

  • Hash Cond: (p.um_hsncode_id = c.um_hsncode_id)
12. 193.451 7,897.199 ↓ 17.5 176,608 1

Hash Left Join (cost=209,356.03..356,122.57 rows=10,096 width=309) (actual time=5,959.896..7,897.199 rows=176,608 loops=1)

  • Hash Cond: (il.um_cesstax_id = cesst.c_tax_id)
13. 213.335 7,703.704 ↓ 17.5 176,608 1

Hash Left Join (cost=209,354.40..356,082.81 rows=10,096 width=277) (actual time=5,959.842..7,703.704 rows=176,608 loops=1)

  • Hash Cond: (il.um_addcesstax_id = cesskf.c_tax_id)
14. 248.978 7,490.316 ↓ 17.5 176,608 1

Hash Join (cost=209,352.77..356,019.64 rows=10,096 width=245) (actual time=5,959.769..7,490.316 rows=176,608 loops=1)

  • Hash Cond: (il.c_tax_id = ta.c_tax_id)
15. 346.177 7,241.182 ↓ 13.7 176,608 1

Hash Join (cost=209,349.39..355,867.12 rows=12,849 width=58) (actual time=5,959.591..7,241.182 rows=176,608 loops=1)

  • Hash Cond: (il.m_product_id = p.m_product_id)
16. 2,522.771 6,725.080 ↓ 13.7 176,608 1

Hash Join (cost=202,336.83..347,919.46 rows=12,849 width=58) (actual time=5,789.615..6,725.080 rows=176,608 loops=1)

  • Hash Cond: (il.c_invoice_id = i.c_invoice_id)
17. 1,642.933 1,642.933 ↑ 1.0 2,714,252 1

Seq Scan on c_invoiceline il (cost=0.00..131,882.76 rows=2,714,276 width=69) (actual time=0.012..1,642.933 rows=2,714,252 loops=1)

18. 22.720 2,559.376 ↓ 13.2 21,697 1

Hash (cost=202,316.24..202,316.24 rows=1,647 width=11) (actual time=2,559.376..2,559.376 rows=21,697 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 912kB
19. 2,536.656 2,536.656 ↓ 13.2 21,697 1

Seq Scan on c_invoice i (cost=0.00..202,316.24 rows=1,647 width=11) (actual time=2.930..2,536.656 rows=21,697 loops=1)

  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (c_order_id > 0::numeric) AND (ad_client_id = 1000001::numeric) AND (issotrx = 'Y'::bpchar) AND (trunc((dateinvoiced)::time (...)
20. 44.339 169.925 ↑ 1.0 62,736 1

Hash (cost=5,860.36..5,860.36 rows=62,736 width=17) (actual time=169.925..169.925 rows=62,736 loops=1)

  • Buckets: 2048 Batches: 4 Memory Usage: 774kB
21. 125.586 125.586 ↑ 1.0 62,736 1

Seq Scan on m_product p (cost=0.00..5,860.36 rows=62,736 width=17) (actual time=0.015..125.586 rows=62,736 loops=1)

22. 0.023 0.156 ↓ 1.3 28 1

Hash (cost=3.10..3.10 rows=22 width=209) (actual time=0.156..0.156 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
23. 0.059 0.133 ↓ 1.3 28 1

Hash Join (cost=1.50..3.10 rows=22 width=209) (actual time=0.074..0.133 rows=28 loops=1)

  • Hash Cond: (ta.c_taxcategory_id = tc.c_taxcategory_id)
24. 0.029 0.029 ↑ 1.0 28 1

Seq Scan on c_tax ta (cost=0.00..1.28 rows=28 width=84) (actual time=0.015..0.029 rows=28 loops=1)

25. 0.022 0.045 ↑ 1.0 22 1

Hash (cost=1.22..1.22 rows=22 width=151) (actual time=0.045..0.045 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
26. 0.023 0.023 ↑ 1.0 22 1

Seq Scan on c_taxcategory tc (cost=0.00..1.22 rows=22 width=151) (actual time=0.010..0.023 rows=22 loops=1)

27. 0.028 0.053 ↑ 1.0 28 1

Hash (cost=1.28..1.28 rows=28 width=45) (actual time=0.053..0.053 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
28. 0.025 0.025 ↑ 1.0 28 1

Seq Scan on c_tax cesskf (cost=0.00..1.28 rows=28 width=45) (actual time=0.005..0.025 rows=28 loops=1)

29. 0.024 0.044 ↑ 1.0 28 1

Hash (cost=1.28..1.28 rows=28 width=45) (actual time=0.044..0.044 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
30. 0.020 0.020 ↑ 1.0 28 1

Seq Scan on c_tax cesst (cost=0.00..1.28 rows=28 width=45) (actual time=0.002..0.020 rows=28 loops=1)

31. 8.751 20.091 ↑ 1.0 12,613 1

Hash (cost=670.13..670.13 rows=12,613 width=16) (actual time=20.091..20.091 rows=12,613 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 616kB
32. 11.340 11.340 ↑ 1.0 12,613 1

Seq Scan on um_hsncode c (cost=0.00..670.13 rows=12,613 width=16) (actual time=0.005..11.340 rows=12,613 loops=1)

33. 93.757 286.974 ↓ 14.0 175,989 1

Materialize (cost=1,587.37..1,650.44 rows=12,613 width=64) (actual time=192.582..286.974 rows=175,989 loops=1)

34. 1.956 193.217 ↑ 10.1 1,247 1

Sort (cost=1,587.37..1,618.90 rows=12,613 width=64) (actual time=192.571..193.217 rows=1,247 loops=1)

  • Sort Key: hd.um_hsncode
  • Sort Method: quicksort Memory: 403kB
35. 191.261 191.261 ↑ 10.1 1,247 1

CTE Scan on hsndesc hd (cost=0.00..252.26 rows=12,613 width=64) (actual time=89.561..191.261 rows=1,247 loops=1)