explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bWqf

Settings
# exclusive inclusive rows x rows loops node
1. 1,030.544 6,811.417 ↓ 114.8 36,857 1

GroupAggregate (cost=2,019.22..2,036.88 rows=321 width=465) (actual time=4,470.649..6,811.417 rows=36,857 loops=1)

  • Group Key: d.one_two, p1a.row3, p1a.row3_dates, p1.row4, p1.row4_dates, p1.row5, p1.row5_dates, (CASE WHEN (p2.cat_id < 100) THEN m.filter4 ELSE p2.row4 END), (CASE WHEN (p2.cat_id < 100) THEN m.filter4_dates ELSE p2.row4_dates END), d.row17, d.row17_dates
2. 5,256.961 5,780.873 ↓ 1,591.2 510,767 1

Sort (cost=2,019.22..2,020.02 rows=321 width=423) (actual time=4,470.532..5,780.873 rows=510,767 loops=1)

  • Sort Key: d.one_two, p1a.row3, p1a.row3_dates, p1.row4, p1.row4_dates, p1.row5, p1.row5_dates, (CASE WHEN (p2.cat_id < 100) THEN m.filter4 ELSE p2.row4 END), (CASE WHEN (p2.cat_id < 100) THEN m.filter4_dates ELSE p2.row4_dates END), d.row17, d.row17_dates
  • Sort Method: external merge Disk: 72056kB
3. 146.997 523.912 ↓ 1,591.2 510,767 1

Nested Loop (cost=10.76..2,005.86 rows=321 width=423) (actual time=0.114..523.912 rows=510,767 loops=1)

4. 1.393 41.684 ↓ 101.1 3,133 1

Nested Loop Left Join (cost=10.32..1,270.52 rows=31 width=423) (actual time=0.102..41.684 rows=3,133 loops=1)

5. 0.367 17.392 ↓ 43.5 1,347 1

Nested Loop (cost=9.91..1,122.08 rows=31 width=399) (actual time=0.090..17.392 rows=1,347 loops=1)

6. 0.039 0.039 ↑ 1.0 1 1

Seq Scan on part1a p1a (cost=0.00..4.03 rows=1 width=58) (actual time=0.026..0.039 rows=1 loops=1)

  • Filter: (cat_id = 1)
  • Rows Removed by Filter: 161
7. 1.323 16.986 ↓ 43.5 1,347 1

Nested Loop (cost=9.91..1,117.75 rows=31 width=345) (actual time=0.061..16.986 rows=1,347 loops=1)

8. 1.560 2.193 ↓ 6.3 1,347 1

Hash Join (cost=9.49..142.70 rows=213 width=343) (actual time=0.046..2.193 rows=1,347 loops=1)

  • Hash Cond: (p2.majordecode = p1.row8)
9. 0.610 0.610 ↑ 1.0 1,347 1

Index Scan using page2_cat_id_idx on page2 p2 (cost=0.42..84.36 rows=1,347 width=106) (actual time=0.018..0.610 rows=1,347 loops=1)

  • Index Cond: (cat_id = 1)
10. 0.010 0.023 ↑ 1.0 26 1

Hash (cost=8.74..8.74 rows=26 width=250) (actual time=0.023..0.023 rows=26 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
11. 0.013 0.013 ↑ 1.0 26 1

Index Scan using page1_cat_id_idx on page1 p1 (cost=0.29..8.74 rows=26 width=250) (actual time=0.006..0.013 rows=26 loops=1)

  • Index Cond: (cat_id = 1)
12. 13.470 13.470 ↑ 1.0 1 1,347

Index Scan using f1f2_cat_id_sect_idx on f1f2 f (cost=0.42..4.57 rows=1 width=22) (actual time=0.010..0.010 rows=1 loops=1,347)

  • Index Cond: ((cat_id = 1) AND (sect = p2.row1))
13. 22.899 22.899 ↓ 2.0 2 1,347

Index Scan using ms_key1_idx on ms m (cost=0.42..4.78 rows=1 width=39) (actual time=0.012..0.017 rows=2 loops=1,347)

  • Index Cond: (key1 = p2.image_id)
  • Filter: ((key2 = 'C'::text) AND ((key3 = 'A'::text) OR (key3 = 'ALL'::text)))
  • Rows Removed by Filter: 8
14. 335.231 335.231 ↓ 32.6 163 3,133

Index Scan using part2_cat_id_f1_f2_idx on part2 d (cost=0.43..23.62 rows=5 width=76) (actual time=0.007..0.107 rows=163 loops=3,133)

  • Index Cond: ((cat_id = 1) AND (f1 = f.f1) AND (f2 = f.f2))
  • Filter: (number_n IS NOT NULL)
  • Rows Removed by Filter: 5
Planning time : 18.467 ms
Execution time : 6,850.540 ms