I am using PostGIS 2.2.1 with PostgreSQL 9.5.10. I have a table with a geography field for coordinates and I am making a simple bounding box query. The table has 1029 rows. EXPLAIN says it does a sequential scan. What is going on?
masa=# \d+ store
Table "public.store"
Column | Type | Modifiers | Storage | Stats target | Description
-----------+------------------------+-----------------------------------------------------+----------+--------------+--------------------
id | integer | not null default nextval('store_id_seq1'::regclass) | plain | |
parent_id | integer | | plain | |
name | character varying(255) | not null | extended | |
coords | geography(Point,4326) | | main | | (DC2Type:geopoint)
Indexes:
"store_pkey" PRIMARY KEY, btree (id)
"uniq_ff5758775e237e06" UNIQUE, btree (name)
"idx_ff575877727aca70" btree (parent_id)
"idx_ff575877d86de985" gist (coords)
Foreign-key constraints:
"fk_ff575877727aca70" FOREIGN KEY (parent_id) REFERENCES store(id)
Referenced by:
TABLE "store_product" CONSTRAINT "fk_ca42254ab092a811" FOREIGN KEY (store_id) REFERENCES store(id) ON DELETE CASCADE
TABLE "store" CONSTRAINT "fk_ff575877727aca70" FOREIGN KEY (parent_id) REFERENCES store(id)
masa=# explain SELECT * FROM store WHERE coords && ST_MakeEnvelope(24.93522187393,60.155284015594,24.95327552607,60.164258984406, 4326);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on store (cost=0.00..27.86 rows=206 width=58)
Filter: (coords && '0103000020E610000001000000050000007F0863B36AEF3840AD48BC58E0134E407F0863B36AEF3840890C3B7006154E40408C68DD09F43840890C3B7006154E40408C68DD09F43840AD48BC58E0134E407F0863B36AEF3840AD48BC58E0134E40'::geography)
(2 rows)
masa=#
Apparently the reason was that table needed vacuum/analyze
masa=# VACUUM FULL ANALYZE store;
VACUUM
masa=# explain SELECT * FROM store WHERE coords && ST_MakeEnvelope(24.93522187393,60.155284015594,24.95327552607,60.164258984406, 4326);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_ff575877d86de985 on store (cost=0.14..8.16 rows=1 width=82)
Index Cond: (coords && '0103000020E610000001000000050000007F0863B36AEF3840AD48BC58E0134E407F0863B36AEF3840890C3B7006154E40408C68DD09F43840890C3B7006154E40408C68DD09F43840AD48BC58E0134E407F0863B36AEF3840AD48BC58E0134E40'::geography)
(2 rows)
masa=#
VACUUM ANALYZE FULL store;then rerun your query withEXPLAIN ANALYZEgeography_overlaps()in one of my questions and if I use it, it does not use the index. Why?geography_overlapswould use index because you said&&literally calls it. But I missed what you meant by the "catch" about&&using index, meantgeaometry_overlapsdoes not use index. I should stop doing this kind of stuff at 3AM :) If you put an answer, I can select is as correct. Although it is now self answered also :)