explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q7LJ

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

GroupAggregate (cost=2,019.22..2,036.88 rows=321 width=465) (actual time=4,078.237..6,021.811 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. 4,661.080 5,164.602 ↓ 1,591.2 510,767 1

Sort (cost=2,019.22..2,020.02 rows=321 width=423) (actual time=4,078.140..5,164.602 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. 138.942 503.522 ↓ 1,591.2 510,767 1

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

4. 1.433 41.881 ↓ 101.1 3,133 1

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

5. 0.380 17.549 ↓ 43.5 1,347 1

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

6. 0.037 0.037 ↑ 1.0 1 1

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

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

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

8. 1.594 2.330 ↓ 6.3 1,347 1

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

  • Hash Cond: (p2.majordecode = p1.row8)
9. 0.713 0.713 ↑ 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.016..0.713 rows=1,347 loops=1)

  • Index Cond: (cat_id = 1)
10. 0.008 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.015 0.015 ↑ 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.007..0.015 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. 322.699 322.699 ↓ 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.103 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 : 16.459 ms
Execution time : 6,032.480 ms