explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GTuJ : GFT

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=23,045,377.37..40,391,179.83 rows=464 width=410) (actual rows= loops=)

  • Join Filter: (dpc.dpconfigjid IS NULL)
  • Filter: (c.gridfeefromdgo OR (((COALESCE(dpc.switchcateg, dpc2.switchcateg))::text = 'Y'::text) AND ((dp.direction)::text = 'I'::text)) OR ((COALESCE(dpc.switchcateg, dpc2.switchcateg))::text = ANY ('{C,M}'::text[])) OR (alternatives: SubPlan 6 or hashed SubPlan 7) OR (SubPlan 8))
2. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=23,045,366.50..23,056,105.17 rows=616 width=430) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=23,045,355.63..23,048,159.62 rows=616 width=420) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=23,045,355.20..23,046,672.61 rows=616 width=416) (actual rows= loops=)

  • Merge Cond: (credited_pgf.papergridfeeid = credited_gfd.papergridfeeid)
5. 0.000 0.000 ↓ 0.0

Index Scan using papergridfee_pkey on papergridfee credited_pgf (cost=0.29..1,246.00 rows=30,849 width=29) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Sort (cost=23,045,340.25..23,045,341.79 rows=616 width=395) (actual rows= loops=)

  • Sort Key: credited_gfd.papergridfeeid
7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=23,042,418.94..23,045,311.71 rows=616 width=395) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Merge Join (cost=23,042,418.37..23,043,735.78 rows=616 width=351) (actual rows= loops=)

  • Merge Cond: (pgf.papergridfeeid = gfd.papergridfeeid)
9. 0.000 0.000 ↓ 0.0

Index Scan using papergridfee_pkey on papergridfee pgf (cost=0.29..1,246.00 rows=30,849 width=29) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Sort (cost=23,042,403.42..23,042,404.96 rows=616 width=326) (actual rows= loops=)

  • Sort Key: gfd.papergridfeeid
11. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=23,042,369.45..23,042,374.88 rows=616 width=326) (actual rows= loops=)

  • Hash Cond: (invoiceline.gridfeedetailid = gfd.gridfeedetailid)
12. 0.000 0.000 ↓ 0.0

Unique (cost=23,033,493.31..23,033,495.22 rows=255 width=8) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Sort (cost=23,033,493.31..23,033,493.94 rows=255 width=8) (actual rows= loops=)

  • Sort Key: invoiceline.gridfeedetailid, (array_agg(DISTINCT invoiceline.invoiceid))
14. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=0.57..23,033,483.11 rows=255 width=8) (actual rows= loops=)

  • Group Key: invoiceline.gridfeedetailid
15. 0.000 0.000 ↓ 0.0

Index Scan using invoiceline_gridfeedetailid_index on invoiceline (cost=0.57..21,403,941.69 rows=325,907,648 width=8) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Hash (cost=8,868.44..8,868.44 rows=616 width=298) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=11.38..8,868.44 rows=616 width=298) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10.95..7,375.22 rows=616 width=260) (actual rows= loops=)

  • Join Filter: (c.supplierid = s.supplierid)
19. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=10.95..7,355.72 rows=616 width=232) (actual rows= loops=)

  • Hash Cond: ((j.pointid = gf.pointid) AND (gfd.fromdate = gf.fromdate))
20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.87..7,344.01 rows=616 width=236) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.30..9.97 rows=1 width=59) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.88..7.56 rows=1 width=58) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash Join (cost=2.45..4.27 rows=1 width=46) (actual rows= loops=)

  • Hash Cond: (dgo.gridoperatorid = dp.gridoperatorid)
24. 0.000 0.000 ↓ 0.0

Seq Scan on gridoperator dgo (cost=0.00..1.59 rows=59 width=18) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Hash (cost=2.44..2.44 rows=1 width=32) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Index Scan using idx_deliverypoint_gsrn on deliverypoint dp (cost=0.42..2.44 rows=1 width=32) (actual rows= loops=)

  • Index Cond: ((gsrn)::text = '541456700001559356'::text)
27. 0.000 0.000 ↓ 0.0

Index Scan using idx_contractdpjournal_pointid_pk on contractdpjournal j (cost=0.42..3.26 rows=2 width=12) (actual rows= loops=)

  • Index Cond: (pointid = dp.pointid)
28. 0.000 0.000 ↓ 0.0

Index Scan using contract_pkey on contract c (cost=0.42..2.40 rows=1 width=9) (actual rows= loops=)

  • Index Cond: (contractid = j.contractid)
29. 0.000 0.000 ↓ 0.0

Index Scan using idx_gfdetail_contractdpid on gridfeedetail gfd (cost=0.57..7,276.34 rows=5,770 width=181) (actual rows= loops=)

  • Index Cond: (contractdpid = j.contractdpid)
  • Filter: ((invoicetype)::text <> 'P'::text)
30. 0.000 0.000 ↓ 0.0

Hash (cost=5.23..5.23 rows=123 width=8) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Seq Scan on gridfee gf (cost=0.00..5.23 rows=123 width=8) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.03 rows=2 width=36) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Seq Scan on supplier s (cost=0.00..1.02 rows=2 width=36) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Index Scan using idx_transaction_msgid_pk on transaction t (cost=0.44..2.41 rows=1 width=46) (actual rows= loops=)

  • Index Cond: (msgid = gfd.msgid)
35. 0.000 0.000 ↓ 0.0

Index Scan using "gridfeedetailid_primaryKey" on gridfeedetail credited_gfd (cost=0.57..2.55 rows=1 width=52) (actual rows= loops=)

  • Index Cond: (gridfeedetailid = gfd.credits)
