explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h7fN

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 48,125.230 ↑ 1.0 6 1

Limit (cost=5,127,437.49..5,127,437.99 rows=6 width=784) (actual time=48,125.218..48,125.230 rows=6 loops=1)

2. 0.011 48,125.227 ↑ 1.0 6 1

Unique (cost=5,127,437.49..5,127,437.99 rows=6 width=784) (actual time=48,125.216..48,125.227 rows=6 loops=1)

3. 0.030 48,125.216 ↑ 1.0 6 1

Sort (cost=5,127,437.49..5,127,437.51 rows=6 width=784) (actual time=48,125.215..48,125.216 rows=6 loops=1)

  • Sort Key: auction.id DESC, auction.version, bundlefilterconfig.auctiondate, (CASE WHEN (((auction.auctionstate)::text = 'COMPLETED'::text) AND (('now'::cstring)::date <= bundledesc.settlementdate) AND (count(trade4.*) > 0)) THEN 'TRADING_COMPLETED'::text WHEN (((auction.auctionstate)::text = 'COMPLETED'::text) AND (('now'::cstring)::date <= bundledesc.auctionmaturitydate) AND (count(trade5.*) = count(trade1.*))) THEN 'SETTLED'::text WHEN (((auction.auctionstate)::text = 'COMPLETED'::text) AND (bundledesc.settlementdate < ('now'::cstring)::date) AND (('now'::cstring)::date <= bundledesc.auctionmaturitydate) AND (count(trade6.*) > 0)) THEN 'LATE_INVESTOR_PAYMENT'::text WHEN (((auction.auctionstate)::text = 'COMPLETED'::text) AND (bundledesc.auctionmaturitydate < ('now'::cstring)::date) AND (count(trade7.*) < count(trade1.*))) THEN 'OVERDUE'::text WHEN (((auction.auctionstate)::text = 'COMPLETED'::text) AND (count(trade7.*) = count(trade1.*))) THEN 'PAID'::text WHEN (((auction.auctionstate)::text = 'COMPLETED'::text) AND (('now'::cstring)::date < bundledesc.settlementdate) AND (count(trade8.*) = count(trade1.*))) THEN 'SETTLEMENT_INSTRUCTIONS_SENT'::text WHEN (((auction.auctionstate)::text = 'COMPLETED'::text) AND (('now'::cstring)::date = bundledesc.settlementdate) AND (count(trade9.*) > 0)) THEN 'TRADING_COMPLETED'::text WHEN (((auction.auctionstate)::text = 'COMPLETED'::text) AND (('now'::cstring)::date <= bundledesc.auctionmaturitydate) AND (count(trade10.*) > 0)) THEN 'LATE_ORIGINATOR_PAYMENT'::text WHEN (((auction.auctionstate)::text = 'COMPLETED'::text) AND (('now'::cstring)::date <= bundledesc.auctionmaturitydate) AND (count(trade11.*) = count(trade1.*))) THEN 'MATURITY_PAYMENT_RECEIVED'::text ELSE 'UNKNOWN'::text END), bundledesc.settlementdate, (COALESCE(auctioninvoice.collecteddiscountedamountincents, '0'::numeric)), (COALESCE(auctioninvoice.collectedinvoiceamountincents, '0'::numeric)), (sum(tradepayment2.discountedvolumeincents)), bundlefilterconfig.currency_id, (COALESCE(summary.executedspread, '0'::numeric)), (COALESCE(auctioninvoice.invoicesize, '0'::bigint)), bundledesc.auctionmaturitydate, (COALESCE(auctioninvoice.netbuyerfixshareincents, '0'::numeric)), (COALESCE(auctioninvoice.netcrxfixshareincents, '0'::numeric)), (COALESCE(auctioninvoice.netsurplusbuyerincents, '0'::numeric)), (COALESCE(auctioninvoice.netsurpluscrxincents, '0'::numeric)), (COALESCE(auctioninvoice.opendiscountedamountincents, '0'::numeric)), (COALESCE(auctioninvoice.openinvoiceamountincents, '0'::numeric)), (sum(tradepayment3.discountedvolumeincents)), (COALESCE(summary.ordersplacedtotal, '0'::bigint)), bundlefilterconfig.buyer_id, (COALESCE(auctioninvoice.paiddiscountedamountincents, '0'::numeric)), (COALESCE(summary.sumexecutedamountincents, '0'::numeric)), (COALESCE(summary.summaxvolumeincents, '0'::numeric)), (COALESCE(summary.sumopeninterest, '0'::numeric)), (COALESCE(auctioninvoice.totalcompensationincents, '0'::numeric)), (COALESCE(auctioninvoice.totaldiscountedamountincents, '0'::numeric)), (COALESCE(auctioninvoice.totalinvoiceamountincents, '0'::numeric)), (COALESCE(auctioninvoice.totalroundingdeviationincents, '0'::numeric)), (sum(tradepayment1.discountedvolumeincents)), (CASE WHEN ((count(trade12.*) = count(trade1.*)) AND (count(trade1.*) > 0)) THEN 'SENT'::text ELSE 'NOT_SENT'::text END), (COALESCE(summary.tradesize, '0'::bigint))
  • Sort Method: quicksort Memory: 27kB
