explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ofqK

Settings
# exclusive inclusive rows x rows loops node
1. 277.300 177,294.262 ↑ 6.3 31,071 1

Sort (cost=10,490,738.32..10,491,226.96 rows=195,456 width=793) (actual time=177,277.431..177,294.262 rows=31,071 loops=1)

  • Sort Key: balance_list_gims.creditor, balance_list_gims.malo_melo, balance_list_gims.invoice_creation_date
  • Sort Method: external merge Disk: 6144kB
2. 14.269 177,016.962 ↑ 6.3 31,071 1

Subquery Scan on balance_list_gims (cost=10,322,252.25..10,335,934.17 rows=195,456 width=793) (actual time=176,989.613..177,016.962 rows=31,071 loops=1)

3. 8.205 177,002.693 ↑ 6.3 31,071 1

Unique (cost=10,322,252.25..10,333,002.33 rows=195,456 width=765) (actual time=176,989.606..177,002.693 rows=31,071 loops=1)

4.          

CTE end_amounts

5. 87.395 72,084.143 ↑ 1.3 10,278 1

GroupAggregate (cost=8,780,811.27..8,787,738.52 rows=12,880 width=78) (actual time=71,971.306..72,084.143 rows=10,278 loops=1)

  • Group Key: i_1.grid_operator_mpid, (CASE WHEN (i_1.market_location_id IS NULL) THEN i_1.meter_id ELSE i_1.market_location_id END)
6. 365.196 71,996.748 ↓ 1.0 61,385 1

Sort (cost=8,780,811.27..8,780,960.20 rows=59,572 width=85) (actual time=71,971.264..71,996.748 rows=61,385 loops=1)

  • Sort Key: i_1.grid_operator_mpid, (CASE WHEN (i_1.market_location_id IS NULL) THEN i_1.meter_id ELSE i_1.market_location_id END)
  • Sort Method: external merge Disk: 4576kB
7. 57,219.655 71,631.552 ↓ 1.0 61,385 1

Nested Loop Left Join (cost=11,344.57..8,773,234.01 rows=59,572 width=85) (actual time=82.927..71,631.552 rows=61,385 loops=1)

  • Join Filter: ((i_1.payment_execution_date <= i2_2.payment_execution_date) AND (i2_2.source_ts >= i_1.source_ts) AND (i_1.source_ts <= i2_2.payment_execution_date) AND (((CASE WHEN (i2_2.market_location_id IS NOT NULL) THEN i2_2.market_location_id ELSE i2_2.meter_id END)::text = (i_1.market_location_id)::text) OR ((i_1.market_location_id IS NULL) AND ((i_1.meter_id)::text = (CASE WHEN (i2_2.market_location_id IS NOT NULL) THEN i2_2.market_location_id ELSE i2_2.meter_id END)::text))))
  • Rows Removed by Join Filter: 348081816
8. 43.667 118.457 ↑ 1.0 59,556 1

