PostGIS – Efficient Proximity Check with ST_DWITHIN and Spatial Index

Question

Here is a snippet from my code:

ST_DWithin(c.convex_hull::geography , cp."geoJson"::geography, 20)

Please note both "convex_hull" and "geoJson" are geometry columns (which is why i am casting to geography). Further, please note that both columns have spatial indexing implemented as follows:

CREATE INDEX idx_bfp_clusters_convex_hull ON public.bfp_clusters USING gist (convex_hull)

and

CREATE INDEX "cp_geoJson_idx" ON public.campaign_pings USING gist ("geoJson")

Do I have to cast geometries to geography in both the CREATE INDEX commands above, or can I cast to geography in just one of the commands (the convex_hull one) to avail the speed benefit of spatial indexing?

Asking this since I would rather not tamper with the INDEX on the "geoJson" since it might affect existing code. Is there a way I can keep the existing index on the "geoJson" column, and add another geography index on the same column?

For more context, here is the aforementioned code snippet in place in a CTE:

clusters_with_coverage AS (

    -- Select the cluster_id, convex_hull geometry, and calculate coverage

    select
    
        c.uc_name,

        c.cluster_id,

        c.convex_hull,

        CASE

            -- Use the EXISTS keyword to check if any campaign pings are within 20 meters of the cluster's convex hull

            WHEN EXISTS (

                -- Subquery: Check if there is any campaign ping within 20 meters of the cluster

                SELECT 1

                FROM campaign_ping cp

                -- Use ST_DWithin function to determine if the campaign ping is within 20 meters of the cluster's convex hull

                WHERE ST_DWithin(c.convex_hull::geography , cp."geoJson"::geography, 20)

            ) THEN 'covered'  -- If campaign pings are within 20 meters, mark coverage as 'covered'

            ELSE 'missed'     -- If no campaign pings are within 20 meters, mark coverage as 'missed'

        END AS coverage

    FROM bfp_clusters c  -- Select cluster data from the 'bfp_clusters' table 

)

Answer 1

You can test whether an index is being used. The query below will tell you the indexes for the specified table, the number of times they've been scanned, and their size. You can create the geography indexes, run your query, then check the index usage with the following (replace schemaname with your schema and tablename with your table name). If they haven't been scanned, they aren't being used, and you can drop them:

SELECT a.*, pg_size_pretty(pg_relation_size(b.indexrelid)) index_size
FROM   pg_stat_user_indexes a 
JOIN   pg_index b ON a.indexrelid = b.indexrelid 
WHERE  schemaname in ('<schemaname>') AND relname in ('<tablename>');

You can add as many indexes as you like. Note that if you're doing many inserts or updates on the table, the indexes will slow that down, because the updated/inserted row needs to be written to all the indexes.