explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MeXG

Settings
# exclusive inclusive rows x rows loops node
1. 209.551 12,713.008 ↑ 5.6 36 1

HashAggregate (cost=937,951.11..937,954.61 rows=200 width=60) (actual time=12,712.964..12,713.008 rows=36 loops=1)

  • Group Key: loc.areacode
2.          

CTE loc

3. 43.440 44.544 ↓ 1.1 10,237 1

Bitmap Heap Scan on filtertable_apt_loc (cost=862.25..4,360.34 rows=9,660 width=44) (actual time=1.166..44.544 rows=10,237 loops=1)

  • Recheck Cond: (siggcode = ANY ('{11260,11380,11440,11110,11140,11470,11500,41281,41285,41390,11530,41210,11650,11545,11560,11170,11410,11590,11620,11290,11305,41290,11320,11680,11200,11350,11710,11215,11230,41131,41450,11740,41310,41360,41610,41133}'::integer[]))
  • Filter: ((wgs84)::text <> 'ST_Point(0, 0)'::text)
  • Heap Blocks: exact=409
4. 1.104 1.104 ↓ 1.1 10,237 1

Bitmap Index Scan on filtertable_apt_loc_siggcode (cost=0.00..859.84 rows=9,709 width=0) (actual time=1.104..1.104 rows=10,237 loops=1)

  • Index Cond: (siggcode = ANY ('{11260,11380,11440,11110,11140,11470,11500,41281,41285,41390,11530,41210,11650,11545,11560,11170,11410,11590,11620,11290,11305,41290,11320,11680,11200,11350,11710,11215,11230,41131,41450,11740,41310,41360,41610,41133}'::integer[]))
5.          

CTE prefiltered

6. 825.003 10,322.122 ↑ 44.6 258,020 1

Nested Loop (cost=245.00..644,904.53 rows=11,518,110 width=16) (actual time=107.503..10,322.122 rows=258,020 loops=1)

7. 40.516 120.027 ↓ 51.2 10,237 1

HashAggregate (cost=217.35..219.35 rows=200 width=8) (actual time=107.424..120.027 rows=10,237 loops=1)

  • Group Key: loc_1.hid
8. 79.511 79.511 ↓ 1.1 10,237 1

CTE Scan on loc loc_1 (cost=0.00..193.20 rows=9,660 width=8) (actual time=0.001..79.511 rows=10,237 loops=1)

9. 8,353.392 9,377.092 ↑ 37.6 25 10,237

Bitmap Heap Scan on filtertable_apt_filter (cost=27.65..3,214.02 rows=941 width=16) (actual time=0.766..0.916 rows=25 loops=10,237)

  • Recheck Cond: (hid = loc_1.hid)
  • Heap Blocks: exact=2055169
10. 1,023.700 1,023.700 ↑ 4.6 203 10,237

Bitmap Index Scan on filtertable_apt_filter_idx_hid (cost=0.00..27.42 rows=941 width=0) (actual time=0.100..0.100 rows=203 loops=10,237)

  • Index Cond: (hid = loc_1.hid)
11.          

CTE joined

12. 752.520 12,291.268 ↓ 51.2 10,237 1

HashAggregate (cost=287,952.75..287,955.25 rows=200 width=40) (actual time=12,257.545..12,291.268 rows=10,237 loops=1)

  • Group Key: prefiltered.hid
13. 11,538.748 11,538.748 ↑ 44.6 258,020 1

CTE Scan on prefiltered (cost=0.00..230,362.20 rows=11,518,110 width=16) (actual time=107.505..11,538.748 rows=258,020 loops=1)

14. 51.932 12,503.457 ↓ 1.1 10,237 1

Hash Join (cost=6.50..561.95 rows=9,660 width=68) (actual time=12,442.925..12,503.457 rows=10,237 loops=1)

  • Hash Cond: (loc.hid = joined.hid)
15. 9.784 9.784 ↓ 1.1 10,237 1

CTE Scan on loc (cost=0.00..193.20 rows=9,660 width=44) (actual time=1.170..9.784 rows=10,237 loops=1)

16. 50.433 12,441.741 ↓ 51.2 10,237 1

Hash (cost=4.00..4.00 rows=200 width=40) (actual time=12,441.741..12,441.741 rows=10,237 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 653kB
17. 12,391.308 12,391.308 ↓ 51.2 10,237 1

CTE Scan on joined (cost=0.00..4.00 rows=200 width=40) (actual time=12,257.548..12,391.308 rows=10,237 loops=1)

Planning time : 0.465 ms
Execution time : 12,715.508 ms