Use a materialized CTE for example postcode too.

[dracos]

As per the comment, without doing this the query performs an identical transform on every row it's looking at, rather than doing it once at the start.

Before:

mapit=# explain analyze
SELECT * FROM "mapit_postcode"
WHERE ( ST_CoveredBy(location,
    ST_Transform((select ST_Collect(polygon) from mapit_geometry
        where area_id=2577 group by area_id), 4326)))
LIMIT 1;
                                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=45.32..583.67 rows=1 width=43) (actual time=9536.938..9536.941 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  GroupAggregate  (cost=0.42..19.91 rows=3 width=36) (actual time=0.980..0.981 rows=1 loops=1)
           Group Key: mapit_geometry.area_id
           ->  Index Scan using mapit_geometry_area_id on mapit_geometry  (cost=0.42..15.75 rows=3 width=13437) (actual time=0.019..0.021 rows=1 loops=1)
                 Index Cond: (area_id = 2577)
   ->  Index Scan using postcodes_postcode_location_id on mapit_postcode  (cost=25.41..105003.54 rows=195 width=43) (actual time=9536.936..9536.937 rows=1 loops=1) 
         Index Cond: (location @ st_transform($0, 4326))
         Filter: st_coveredby(location, st_transform($0, 4326))
         Rows Removed by Filter: 406
 Planning Time: 0.165 ms
 Execution Time: 9537.274 ms
(12 rows)

(Rewriting as a non-materialized CTE it is the same query plan:

mapit=# explain analyze WITH target AS (
    SELECT ST_Transform((select ST_Collect(polygon) from mapit_geometry
            where area_id=2577 group by area_id), 4326) AS polygon )
SELECT "mapit_postcode"."id", "mapit_postcode"."postcode", "mapit_postcode"."location"::bytea
  FROM mapit_postcode, target WHERE ST_CoveredBy(location, target.polygon)
LIMIT 1;
                                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=45.32..583.80 rows=1 width=43) (actual time=9248.305..9248.308 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  GroupAggregate  (cost=0.42..19.91 rows=3 width=36) (actual time=1.015..1.017 rows=1 loops=1)
           Group Key: mapit_geometry.area_id
           ->  Index Scan using mapit_geometry_area_id on mapit_geometry  (cost=0.42..15.75 rows=3 width=13437) (actual time=0.017..0.018 rows=1 loops=1)
                 Index Cond: (area_id = 2577)
   ->  Index Scan using postcodes_postcode_location_id on mapit_postcode  (cost=25.41..105027.91 rows=195 width=43) (actual time=9248.302..9248.303 rows=1 loops=1)
         Index Cond: (location @ st_transform($0, 4326))
         Filter: st_coveredby(location, st_transform($0, 4326))
         Rows Removed by Filter: 406
 Planning Time: 0.312 ms
 Execution Time: 9248.670 ms
(12 rows)

After:

mapit=# explain analyze WITH target AS MATERIALIZED (
    SELECT ST_Transform((select ST_Collect(polygon) from mapit_geometry
            where area_id=2577 group by area_id), 4326) AS polygon )
SELECT "mapit_postcode"."id", "mapit_postcode"."postcode", "mapit_postcode"."location"::bytea
  FROM mapit_postcode, target WHERE ST_CoveredBy(location, target.polygon) LIMIT 1;
                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=120.01..148.00 rows=1 width=43) (actual time=48.151..48.157 rows=1 loops=1)
   CTE target
     ->  Result  (cost=19.91..44.92 rows=1 width=32) (actual time=43.164..43.166 rows=1 loops=1)
           InitPlan 1 (returns $0)
             ->  GroupAggregate  (cost=0.42..19.91 rows=3 width=36) (actual time=1.297..1.299 rows=1 loops=1)
                   Group Key: mapit_geometry.area_id
                   ->  Index Scan using mapit_geometry_area_id on mapit_geometry  (cost=0.42..15.75 rows=3 width=13437) (actual time=0.029..0.033 rows=1 loops=1)
                         Index Cond: (area_id = 2577)
   ->  Nested Loop  (cost=75.09..54659.02 rows=1950 width=43) (actual time=48.150..48.151 rows=1 loops=1)
         ->  CTE Scan on target  (cost=0.00..0.02 rows=1 width=32) (actual time=43.239..43.239 rows=1 loops=1)
         ->  Bitmap Heap Scan on mapit_postcode  (cost=75.09..54413.30 rows=195 width=43) (actual time=4.892..4.892 rows=1 loops=1)
               Filter: st_coveredby(location, target.polygon)
               Heap Blocks: exact=1
               ->  Bitmap Index Scan on postcodes_postcode_location_id  (cost=0.00..75.04 rows=1950 width=0) (actual time=4.330..4.330 rows=29757 loops=1)
                     Index Cond: (location @ target.polygon)
 Planning Time: 0.290 ms
 Execution Time: 48.376 ms

[codecov[bot]]

Codecov Report

Merging #394 (e016ce6) into master (c9be442) will increase coverage by 0.06%. The diff coverage is 91.66%.

@@            Coverage Diff             @@
##           master     #394      +/-   ##
==========================================
+ Coverage   71.86%   71.92%   +0.06%     
==========================================
  Files          39       39              
  Lines        2303     2308       +5     
==========================================
+ Hits         1655     1660       +5     
  Misses        648      648              

Impacted Files	Coverage Δ
mapit/models.py	84.51% <90.90%> (+0.35%)	:arrow_up:
mapit/views/postcodes.py	78.37% <100.00%> (ø)

---

Continue to review full report at Codecov.

Legend - Click here to learn more Δ = absolute <relative> (impact), ø = not affected, ? = missing data Powered by Codecov. Last update c9be442...e016ce6. Read the comment docs.