Gather (cost=5,621.87..16,974.38 rows=59,572 width=99) (actual time=47.953..118.457 rows=59,556 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 8.185 74.790 ↑ 1.3 19,852 3

Hash Left Join (cost=4,621.87..10,017.18 rows=24,822 width=99) (actual time=34.630..74.790 rows=19,852 loops=3)

  • Hash Cond: ((gb_1.booking_id)::text = (b_1.booking_id)::text)
10. 16.810 57.131 ↑ 1.3 19,852 3

Parallel Hash Join (cost=3,810.37..8,864.37 rows=24,822 width=116) (actual time=24.809..57.131 rows=19,852 loops=3)

  • Hash Cond: (i_1.gims_invoice_id = gb_1.invoice_pk)
11. 15.846 15.846 ↑ 1.3 20,333 3

Parallel Seq Scan on invoice i_1 (cost=0.00..4,807.94 rows=26,680 width=93) (actual time=0.015..15.846 rows=20,333 loops=3)

  • Filter: ((source_ts < '2019-07-01 00:00:00'::timestamp without time zone) AND ((invoice_type_code)::text = ANY ('{ABS,JVR,ZVR,MVR,MMM,MSB}'::text[])))
  • Rows Removed by Filter: 17974
12. 12.229 24.475 ↑ 1.8 35,285 3

Parallel Hash (cost=3,031.40..3,031.40 rows=62,318 width=39) (actual time=24.474..24.475 rows=35,285 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 9344kB
13. 12.246 12.246 ↑ 1.8 35,285 3

Parallel Seq Scan on booking gb_1 (cost=0.00..3,031.40 rows=62,318 width=39) (actual time=1.383..12.246 rows=35,285 loops=3)

  • Filter: ((booking_type)::text = ANY ('{""final cancellation"",""final booking""}'::text[]))
  • Rows Removed by Filter: 9069
14. 4.532 9.474 ↓ 1.0 23,451 3

Hash (cost=519.00..519.00 rows=23,400 width=16) (actual time=9.474..9.474 rows=23,451 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1369kB
15. 4.942 4.942 ↓ 1.0 23,451 3

Seq Scan on bookings b_1 (cost=0.00..519.00 rows=23,400 width=16) (actual time=2.259..4.942 rows=23,451 loops=3)

16. 14,243.089 14,293.440 ↑ 1.0 5,845 59,556

Materialize (cost=5,722.70..9,615.42 rows=5,873 width=70) (actual time=0.001..0.240 rows=5,845 loops=59,556)

17. 10.757 50.351 ↑ 1.0 5,845 1

Gather (cost=5,722.70..9,586.05 rows=5,873 width=70) (actual time=34.708..50.351 rows=5,845 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
18. 6.877 39.594 ↑ 1.2 2,922 2

Parallel Hash Join (cost=4,722.70..7,998.75 rows=3,455 width=70) (actual time=24.221..39.594 rows=2,922 loops=2)

  • Hash Cond: (gb1.invoice_pk = i2_2.gims_invoice_id)
19. 9.982 9.982 ↑ 1.2 52,113 2

Parallel Seq Scan on booking gb1 (cost=0.00..3,031.40 rows=61,402 width=8) (actual time=1.203..9.982 rows=52,113 loops=2)

  • Filter: ((booking_type)::text = 'final booking'::text)
  • Rows Removed by Filter: 14418
20. 1.450 22.735 ↓ 1.3 3,368 2

Parallel Hash (cost=4,689.32..4,689.32 rows=2,670 width=70) (actual time=22.735..22.735 rows=3,368 loops=2)

  • Buckets: 8192 Batches: 1 Memory Usage: 736kB
21. 21.285 21.285 ↓ 1.3 3,368 2

Parallel Seq Scan on invoice i2_2 (cost=0.00..4,689.32 rows=2,670 width=70) (actual time=0.022..21.285 rows=3,368 loops=2)

  • Filter: ((source_ts < '2019-07-01 00:00:00'::timestamp without time zone) AND ((invoice_type_code)::text = ANY ('{JVR,MVR,ABR,ZVR}'::text[])))
  • Rows Removed by Filter: 54093
22.          

CTE parentinvoice

23. 14.578 68.650 ↑ 1.1 7,274 1

Gather (cost=5,733.92..9,799.62 rows=7,848 width=104) (actual time=47.703..68.650 rows=7,274 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
24. 9.283 54.072 ↑ 1.3 3,637 2

Parallel Hash Join (cost=4,733.92..8,014.82 rows=4,616 width=104) (actual time=34.379..54.072 rows=3,637 loops=2)

  • Hash Cond: (gb1_1.invoice_pk = i2_3.gims_invoice_id)
25. 12.266 12.266 ↑ 1.2 52,113 2

Parallel Seq Scan on booking gb1_1 (cost=0.00..3,031.40 rows=61,402 width=8) (actual time=1.500..12.266 rows=52,113 loops=2)

  • Filter: ((booking_type)::text = 'final booking'::text)
  • Rows Removed by Filter: 14418
26. 3.029 32.523 ↓ 1.2 4,106 2

Parallel Hash (cost=4,689.32..4,689.32 rows=3,568 width=84) (actual time=32.523..32.523 rows=4,106 loops=2)

  • Buckets: 16384 Batches: 1 Memory Usage: 1120kB
27. 29.494 29.494 ↓ 1.2 4,106 2

Parallel Seq Scan on invoice i2_3 (cost=0.00..4,689.32 rows=3,568 width=84) (actual time=0.028..29.494 rows=4,106 loops=2)

  • Filter: ((source_ts <= '2019-09-01 00:00:00'::timestamp without time zone) AND ((invoice_type_code)::text = ANY ('{JVR,MVR,ABR,ZVR}'::text[])))
  • Rows Removed by Filter: 53354
28. 264.108 176,994.488 ↑ 6.3 31,266 1

Sort (cost=1,524,714.11..1,525,202.75 rows=195,456 width=765) (actual time=176,989.605..176,994.488 rows=31,266 loops=1)

  • Sort Key: i.gims_invoice_id, i.grid_operator_mpid, i.grid_operator_name, i.payment_execution_date, (CASE WHEN ((i.grid_operator_mpid)::text = ANY ('{9870075400001,9800181200003,9900269000000,9906214000003}'::text[])) THEN 'iA'::text ELSE 'ooA'::text END), (CASE WHEN (i.market_location_id IS NULL) THEN i.meter_id ELSE i.market_location_id END), i.meter_type, i.invoice_id, gb.booking_id, b.booking_date, gb.booking_type, (CASE WHEN (((i.invoice_type_code)::text = 'ABS'::text) AND ((gb.booking_type)::text = 'final booking'::text) AND (i.payment_execution_date <= '2019-09-01 00:00:00'::timestamp without time zone) AND (parentinvoice.releate_invoice IS NULL)) THEN abs(i.amount_due) WHEN (((i.invoice_type_code)::text = 'ABS'::text) AND ((gb.booking_type)::text = 'final cancellation'::text) AND (i.payment_execution_date > '2019-09-01 00:00:00'::timestamp without time zone)) THEN abs(i.amount_due) WHEN (((i.invoice_type_code)::text = ANY ('{JVR,MVR,ABR,ZVR,MMM,MSB}'::text[])) AND (i.source_ts >= '2019-07-01 00:00:00'::timestamp without time zone) AND ((i.payment_execution_date > '2019-09-01 00:00:00'::timestamp without time zone) OR (i.payment_execution_date IS NULL)) AND (i.amount_due < '0'::numeric)) THEN abs(i.amount_due) WHEN (((i.invoice_type_code)::text = ANY ('{JVR,MVR,ABR,ZVR,MMM,MSB}'::text[])) AND (i.source_ts < '2019-09-01 00:00:00'::timestamp without time zone) AND (i.payment_execution_date <= '2019-09-01 00:00:00'::timestamp without time zone) AND (i.amount_due > '0'::numeric)) THEN abs(i.amount_due) WHEN (((i.invoice_type_code)::text = ANY ('{JVR,MVR,ABR,ZVR,MMM,MSB}'::text[])) AND (i.source_ts >= '2019-07-01 00:00:00'::timestamp without time zone) AND (i.payment_execution_date <= '2019-09-01 00:00:00'::timestamp without time zone)) THEN abs(i.amount_due) ELSE '0'::numeric END), (CASE WHEN ((i.invoice_type_code)::text = 'ABS_NEUTR'::text) THEN abs(i.invoice_amount) WHEN (((i.invoice_type_code)::text = ANY ('{JVR,MVR,ABR,ZVR,MMM,MSB}'::text[])) AND (i.source_ts >= '2019-07-01 00:00:00'::timestamp without time zone) AND (i.payment_execution_date > '2019-09-01 00:00:00'::timestamp without time zone) AND (i.amount_due > '0'::numeric)) THEN abs(i.amount_due) WHEN (((i.invoice_type_code)::text = ANY ('{JVR,MVR,ABR,ZVR,MMM,MSB}'::text[])) AND (i.source_ts < '2019-09-01 00:00:00'::timestamp without time zone) AND (i.payment_execution_date <= '2019-09-01 00:00:00'::timestamp without time zone) AND (i.amount_due < '0'::numeric)) THEN abs(i.amount_due) WHEN (((i.invoice_type_code)::text = ANY ('{JVR,MVR,ABR,ZVR,MMM,MSB}'::text[])) AND (i.source_ts >= '2019-07-01 00:00:00'::timestamp without time zone) AND (i.payment_execution_date <= '2019-09-01 00:00:00'::timestamp without time zone)) THEN abs(i.amount_due) WHEN (((i.invoice_type_code)::text = 'ABS'::text) AND ((gb.booking_type)::text = 'final booking'::text) AND ((i.payment_execution_date > '2019-09-01 00:00:00'::timestamp without time zone) OR (i.payment_execution_date IS NULL))) THEN abs(i.amount_due) WHEN (((i.invoice_type_code)::text = 'ABS'::text) AND ((gb.booking_type)::text = 'final cancellation'::text) AND (i.payment_execution_date < '2019-09-01 00:00:00'::timestamp without time zone)) THEN abs(i.amount_due) WHEN ((i.invoice_type_code)::text = 'ABS_COR'::text) THEN ('-1'::numeric * i.invoice_amount) WHEN (((i.invoice_type_code)::text = ANY ('{JVR,MVR,ABR,ZVR,MMM,MSB}'::text[])) AND ((i.payment_execution_date IS NULL) OR (i.payment_execution_date > '2019-09-01 00:00:00'::timestamp without time zone)) AND (i.amount_due > '0'::numeric)) THEN abs(i.amount_due) ELSE '0'::numeric END), (CASE WHEN (end_amounts.amount_sum IS NULL) THEN '0'::numeric ELSE end_amounts.amount_sum END), (CASE WHEN ((i.invoice_type_code)::text = 'ABS_NEUTR'::text) THEN 'ABS'::character varying WHEN ((i.invoice_type_code)::text = 'ABS_COR'::text) THEN 'ABS'::character varying ELSE i.invoice_type_code END), i.invoice_amount, i.amount_due, i.remadv_sent_at, i.source_ts, i.due_date, (CASE WHEN ((i.invoice_type_code)::text = 'ABS_NEUTR'::text) THEN 'ABS-Neutralize'::text WHEN ((i.invoice_type_code)::text = 'ABS_COR'::text) THEN 'ABS-Equalize'::text ELSE NULL::text END)
  • Sort Method: external merge Disk: 6208kB
29. 192.950 176,730.380 ↑ 6.3 31,266 1

Hash Left Join (cost=46,174.62..1,375,257.96 rows=195,456 width=765) (actual time=73,904.771..176,730.380 rows=31,266 loops=1)

  • Hash Cond: ((gb.booking_id)::text = (b.booking_id)::text)
30. 71.008 176,531.172 ↑ 6.3 31,266 1

Merge Join (cost=45,363.12..1,336,576.86 rows=195,456 width=733) (actual time=73,898.473..176,531.172 rows=31,266 loops=1)

  • Merge Cond: (gb.invoice_pk = i.gims_invoice_id)
31. 136.127 136.127 ↑ 1.3 82,084 1

Index Scan using booking_pk on booking gb (cost=0.42..14,846.31 rows=105,940 width=39) (actual time=0.008..136.127 rows=82,084 loops=1)

  • Filter: ((booking_type)::text = ANY ('{""final cancellation"",""final booking""}'::text[]))
  • Rows Removed by Filter: 19218
32. 46.018 176,324.037 ↑ 4.8 34,274 1

Materialize (cost=45,362.70..1,318,946.15 rows=165,361 width=702) (actual time=72,626.963..176,324.037 rows=34,274 loops=1)

33. 41,116.047 176,278.019 ↑ 4.9 34,005 1

Nested Loop Left Join (cost=45,362.70..1,318,532.75 rows=165,361 width=702) (actual time=72,626.960..176,278.019 rows=34,005 loops=1)

  • Join Filter: (((end_amounts.mm)::text = (i.market_location_id)::text) OR ((i.market_location_id IS NULL) AND ((i.meter_id)::text = (end_amounts.mm)::text)))
  • Rows Removed by Join Filter: 345063988
34. 20,092.753 37,825.148 ↓ 13.1 33,576 1

Nested Loop Left Join (cost=45,362.70..495,693.95 rows=2,555 width=670) (actual time=547.093..37,825.148 rows=33,576 loops=1)

  • Join Filter: ((i.payment_execution_date <= parentinvoice.payment_execution_date) AND (parentinvoice.creation_date >= i.source_ts) AND (i.source_ts <= parentinvoice.payment_execution_date) AND (((parentinvoice.malo_melo)::text = (i.market_location_id)::text) OR ((i.market_location_id IS NULL) AND ((i.meter_id)::text = (parentinvoice.malo_melo)::text))))
  • Rows Removed by Join Filter: 243333435
35. 27.582 604.459 ↓ 19.1 33,453 1

Subquery Scan on i (cost=45,362.70..49,515.53 rows=1,749 width=662) (actual time=476.256..604.459 rows=33,453 loops=1)

  • Filter: ((i.invoice_type_code)::text = ANY ('{ABS,ABS_COR,ABS_NEUTR,JVR,ZVR,MVR,MMM,MSB}'::text[]))
  • Rows Removed by Filter: 639
36. 51.610 576.877 ↑ 1.3 34,092 1

Unique (cost=45,362.70..48,641.25 rows=43,714 width=1,093) (actual time=476.250..576.877 rows=34,092 loops=1)

37. 392.904 525.267 ↑ 1.3 34,092 1

Sort (cost=45,362.70..45,471.99 rows=43,714 width=1,093) (actual time=476.248..525.267 rows=34,092 loops=1)

  • Sort Key: i1.gims_invoice_id, i1.daily_batch_name, i1.invoice_id, i1.invoice_type_code, i1.market_location_id, i1.billing_period_start, i1.billing_period_end, i1.due_date, i1.amount_due, i1.prepaid_amount, i1.invoice_amount, i1.tax_amount, i1.grid_operator_mpid, i1.grid_operator_mpid_code, i1.grid_operator_name, i1.meter_id, i1.meter_type, i1.original_posting_date, i1.receipt_date, i1.billing_period_closed, i1.cancellation_id, i1.cancellation_remadv_id, i1.remadv_id, i1.remadv_type, i1.remadv_amount, i1.remadv_sent_at, i1.payment_execution_date, i1.updated_ts, i1.source_ts
  • Sort Method: external merge Disk: 8512kB
38. 2.547 132.363 ↑ 1.3 34,092 1

Append (cost=0.00..21,073.77 rows=43,714 width=1,093) (actual time=0.267..132.363 rows=34,092 loops=1)

39. 54.095 54.095 ↑ 1.2 28,582 1

Seq Scan on invoice i1 (cost=0.00..6,705.83 rows=34,145 width=263) (actual time=0.266..54.095 rows=28,582 loops=1)

  • Filter: ((amount_due <> '0'::numeric) AND (((source_ts >= '2019-07-01 00:00:00'::timestamp without time zone) AND (source_ts <= '2019-09-01 00:00:00'::timestamp without time zone)) OR ((source_ts <= '2019-07-01 00:00:00'::timestamp without time zone) AND ((payment_execution_date IS NULL) OR ((payment_execution_date >= '2019-07-01 00:00:00'::timestamp without time zone) AND (payment_execution_date <= '2019-09-01 00:00:00'::timestamp without time zone))))))
  • Rows Removed by Filter: 86339
40. 1.196 40.047 ↑ 1.7 4,227 1

Subquery Scan on *SELECT* 2 (cost=1,000.00..7,184.76 rows=7,058 width=291) (actual time=15.438..40.047 rows=4,227 loops=1)

41. 25.223 38.851 ↑ 1.7 4,227 1

Gather (cost=1,000.00..7,114.18 rows=7,058 width=291) (actual time=15.436..38.851 rows=4,227 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
42. 13.628 13.628 ↑ 2.1 1,409 3

Parallel Seq Scan on invoice i2 (cost=0.00..5,408.38 rows=2,941 width=291) (actual time=4.847..13.628 rows=1,409 loops=3)

  • Filter: ((source_ts < '2019-07-01 00:00:00'::timestamp without time zone) AND (payment_execution_date >= '2019-07-01 00:00:00'::timestamp without time zone) AND (amount_due <> '0'::numeric) AND ((invoice_type_code)::text = 'ABS'::text) AND (((source_ts >= '2019-07-01 00:00:00'::timestamp without time zone) AND (source_ts <= '2019-09-01 00:00:00'::timestamp without time zone)) OR ((source_ts <= '2019-07-01 00:00:00'::timestamp without time zone) AND ((payment_execution_date IS NULL) OR ((payment_execution_date >= '2019-07-01 00:00:00'::timestamp without time zone) AND (payment_execution_date <= '2019-09-01 00:00:00'::timestamp without time zone))))))
  • Rows Removed by Filter: 36898
43. 19.665 35.674 ↑ 2.0 1,283 1

Gather (cost=1,000.00..6,598.05 rows=2,511 width=345) (actual time=1.419..35.674 rows=1,283 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
44. 16.009 16.009 ↑ 2.4 428 3

Parallel Seq Scan on invoice i2_1 (cost=0.00..5,346.95 rows=1,046 width=345) (actual time=0.494..16.009 rows=428 loops=3)

  • Filter: (((invoice_type_code)::text = ANY ('{JVR,ZVR,MVR}'::text[])) AND ((invoice_amount - amount_due) <> '0'::numeric) AND (((source_ts >= '2019-07-01 00:00:00'::timestamp without time zone) AND (source_ts <= '2019-09-01 00:00:00'::timestamp without time zone)) OR ((source_ts <= '2019-07-01 00:00:00'::timestamp without time zone) AND ((payment_execution_date IS NULL) OR ((payment_execution_date >= '2019-07-01 00:00:00'::timestamp without time zone) AND (payment_execution_date <= '2019-09-01 00:00:00'::timestamp without time zone))))))
  • Rows Removed by Filter: 37879
45. 17,127.936 17,127.936 ↑ 1.1 7,274 33,453

CTE Scan on parentinvoice (cost=0.00..156.96 rows=7,848 width=56) (actual time=0.002..0.512 rows=7,274 loops=33,453)

46. 97,336.824 97,336.824 ↑ 1.3 10,278 33,576

CTE Scan on end_amounts (cost=0.00..257.60 rows=12,880 width=64) (actual time=2.144..2.899 rows=10,278 loops=33,576)

47. 2.964 6.258 ↓ 1.0 23,451 1

Hash (cost=519.00..519.00 rows=23,400 width=20) (actual time=6.258..6.258 rows=23,451 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1461kB
48. 3.294 3.294 ↓ 1.0 23,451 1

Seq Scan on bookings b (cost=0.00..519.00 rows=23,400 width=20) (actual time=0.581..3.294 rows=23,451 loops=1)

Planning time : 9.132 ms
Execution time : 177,313.400 ms