Here is the tables:
-- 14 000 records
CREATE TABLE public.storagepricingapp_storagelocation
(
id integer NOT NULL DEFAULT nextval('storagepricingapp_storagelocation_id_seq'::regclass),
location geometry(Point,4326) NOT NULL,
CONSTRAINT storagepricingapp_storagelocation_pkey PRIMARY KEY (id)
)
CREATE INDEX storagepricingapp_storagelocation_location_id
ON public.storagepricingapp_storagelocation
USING gist
(location);
-- 30 millons records
CREATE TABLE public.storagepricingapp_storageunit
(
id integer NOT NULL DEFAULT nextval('storagepricingapp_storageunit_id_seq'::regclass),
sizeid character varying(30) NOT NULL,
dateupdated date NOT NULL,
facility_id integer NOT NULL,
datetimeadded timestamp with time zone NOT NULL,
CONSTRAINT storagepricingapp_storageunit_pkey PRIMARY KEY (id),
CONSTRAINT st_facility_id_f26e0175_fk_storagepricingapp_storagelocation_id FOREIGN KEY (facility_id)
REFERENCES public.storagepricingapp_storagelocation (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
CREATE INDEX storagepricingapp_storageunit_e32a5395
ON public.storagepricingapp_storageunit
USING btree
(facility_id);
The query:
set enable_seqscan=on;
explain analyze
SELECT "storagepricingapp_storageunit"."id"
FROM "storagepricingapp_storageunit"
INNER JOIN "storagepricingapp_storagelocation"
ON ("storagepricingapp_storageunit"."facility_id" = "storagepricingapp_storagelocation"."id")
WHERE ST_DistanceSphere("storagepricingapp_storagelocation"."old_location",
ST_GeomFromEWKB('\x0101000020e6100000b04cf0ff963f5ac0ad4886eb90dc4340'::bytea)) <= 8046.72
"Hash Join (cost=5066.45..1126897.09 rows=10162705 width=4) (actual time=29.178..11033.324 rows=118421 loops=1)"
" Hash Cond: (storagepricingapp_storageunit.facility_id = storagepricingapp_storagelocation.id)"
" -> Seq Scan on storagepricingapp_storageunit (cost=0.00..905873.16 rows=30488116 width=8) (actual time=0.042..4533.005 rows=30490125 loops=1)"
" -> Hash (cost=5008.03..5008.03 rows=4673 width=4) (actual time=25.836..25.836 rows=31 loops=1)"
" Buckets: 8192 Batches: 1 Memory Usage: 66kB"
" -> Seq Scan on storagepricingapp_storagelocation (cost=0.00..5008.03 rows=4673 width=4) (actual time=0.783..25.799 rows=31 loops=1)"
" Filter: (_st_distance(geography(old_location), '0101000020E6100000B04CF0FF963F5AC0AD4886EB90DC4340'::geography, '0'::double precision, false) <= '8046.72'::double precision)"
" Rows Removed by Filter: 13988"
"Planning time: 0.349 ms"
"Execution time: 11037.847 ms"
A query with disabled seqscan
set enable_seqscan=off;
explain analyze
SELECT "storagepricingapp_storageunit"."id"
FROM "storagepricingapp_storageunit"
INNER JOIN "storagepricingapp_storagelocation"
ON ("storagepricingapp_storageunit"."facility_id" = "storagepricingapp_storagelocation"."id")
WHERE ST_DistanceSphere("storagepricingapp_storagelocation"."old_location",
ST_GeomFromEWKB('\x0101000020e6100000b04cf0ff963f5ac0ad4886eb90dc4340'::bytea)) <= 8046.72
"Nested Loop (cost=58.85..16519593.31 rows=10162705 width=4) (actual time=4.353..97.927 rows=118421 loops=1)"
" -> Index Scan using storagepricingapp_storagelocation_pkey on storagepricingapp_storagelocation (cost=0.29..13625.36 rows=4673 width=4) (actual time=2.508..39.794 rows=31 loops=1)"
" Filter: (_st_distance(geography(old_location), '0101000020E6100000B04CF0FF963F5AC0AD4886EB90DC4340'::geography, '0'::double precision, false) <= '8046.72'::double precision)"
" Rows Removed by Filter: 13988"
" -> Bitmap Heap Scan on storagepricingapp_storageunit (cost=58.56..3497.70 rows=3450 width=8) (actual time=0.355..1.249 rows=3820 loops=31)"
" Recheck Cond: (facility_id = storagepricingapp_storagelocation.id)"
" Heap Blocks: exact=14184"
" -> Bitmap Index Scan on storagepricingapp_storageunit_e32a5395 (cost=0.00..57.70 rows=3450 width=0) (actual time=0.294..0.294 rows=3820 loops=31)"
" Index Cond: (facility_id = storagepricingapp_storagelocation.id)"
"Planning time: 0.477 ms"
"Execution time: 104.952 ms"
How to force planner to use indexes in the query?
ST_DWithin. See https://gis.stackexchange.com/questions/247113/how-to-properly-set-up-indexes-for-postgis-distance-queries – Vince Jul 11 '17 at 14:04