explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OYEn

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

Gather (cost=2,117.41..1,786,964.28 rows=64,156 width=16) (actual time=165.696..108,423.815 rows=1,013,768 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 1,048.683 72,055.842 ↓ 12.6 337,923 3 / 3

Hash Left Join (cost=1,117.41..1,339,919.69 rows=26,732 width=31) (actual time=167.187..72,055.842 rows=337,923 loops=3)

  • Hash Cond: (cntr."CurrencyId" = curr."CurrencyId")
3. 1,126.015 71,006.523 ↓ 12.6 337,923 3 / 3

Hash Left Join (cost=1,111.29..1,339,841.80 rows=26,732 width=32) (actual time=166.524..71,006.523 rows=337,923 loops=3)

  • Hash Cond: (el."ExpenseLineCountryId" = cntr."CountryId")
4. 1,575.706 69,879.626 ↓ 12.6 337,923 3 / 3

Nested Loop (cost=1,102.35..1,339,761.58 rows=26,732 width=32) (actual time=165.534..69,879.626 rows=337,923 loops=3)

5. 8,369.199 28,043.720 ↓ 12.8 67,100 3 / 3

Hash Join (cost=1,101.79..484,529.23 rows=5,255 width=4) (actual time=161.066..28,043.720 rows=67,100 loops=3)

  • Hash Cond: (er."EntityId" = entity."CustomerId")
6. 19,673.033 19,673.033 ↑ 1.3 5,982,197 3 / 3

Parallel Seq Scan on "IneligibleExpenseReport" er (cost=0.00..463,795.53 rows=7,478,753 width=12) (actual time=2.710..19,673.033 rows=5,982,197 loops=3)

7. 0.302 1.488 ↑ 1.6 199 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
8. 0.966 1.186 ↑ 1.6 199 3 / 3

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

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

BitmapOr (cost=11.41..11.41 rows=321 width=0) (actual time=0.219..0.220 rows=0 loops=3)

10. 0.104 0.104 ↑ 1.0 1 3 / 3

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

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

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

  • Index Cond: ("HeadQuarterId" = 70,283,727)
12. 40,260.200 40,260.200 ↑ 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.238..0.600 rows=5 loops=201,301)

  • Index Cond: ("ExpenseReportId" = er."ExpenseReportId")
13. 0.398 0.882 ↑ 1.0 264 3 / 3

Hash (cost=5.64..5.64 rows=264 width=8) (actual time=0.881..0.882 rows=264 loops=3)

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

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

15. 0.307 0.636 ↓ 1.0 191 3 / 3

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

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

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

17.          

SubPlan (for Gather)

18. 39,536.952 39,536.952 ↑ 1.0 1 1,013,768

Seq Scan on "OTSReportingExchangeRate" ots (cost=0.00..6.85 rows=1 width=8) (actual time=0.015..0.039 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 : 14.580 ms
Execution time : 109,716.363 ms