explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 74Az

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 73,490.529 ↓ 15.8 1,013,768 1

Gather (cost=2,123.31..1,652,296.60 rows=64,156 width=16) (actual time=191.222..73,490.529 rows=1,013,768 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2.          

CTE exchange_rates

3. 0.295 0.295 ↑ 1.0 190 1

Seq Scan on "OTSReportingExchangeRate" (cost=0.00..5.90 rows=190 width=11) (actual time=0.032..0.295 rows=190 loops=1)

4. 963.891 53,080.214 ↓ 12.6 337,923 3 / 3

Hash Left Join (cost=1,117.41..1,339,973.71 rows=26,732 width=31) (actual time=193.024..53,080.214 rows=337,923 loops=3)

  • Hash Cond: (cntr."CurrencyId" = curr."CurrencyId")
5. 1,032.044 52,115.764 ↓ 12.6 337,923 3 / 3

Hash Left Join (cost=1,111.29..1,339,895.81 rows=26,732 width=32) (actual time=192.434..52,115.764 rows=337,923 loops=3)

  • Hash Cond: (el."ExpenseLineCountryId" = cntr."CountryId")
6. 1,434.778 51,082.694 ↓ 12.6 337,923 3 / 3

Nested Loop (cost=1,102.35..1,339,815.59 rows=26,732 width=32) (actual time=191.300..51,082.694 rows=337,923 loops=3)

7. 8,334.203 20,459.271 ↓ 12.8 67,100 3 / 3

Hash Join (cost=1,101.79..484,549.11 rows=5,255 width=4) (actual time=133.413..20,459.271 rows=67,100 loops=3)

  • Hash Cond: (er."EntityId" = entity."CustomerId")
8. 12,123.129 12,123.129 ↑ 1.3 5,982,344 3 / 3

Parallel Seq Scan on "IneligibleExpenseReport" er (cost=0.00..463,814.61 rows=7,479,061 width=12) (actual time=0.078..12,123.129 rows=5,982,344 loops=3)

9. 0.559 1.939 ↑ 1.6 199 3 / 3

Hash (cost=1,097.77..1,097.77 rows=321 width=8) (actual time=1.938..1.939 rows=199 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
10. 1.163 1.380 ↑ 1.6 199 3 / 3

Bitmap Heap Scan on "Customer" entity (cost=11.41..1,097.77 rows=321 width=8) (actual time=0.249..1.380 rows=199 loops=3)

  • Recheck Cond: (("CustomerId" = 70,283,727) OR ("HeadQuarterId" = 70,283,727))
  • Heap Blocks: exact=43
11. 0.006 0.217 ↓ 0.0 0 3 / 3

BitmapOr (cost=11.41..11.41 rows=321 width=0) (actual time=0.216..0.217 rows=0 loops=3)

12. 0.090 0.090 ↑ 1.0 1 3 / 3

Bitmap Index Scan on "CustomerId" (cost=0.00..4.43 rows=1 width=0) (actual time=0.089..0.090 rows=1 loops=3)

  • Index Cond: ("CustomerId" = 70,283,727)
13. 0.121 0.121 ↑ 1.6 198 3 / 3

Bitmap Index Scan on "ix_Customer_HeadQuarterId" (cost=0.00..6.82 rows=320 width=0) (actual time=0.120..0.121 rows=198 loops=3)

  • Index Cond: ("HeadQuarterId" = 70,283,727)
14. 29,188.645 29,188.645 ↑ 188.8 5 201,301 / 3

Index Scan using "fki_IneligibleExpenseLine_ExpenseReport" on "IneligibleExpenseLine" el (cost=0.57..153.31 rows=944 width=36) (actual time=0.152..0.435 rows=5 loops=201,301)

  • Index Cond: ("ExpenseReportId" = er."ExpenseReportId")
15. 0.393 1.026 ↑ 1.0 264 3 / 3

Hash (cost=5.64..5.64 rows=264 width=8) (actual time=1.025..1.026 rows=264 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
16. 0.633 0.633 ↑ 1.0 264 3 / 3

Seq Scan on "Country" cntr (cost=0.00..5.64 rows=264 width=8) (actual time=0.038..0.633 rows=264 loops=3)

17. 0.264 0.559 ↓ 1.0 191 3 / 3

Hash (cost=3.83..3.83 rows=183 width=7) (actual time=0.558..0.559 rows=191 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
18. 0.295 0.295 ↓ 1.0 191 3 / 3

Seq Scan on "Currency" curr (cost=0.00..3.83 rows=183 width=7) (actual time=0.045..0.295 rows=191 loops=3)

19.          

SubPlan (for Gather)

20. 35,481.880 35,481.880 ↑ 1.0 1 1,013,768

CTE Scan on exchange_rates ots (cost=0.00..4.75 rows=1 width=8) (actual time=0.012..0.035 rows=1 loops=1,013,768)

  • Filter: (((el."TransactionCurrencyCode" IS NOT NULL) AND (("Currency")::text = (el."TransactionCurrencyCode")::text)) OR ((el."TransactionCurrencyCode" IS NULL) AND (("Currency")::text = (curr."CurrencyPrefix")::text)))
  • Rows Removed by Filter: 189
Planning time : 2.593 ms
Execution time : 74,661.545 ms