explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JPM5

Settings
# exclusive inclusive rows x rows loops node
1. 0.074 1,115.028 ↑ 1.0 100 1

Limit (cost=2.38..783.85 rows=100 width=650) (actual time=13.220..1,115.028 rows=100 loops=1)

2. 0.331 1,114.954 ↑ 420.8 100 1

Nested Loop Left Join (cost=2.38..328,878.06 rows=42,084 width=650) (actual time=13.218..1,114.954 rows=100 loops=1)

3. 0.155 10.723 ↑ 420.8 100 1

Nested Loop Left Join (cost=1.53..147,762.18 rows=42,084 width=586) (actual time=0.104..10.723 rows=100 loops=1)

4. 0.848 2.068 ↑ 420.8 100 1

Merge Full Join (cost=0.70..10,899.20 rows=42,084 width=449) (actual time=0.030..2.068 rows=100 loops=1)

  • Merge Cond: (batchmetadata.id = filemetadata.batch_id)
5. 0.513 0.513 ↑ 195.7 62 1

Index Scan using batchmetadata_pkey on batchmetadata (cost=0.29..2,878.10 rows=12,132 width=175) (actual time=0.010..0.513 rows=62 loops=1)

6. 0.707 0.707 ↑ 462.5 91 1

Index Scan using idx_batch_id on filemetadata (cost=0.41..7,719.15 rows=42,084 width=274) (actual time=0.012..0.707 rows=91 loops=1)

7. 0.720 8.500 ↑ 1.0 1 100

Nested Loop Left Join (cost=0.83..3.24 rows=1 width=137) (actual time=0.079..0.085 rows=1 loops=100)

  • Filter: (x111.id IS NULL)
  • Rows Removed by Filter: 6
8. 2.200 2.200 ↑ 1.0 4 100

Index Scan using idx_batchstatus_batchid on batchstatus x110 (cost=0.41..0.83 rows=4 width=137) (actual time=0.021..0.022 rows=4 loops=100)

  • Index Cond: (CASE WHEN (CASE WHEN (batchmetadata.id IS NULL) THEN NULL::integer ELSE 1 END IS NOT NULL) THEN batchmetadata.id ELSE NULL::text END = batch_id)
  • Filter: external
  • Rows Removed by Filter: 0
9. 5.580 5.580 ↓ 2.0 2 372

Index Scan using idx_batchstatus_batchid on batchstatus x111 (cost=0.41..0.59 rows=1 width=49) (actual time=0.014..0.015 rows=2 loops=372)

  • Index Cond: (x110.batch_id = batch_id)
  • Filter: (x110."timestamp" < "timestamp")
  • Rows Removed by Filter: 2
10. 1.040 1,103.900 ↑ 1.0 1 100

Nested Loop Left Join (cost=0.84..4.29 rows=1 width=64) (actual time=11.029..11.039 rows=1 loops=100)

  • Filter: (x108.id IS NULL)
  • Rows Removed by Filter: 5
11. 534.600 534.600 ↑ 1.3 3 100

Index Scan using idx_filestatus_filemetadata_id on filestatus x107 (cost=0.42..1.22 rows=4 width=64) (actual time=5.343..5.346 rows=3 loops=100)

  • Index Cond: (CASE WHEN (CASE WHEN (filemetadata.id IS NULL) THEN NULL::integer ELSE 1 END IS NOT NULL) THEN filemetadata.id ELSE NULL::text END = filemetadata_id)
  • Filter: external
  • Rows Removed by Filter: 4
12. 568.260 568.260 ↓ 2.0 2 330

Index Scan using idx_filestatus_filemetadata_id on filestatus x108 (cost=0.42..0.76 rows=1 width=49) (actual time=0.835..1.722 rows=2 loops=330)

  • Index Cond: (x107.filemetadata_id = filemetadata_id)
  • Filter: (external AND (x107."timestamp" < "timestamp"))
  • Rows Removed by Filter: 7
Total runtime : 1,115.186 ms