4. 0.019 48,125.186 ↑ 1.0 6 1

Hash Join (cost=5,127,435.63..5,127,437.42 rows=6 width=784) (actual time=48,125.118..48,125.186 rows=6 loops=1)

  • Hash Cond: (auction.id = capfloorau1_.id)
5. 0.020 48,125.154 ↑ 1.0 6 1

Hash Left Join (cost=5,127,434.50..5,127,436.20 rows=6 width=776) (actual time=48,125.093..48,125.154 rows=6 loops=1)

  • Hash Cond: (auction.id = auctioninvoice.auction_id)
6. 0.010 0.214 ↑ 1.0 6 1

Merge Join (cost=202.20..203.82 rows=6 width=352) (actual time=0.155..0.214 rows=6 loops=1)

  • Merge Cond: (auction.id = auction_1.id)
7. 0.058 0.131 ↑ 1.0 6 1

GroupAggregate (cost=113.97..115.09 rows=6 width=104) (actual time=0.092..0.131 rows=6 loops=1)

  • Group Key: auction.id, bundlefilterconfig.currency_id, bundlefilterconfig.auctiondate, bundledesc.settlementdate, bundledesc.auctionmaturitydate, bundlefilterconfig.buyer_id
8. 0.015 0.073 ↑ 1.0 6 1

Sort (cost=113.97..113.98 rows=6 width=10,930) (actual time=0.071..0.073 rows=6 loops=1)

  • Sort Key: auction.id, bundlefilterconfig.currency_id, bundlefilterconfig.auctiondate, bundledesc.settlementdate, bundledesc.auctionmaturitydate, bundlefilterconfig.buyer_id
  • Sort Method: quicksort Memory: 25kB
9. 0.002 0.058 ↑ 1.0 6 1

Nested Loop Left Join (cost=1.71..113.89 rows=6 width=10,930) (actual time=0.035..0.058 rows=6 loops=1)

  • Join Filter: (trade12.id = limitorder.trade_id)
10. 0.000 0.056 ↑ 1.0 6 1

Nested Loop Left Join (cost=1.71..102.29 rows=6 width=9,850) (actual time=0.034..0.056 rows=6 loops=1)

  • Join Filter: (trade11.id = limitorder.trade_id)
11. 0.002 0.052 ↑ 1.0 6 1

Nested Loop Left Join (cost=1.71..91.23 rows=6 width=8,762) (actual time=0.031..0.052 rows=6 loops=1)

  • Join Filter: (trade10.id = limitorder.trade_id)
12. 0.003 0.050 ↑ 1.0 6 1

Nested Loop Left Join (cost=1.71..80.26 rows=6 width=7,674) (actual time=0.030..0.050 rows=6 loops=1)

  • Join Filter: (trade9.id = limitorder.trade_id)
13. 0.002 0.047 ↑ 1.0 6 1

Nested Loop Left Join (cost=1.71..69.20 rows=6 width=6,586) (actual time=0.029..0.047 rows=6 loops=1)

  • Join Filter: (trade8.id = limitorder.trade_id)
14. 0.004 0.045 ↑ 1.0 6 1

Nested Loop Left Join (cost=1.71..58.23 rows=6 width=5,498) (actual time=0.027..0.045 rows=6 loops=1)

  • Join Filter: (trade7.id = limitorder.trade_id)
