1

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?

closed as off-topic by Vince, mgri, BERA, John Powell, Mapperz Jul 12 '17 at 14:29

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions relating to general IT or with no clear GIS component, are off-topic here but can be researched/asked at Stack Overflow (software development), Super User (computing hardware and software), Database Administrators (relational databases) and other SE sites" – Vince, mgri, BERA, John Powell, Mapperz
If this question can be reworded to fit the rules in the help center, please edit the question.

  • see related post Keep PostgreSQL from sometimes choosing a bad query plan – JGH Jul 11 '17 at 13:06
  • 1
    There's no reason for a distnce funtion to use an index. Instead, use ST_DWithin. See gis.stackexchange.com/questions/247113/… – Vince Jul 11 '17 at 14:04
  • @Vince it is not about index for location field and distance function. It is about index for facility_id field. I can speed up distance query from 75 ms to 0.75 ms but is not critical for me. I need the planner to do not use seqscan for storage units table which has 30 millions records. – kalombo Jul 12 '17 at 6:40
  • Then this is a pure database question, without any GIS content, better asked in Database Administrators. You probably want to use a more appropriate question title there. – Vince Jul 12 '17 at 9:04