36. 0.000 0.000 ↓ 0.0

Index Scan using msgenv_pk on msgenv msg (cost=0.43..2.40 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (t.envid = envid)
37. 0.000 0.000 ↓ 0.0

Index Scan using dpj_un on dpconfigjournal dpc (cost=10.87..12.89 rows=1 width=10) (actual rows= loops=)

  • Index Cond: (dpconfigjid = (SubPlan 4))
38.          

SubPlan (for Index Scan)

39. 0.000 0.000 ↓ 0.0

Limit (cost=10.44..10.45 rows=1 width=26) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Sort (cost=10.44..10.45 rows=1 width=26) (actual rows= loops=)

  • Sort Key: ((tx.msgdate)::date), ((j_1.creationdate)::date), j_1.fromdate, j_1.powerlimiter, m.budgetmeter
41. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.29..10.43 rows=1 width=26) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.86..7.97 rows=1 width=29) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j_1 (cost=0.42..5.51 rows=1 width=25) (actual rows= loops=)

  • Index Cond: (pointid = dp.pointid)
  • Filter: ((fromdate <= gfd.fromdate) AND ((source)::text = 'DGO'::text))
44. 0.000 0.000 ↓ 0.0

Index Scan using idx_transaction_msgid_pk on transaction tx (cost=0.44..2.46 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (msgid = j_1.txref)
45. 0.000 0.000 ↓ 0.0

Index Scan using idx_meter_physicalconfigid_pk on meter m (cost=0.42..2.44 rows=1 width=5) (actual rows= loops=)

  • Index Cond: (j_1.physicalconfigid = physicalconfigid)
46. 0.000 0.000 ↓ 0.0

Index Scan using dpj_un on dpconfigjournal dpc2 (cost=10.87..12.89 rows=1 width=6) (actual rows= loops=)

  • Index Cond: (dpconfigjid = (SubPlan 5))
47.          

SubPlan (for Index Scan)

48. 0.000 0.000 ↓ 0.0

Limit (cost=10.44..10.45 rows=1 width=26) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Sort (cost=10.44..10.45 rows=1 width=26) (actual rows= loops=)

  • Sort Key: ((tx_1.msgdate)::date), ((j_2.creationdate)::date), j_2.fromdate, j_2.powerlimiter, m_1.budgetmeter
50. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.29..10.43 rows=1 width=26) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.86..7.97 rows=1 width=29) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal j_2 (cost=0.42..5.51 rows=1 width=25) (actual rows= loops=)

  • Index Cond: (pointid = dp.pointid)
  • Filter: ((fromdate > gfd.fromdate) AND ((source)::text = 'DGO'::text))
53. 0.000 0.000 ↓ 0.0

Index Scan using idx_transaction_msgid_pk on transaction tx_1 (cost=0.44..2.46 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (msgid = j_2.txref)
54. 0.000 0.000 ↓ 0.0

Index Scan using idx_meter_physicalconfigid_pk on meter m_1 (cost=0.42..2.44 rows=1 width=5) (actual rows= loops=)

  • Index Cond: (j_2.physicalconfigid = physicalconfigid)
55.          

SubPlan (for Nested Loop Left Join)

56. 0.000 0.000 ↓ 0.0

Result (cost=920.08..920.09 rows=1 width=0) (actual rows= loops=)

57.          

Initplan (for Result)

58. 0.000 0.000 ↓ 0.0

Limit (cost=0.57..920.08 rows=1 width=4) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Index Scan using gridfeedetail_fromdate_idx on gridfeedetail (cost=0.57..33,000,033.28 rows=35,889 width=4) (actual rows= loops=)

  • Index Cond: (fromdate IS NOT NULL)
  • Filter: ((NOT credited) AND (papergridfeeid = gfd.papergridfeeid))
60. 0.000 0.000 ↓ 0.0

Aggregate (cost=17,907.89..17,907.90 rows=1 width=4) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Index Only Scan using gridfeedetail_papergridfeeid_msgid_credited_fromdate_todate_idx on gridfeedetail gridfeedetail_1 (cost=0.57..17,818.17 rows=35,889 width=4) (actual rows= loops=)

  • Index Cond: ((papergridfeeid = gfd.papergridfeeid) AND (credited = false))
  • Filter: (NOT credited)
62. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=1.15..13,936.92 rows=1 width=0) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Index Scan using idx_gfdetail_contractdpid on gridfeedetail gfd_1 (cost=0.57..7,372.61 rows=10,194 width=4) (actual rows= loops=)

  • Index Cond: (contractdpid = j.contractdpid)
64. 0.000 0.000 ↓ 0.0

Index Only Scan using invoiceline_gridfeedetailid_index on invoiceline il (cost=0.57..779.53 rows=10,906 width=4) (actual rows= loops=)

  • Index Cond: (gridfeedetailid = gfd_1.gridfeedetailid)
65. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10,772,594.17..10,773,246.07 rows=255 width=4) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

HashAggregate (cost=10,772,593.60..10,772,596.15 rows=255 width=4) (actual rows= loops=)

  • Group Key: il_1.gridfeedetailid
67. 0.000 0.000 ↓ 0.0

Seq Scan on invoiceline il_1 (cost=0.00..9,957,824.48 rows=325,907,648 width=4) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Index Scan using "gridfeedetailid_primaryKey" on gridfeedetail gfd_2 (cost=0.57..2.54 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (gridfeedetailid = il_1.gridfeedetailid)
69. 0.000 0.000 ↓ 0.0

Index Scan using idx_actorrate_actorid on actorrate (cost=0.28..9.37 rows=1 width=0) (actual rows= loops=)

  • Index Cond: (actorid = dp.gridoperatorid)