15. 0.007 0.041 ↑ 1.0 6 1

Nested Loop (cost=1.71..47.17 rows=6 width=4,410) (actual time=0.024..0.041 rows=6 loops=1)

  • Join Filter: (bundledesc.id = auction.bundledescriptor_id)
  • Rows Removed by Join Filter: 30
16. 0.009 0.016 ↑ 1.0 6 1

Hash Join (cost=1.14..2.28 rows=6 width=36) (actual time=0.014..0.016 rows=6 loops=1)

  • Hash Cond: (bundledesc.bundlingfilterconfig_id = bundlefilterconfig.id)
17. 0.001 0.001 ↑ 1.0 6 1

Seq Scan on bundledescriptor bundledesc (cost=0.00..1.06 rows=6 width=24) (actual time=0.001..0.001 rows=6 loops=1)

18. 0.005 0.006 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=28) (actual time=0.006..0.006 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.001 0.001 ↑ 1.0 6 1

Seq Scan on bundlingfilterconfig bundlefilterconfig (cost=0.00..1.06 rows=6 width=28) (actual time=0.001..0.001 rows=6 loops=1)

20. 0.010 0.018 ↑ 1.0 6 6

Materialize (cost=0.57..44.37 rows=6 width=4,390) (actual time=0.001..0.003 rows=6 loops=6)

21. 0.002 0.008 ↑ 1.0 6 1

Nested Loop Left Join (cost=0.57..44.34 rows=6 width=4,390) (actual time=0.005..0.008 rows=6 loops=1)

  • Join Filter: (limitorder.auction_id = auction.id)
22. 0.000 0.000 ↑ 1.0 6 1

Seq Scan on capfloorauction auction (cost=0.00..1.06 rows=6 width=30) (actual time=0.000..0.000 rows=6 loops=1)

23. 0.005 0.006 ↓ 0.0 0 6

Materialize (cost=0.57..43.19 rows=1 width=4,368) (actual time=0.001..0.001 rows=0 loops=6)

24. 0.000 0.001 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.57..43.19 rows=1 width=4,368) (actual time=0.001..0.001 rows=0 loops=1)

25. 0.000 0.001 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.43..35.02 rows=1 width=3,280) (actual time=0.001..0.001 rows=0 loops=1)

26. 0.000 0.001 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.29..26.84 rows=1 width=2,192) (actual time=0.001..0.001 rows=0 loops=1)

27. 0.001 0.001 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.14..18.67 rows=1 width=1,104) (actual time=0.001..0.001 rows=0 loops=1)

28. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on limitorder (cost=0.00..10.50 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: ((orderstate)::text = 'EXECUTED'::text)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using trade_pkey on trade trade1 (cost=0.14..8.16 rows=1 width=1,096) (never executed)

  • Index Cond: (id = limitorder.trade_id)
30. 0.000 0.000 ↓ 0.0 0

Index Scan using trade_pkey on trade trade4 (cost=0.14..8.16 rows=1 width=1,096) (never executed)

  • Index Cond: (id = limitorder.trade_id)
  • Filter: ((tradestate)::text = ANY ('{ALLOCATED,CONFIRMED}'::text[]))
31. 0.000 0.000 ↓ 0.0 0

Index Scan using trade_pkey on trade trade5 (cost=0.14..8.16 rows=1 width=1,096) (never executed)

  • Index Cond: (id = limitorder.trade_id)
  • Filter: ((tradestate)::text = ANY ('{SETTLED,INVESTOR_DISPUTE_CLOSED}'::text[]))
32. 0.000 0.000 ↓ 0.0 0

Index Scan using trade_pkey on trade trade6 (cost=0.14..8.16 rows=1 width=1,096) (never executed)

  • Index Cond: (id = limitorder.trade_id)
  • Filter: ((tradestate)::text = ANY ('{ALLOCATED,CONFIRMED,DISPUTE_WITH_INVESTOR}'::text[]))
33. 0.000 0.000 ↓ 0.0 0 6

Materialize (cost=0.00..10.88 rows=2 width=1,096) (actual time=0.000..0.000 rows=0 loops=6)

34. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on trade trade7 (cost=0.00..10.88 rows=2 width=1,096) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: ((tradestate)::text = ANY ('{PAID_BACK,SPV_DISPUTE_CLOSED}'::text[]))
35. 0.000 0.000 ↓ 0.0 0 6

Materialize (cost=0.00..10.88 rows=1 width=1,096) (actual time=0.000..0.000 rows=0 loops=6)

36. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on trade trade8 (cost=0.00..10.88 rows=1 width=1,096) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: ((tradestate)::text = 'SETTLEMENT_INSTRUCTIONS_SENT'::text)
37. 0.000 0.000 ↓ 0.0 0 6

Materialize (cost=0.00..10.88 rows=2 width=1,096) (actual time=0.000..0.000 rows=0 loops=6)

38. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on trade trade9 (cost=0.00..10.88 rows=2 width=1,096) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: ((tradestate)::text = ANY ('{CONFIRMED,SETTLEMENT_INSTRUCTIONS_SENT}'::text[]))
39. 0.000 0.000 ↓ 0.0 0 6

Materialize (cost=0.00..10.88 rows=1 width=1,096) (actual time=0.000..0.000 rows=0 loops=6)

40. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on trade trade10 (cost=0.00..10.88 rows=1 width=1,096) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((tradestate)::text = 'DISPUTE_WITH_ORIGINATOR'::text)
41. 0.006 0.006 ↓ 0.0 0 6

Materialize (cost=0.00..10.88 rows=2 width=1,096) (actual time=0.001..0.001 rows=0 loops=6)

42. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on trade trade11 (cost=0.00..10.88 rows=2 width=1,096) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: ((tradestate)::text = ANY ('{MATURITY_PAYMENT_RECEIVED,ORIGINATOR_DISPUTE_CLOSED}'::text[]))
43. 0.000 0.000 ↓ 0.0 0 6

Materialize (cost=0.00..11.16 rows=5 width=1,096) (actual time=0.000..0.000 rows=0 loops=6)

44. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on trade trade12 (cost=0.00..11.14 rows=5 width=1,096) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: ((tradestate)::text = ANY ('{SETTLEMENT_INSTRUCTIONS_SENT,SETTLED,DISPUTE_WITH_INVESTOR,INVESTOR_DISPUTE_CLOSED,PAID_BACK}'::text[]))
45. 0.004 0.073 ↑ 1.0 6 1

Materialize (cost=88.23..88.58 rows=6 width=248) (actual time=0.059..0.073 rows=6 loops=1)

46. 0.013 0.069 ↑ 1.0 6 1

GroupAggregate (cost=88.23..88.50 rows=6 width=392) (actual time=0.058..0.069 rows=6 loops=1)

  • Group Key: auction_1.id, summary.summaxvolumeincents, summary.sumopeninterest, summary.executedspread, summary.sumexecutedamountincents, summary.tradesize, summary.ordersplacedtotal
47. 0.013 0.056 ↑ 1.0 6 1

Sort (cost=88.23..88.25 rows=6 width=176) (actual time=0.054..0.056 rows=6 loops=1)

  • Sort Key: auction_1.id, summary.summaxvolumeincents, summary.sumopeninterest, summary.executedspread, summary.sumexecutedamountincents, summary.tradesize, summary.ordersplacedtotal
  • Sort Method: quicksort Memory: 25kB
48. 0.009 0.043 ↑ 1.0 6 1

Hash Left Join (cost=43.10..88.15 rows=6 width=176) (actual time=0.039..0.043 rows=6 loops=1)

  • Hash Cond: (auction_1.id = summary.auction_id)
49. 0.000 0.004 ↑ 1.0 6 1

Nested Loop Left Join (cost=0.88..45.85 rows=6 width=32) (actual time=0.003..0.004 rows=6 loops=1)

  • Join Filter: (limitorder_1.auction_id = auction_1.id)
50. 0.001 0.001 ↑ 1.0 6 1

Seq Scan on capfloorauction auction_1 (cost=0.00..1.06 rows=6 width=8) (actual time=0.001..0.001 rows=6 loops=1)

51. 0.004 0.006 ↓ 0.0 0 6

Materialize (cost=0.88..44.70 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=6)

52. 0.001 0.002 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.88..44.70 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)

53. 0.000 0.001 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.73..39.52 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=1)

