0

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=# 

closed as off-topic by Evan Carroll, Erik Darling, mustaccio, hot2use, McNets Dec 4 '17 at 8:14

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

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, hot2use, McNets
If this question can be reworded to fit the rules in the help center, please edit the question.

  • what version of PostGIS and PostgreSQL? – Evan Carroll Dec 2 '17 at 1:00
  • Sorry, I forgot to put it there. According to my installed packages it seems to be PostGIS 2.2.1 and PostgreSQL 9.5.10 – yurtesen Dec 2 '17 at 1:05
  • Ok, first run VACUUM ANALYZE FULL store; then rerun your query with EXPLAIN ANALYZE – Evan Carroll Dec 2 '17 at 1:07
  • It solved this problem, but if you remember you suggested using geography_overlaps() in one of my questions and if I use it, it does not use the index. Why? – yurtesen Dec 2 '17 at 1:15
  • 1
    OK I assumed geography_overlaps would use index because you said && literally calls it. But I missed what you meant by the "catch" about && using index, meant geaometry_overlaps does 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 :) – yurtesen Dec 2 '17 at 1:27
1

Thanks to Evan Carrol I figured I simply needed to run # VACUUM FULL ANALYZE store; on my table (which was named store) and then database started using the index on queries.

Not the answer you're looking for? Browse other questions tagged or ask your own question.