54. 0.000 0.001 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.58..31.35 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=1)

55. 0.000 0.001 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.43..27.67 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=1)

56. 0.001 0.001 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.29..19.49 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=1)

57. 0.000 0.000 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.14..18.67 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=1)

58. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on limitorder limitorder_1 (cost=0.00..10.50 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: ((orderstate)::text = 'EXECUTED'::text)
59. 0.000 0.000 ↓ 0.0 0

Index Scan using trade_pkey on trade trade1_1 (cost=0.14..8.16 rows=1 width=16) (never executed)

  • Index Cond: (id = limitorder_1.trade_id)
  • Filter: ((tradestate)::text <> 'CANCELED'::text)
60. 0.000 0.000 ↓ 0.0 0

Index Scan using tradepayment_pkey on tradepayment tradepayment1 (cost=0.15..0.81 rows=1 width=16) (never executed)

  • Index Cond: (trade1_1.tradepayment_id = id)
61. 0.000 0.000 ↓ 0.0 0

Index Scan using trade_pkey on trade trade2 (cost=0.14..8.17 rows=1 width=16) (never executed)

  • Index Cond: (id = limitorder_1.trade_id)
  • Filter: ((tradestate)::text = ANY ('{SETTLED,INVESTOR_DISPUTE_CLOSED,MATURITY_PAYMENT_RECEIVED,DISPUTE_WITH_ORIGINATOR,ORIGINATOR_DISPUTE_CLOSED,DISPUTE_WITH_SPV,SPV_DISPUTE_CLOSED,PAID_BACK}'::text[]))
62. 0.000 0.000 ↓ 0.0 0

Index Scan using tradepayment_pkey on tradepayment tradepayment2 (cost=0.15..3.67 rows=1 width=16) (never executed)

  • Index Cond: (trade2.tradepayment_id = id)
63. 0.000 0.000 ↓ 0.0 0

Index Scan using trade_pkey on trade trade3 (cost=0.14..8.17 rows=1 width=16) (never executed)

  • Index Cond: (id = limitorder_1.trade_id)
  • Filter: ((tradestate)::text = ANY ('{ALLOCATED,CONFIRMED,SETTLEMENT_INSTRUCTIONS_SENT,DISPUTE_WITH_INVESTOR}'::text[]))
64. 0.000 0.000 ↓ 0.0 0

Index Scan using tradepayment_pkey on tradepayment tradepayment3 (cost=0.15..5.17 rows=1 width=16) (never executed)

  • Index Cond: (trade3.tradepayment_id = id)
65. 0.001 0.030 ↓ 0.0 0 1

Hash (cost=41.72..41.72 rows=40 width=152) (actual time=0.030..0.030 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
66. 0.000 0.029 ↑ 40.0 1 1

Subquery Scan on summary (cost=40.52..41.72 rows=40 width=152) (actual time=0.029..0.029 rows=1 loops=1)

67. 0.018 0.029 ↑ 40.0 1 1

HashAggregate (cost=40.52..41.32 rows=40 width=152) (actual time=0.029..0.029 rows=1 loops=1)

  • Group Key: limitorder_2.auction_id
68. 0.005 0.011 ↑ 6.7 6 1

Nested Loop Left Join (cost=0.43..38.52 rows=40 width=1,060) (actual time=0.006..0.011 rows=6 loops=1)

69. 0.000 0.000 ↑ 1.0 6 1

Seq Scan on capfloorauction auction_2 (cost=0.00..1.06 rows=6 width=8) (actual time=0.000..0.000 rows=6 loops=1)

70. 0.000 0.006 ↓ 0.0 0 6

Nested Loop Left Join (cost=0.43..6.23 rows=1 width=1,060) (actual time=0.001..0.001 rows=0 loops=6)

71. 0.000 0.006 ↓ 0.0 0 6

Nested Loop Left Join (cost=0.28..5.43 rows=1 width=1,060) (actual time=0.001..0.001 rows=0 loops=6)

72. 0.006 0.006 ↓ 0.0 0 6

Index Scan using limitorder_auction_idx on limitorder limitorder_2 (cost=0.14..4.16 rows=1 width=544) (actual time=0.001..0.001 rows=0 loops=6)

  • Index Cond: (auction_id = auction_2.id)
73. 0.000 0.000 ↓ 0.0 0

Index Scan using trade_pkey on trade (cost=0.14..1.26 rows=1 width=532) (never executed)

  • Index Cond: (id = limitorder_2.trade_id)
74. 0.000 0.000 ↓ 0.0 0

Index Scan using tradepayment_pkey on tradepayment (cost=0.15..0.80 rows=1 width=16) (never executed)

  • Index Cond: (id = trade.tradepayment_id)
75. 0.013 48,124.920 ↑ 1.0 6 1

Hash (cost=5,127,232.22..5,127,232.22 rows=6 width=432) (actual time=48,124.920..48,124.920 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
76. 0.003 48,124.907 ↑ 1.0 6 1

Subquery Scan on auctioninvoice (cost=5,127,231.91..5,127,232.22 rows=6 width=432) (actual time=48,124.892..48,124.907 rows=6 loops=1)

77. 3,602.787 48,124.904 ↑ 1.0 6 1

HashAggregate (cost=5,127,231.91..5,127,232.16 rows=6 width=432) (actual time=48,124.890..48,124.904 rows=6 loops=1)

  • Group Key: auction_3.id
78. 8,542.812 44,522.117 ↑ 1.2 5,292,049 1

Hash Left Join (cost=1,228,597.96..4,864,331.63 rows=6,572,507 width=295) (actual time=10,021.495..44,522.117 rows=5,292,049 loops=1)

  • Hash Cond: (collected_invoices.id = collected_invoices_earlypayments.invoice_id)
79. 1,713.347 32,488.447 ↑ 1.2 5,292,049 1

Hash Join (cost=803,516.62..3,771,993.39 rows=6,572,507 width=287) (actual time=6,530.354..32,488.447 rows=5,292,049 loops=1)

  • Hash Cond: (all_invoices.lastauction_id = auction_3.id)
80. 5,069.873 30,775.095 ↑ 1.0 6,570,050 1

Merge Left Join (cost=803,515.48..3,694,007.27 rows=6,572,507 width=287) (actual time=6,467.312..30,775.095 rows=6,570,050 loops=1)

  • Merge Cond: (all_invoices.id = all_invoices_earlypayments.invoice_id)
81. 2,347.224 24,284.600 ↑ 1.0 6,570,050 1

Merge Left Join (cost=803,515.05..3,188,174.81 rows=6,572,507 width=231) (actual time=6,467.299..24,284.600 rows=6,570,050 loops=1)

  • Merge Cond: (all_invoices.id = collected_invoices.id)
82. 1,312.809 19,969.225 ↑ 1.0 6,570,050 1

Merge Left Join (cost=803,514.62..2,552,229.46 rows=6,572,507 width=223) (actual time=6,363.812..19,969.225 rows=6,570,050 loops=1)

  • Merge Cond: (all_invoices.id = open_invoices.id)
83. 2,516.958 12,080.137 ↑ 1.0 6,570,050 1

Merge Left Join (cost=1.30..1,723,658.46 rows=6,572,507 width=203) (actual time=16.289..12,080.137 rows=6,570,050 loops=1)

  • Merge Cond: (all_invoices.id = paid_invoices.id)
84. 2,105.753 2,105.753 ↑ 1.0 6,570,050 1

Index Scan using supplierinvoice_pkey on supplierinvoice all_invoices (cost=0.43..530,892.04 rows=6,572,507 width=195) (actual time=0.013..2,105.753 rows=6,570,050 loops=1)

85. 1,027.334 7,457.426 ↑ 1.0 6,249,662 1

Materialize (cost=0.86..1,097,982.66 rows=6,268,200 width=16) (actual time=16.275..7,457.426 rows=6,249,662 loops=1)

86. 3,399.392 6,430.092 ↑ 1.0 6,249,662 1

Merge Left Join (cost=0.86..1,082,312.16 rows=6,268,200 width=16) (actual time=16.273..6,430.092 rows=6,249,662 loops=1)

  • Merge Cond: (paid_invoices.id = paid_invoices_earlypayments.invoice_id)
87. 2,021.945 2,021.945 ↑ 1.0 6,249,662 1

Index Scan using supplierinvoice_pkey on supplierinvoice paid_invoices (cost=0.43..580,185.84 rows=6,268,200 width=8) (actual time=16.256..2,021.945 rows=6,249,662 loops=1)

  • Filter: (state = ANY ('{280,250,300,310,320,330}'::integer[]))
  • Rows Removed by Filter: 320388
88. 1,008.755 1,008.755 ↑ 1.0 6,361,482 1

Index Scan using earlypaymentsettlement_invoice_idx on earlypaymentsettlement paid_invoices_earlypayments (cost=0.43..409,882.66 rows=6,361,482 width=16) (actual time=0.007..1,008.755 rows=6,361,482 loops=1)

89. 61.059 6,576.279 ↑ 1.0 487,996 1

Materialize (cost=803,513.32..805,978.01 rows=492,938 width=28) (actual time=6,347.521..6,576.279 rows=487,996 loops=1)

90. 309.465 6,515.220 ↑ 1.0 487,996 1

Sort (cost=803,513.32..804,745.66 rows=492,938 width=28) (actual time=6,347.515..6,515.220 rows=487,996 loops=1)

  • Sort Key: open_invoices.id
  • Sort Method: external merge Disk: 20000kB
91. 3,294.339 6,205.755 ↑ 1.0 487,996 1

Hash Right Join (cost=331,236.96..745,108.45 rows=492,938 width=28) (actual time=1,051.234..6,205.755 rows=487,996 loops=1)

  • Hash Cond: (open_invoices_earlypayments.invoice_id = open_invoices.id)
92. 1,860.936 1,860.936 ↑ 1.0 6,361,482 1

Seq Scan on earlypaymentsettlement open_invoices_earlypayments (cost=0.00..308,287.82 rows=6,361,482 width=24) (actual time=0.115..1,860.936 rows=6,361,482 loops=1)

93. 85.054 1,050.480 ↑ 1.0 487,996 1

Hash (cost=322,668.24..322,668.24 rows=492,938 width=12) (actual time=1,050.480..1,050.480 rows=487,996 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3891kB
94. 965.426 965.426 ↑ 1.0 487,996 1

Seq Scan on supplierinvoice open_invoices (cost=0.00..322,668.24 rows=492,938 width=12) (actual time=60.366..965.426 rows=487,996 loops=1)

  • Filter: (state = ANY ('{50,280,220,300,320}'::integer[]))
  • Rows Removed by Filter: 6082054
95. 1,968.151 1,968.151 ↑ 1.0 5,761,641 1

Index Scan using supplierinvoice_pkey on supplierinvoice collected_invoices (cost=0.43..547,323.31 rows=5,775,262 width=8) (actual time=103.483..1,968.151 rows=5,761,641 loops=1)

  • Filter: (state = ANY ('{310,330}'::integer[]))
  • Rows Removed by Filter: 808409
96. 1,420.622 1,420.622 ↑ 1.0 6,361,482 1

Index Scan using earlypaymentsettlement_invoice_idx on earlypaymentsettlement all_invoices_earlypayments (cost=0.43..409,882.66 rows=6,361,482 width=72) (actual time=0.009..1,420.622 rows=6,361,482 loops=1)

97. 0.004 0.005 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=8) (actual time=0.005..0.005 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
98. 0.001 0.001 ↑ 1.0 6 1

Seq Scan on capfloorauction auction_3 (cost=0.00..1.06 rows=6 width=8) (actual time=0.001..0.001 rows=6 loops=1)

99. 1,516.056 3,490.858 ↑ 1.0 6,361,482 1

Hash (cost=308,287.82..308,287.82 rows=6,361,482 width=24) (actual time=3,490.858..3,490.858 rows=6,361,482 loops=1)

  • Buckets: 65536 Batches: 128 Memory Usage: 3227kB
100. 1,974.802 1,974.802 ↑ 1.0 6,361,482 1

Seq Scan on earlypaymentsettlement collected_invoices_earlypayments (cost=0.00..308,287.82 rows=6,361,482 width=24) (actual time=0.098..1,974.802 rows=6,361,482 loops=1)

101. 0.007 0.013 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=8) (actual time=0.013..0.013 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
102. 0.006 0.006 ↑ 1.0 6 1

Seq Scan on capfloorauction capfloorau1_ (cost=0.00..1.06 rows=6 width=8) (actual time=0.005..0.006 rows=6 loops=1)