1

I have a table with thousands of records with a JSONB column that has a location (geometry) as a point. I am able to select points within a bounding box using this query.

 SELECT
    url,
    ST_AsText(ST_GeomFromGeoJSON(keywords ->> 'lat_lng'::text))
FROM
    photos
WHERE
    ST_GeomFromGeoJSON(keywords ->> 'lat_lng'::text) && ST_MakeEnvelope(-74.494259, 39.486874, -74.774259 , 39.786874, 4326);

Example of keywords column on my table:

{"lat_lng": {"type": "Point", "coordinates": ["-74.218001", "40.107786"]}, "name": "test_name"}

I know that I can add a spatial index to speed up my queries like so, but the problem is that i dont have a lat_lng column, I have a JSONB column with geometry points. (this is just an example on how to add spatial index)

CREATE INDEX places_lat_lng_idx ON places USING gist(lat_lng);

So I did added this as an index instead:

CREATE INDEX photos_lat_lng_idx ON photos USING GIST(ST_GeomFromGeoJSON(keywords ->> 'lat_lng'::text));

I was able to create that without error and run explain on my query which doesn't do a sequential scan anymore and noticably faster. On 10k rows I am getting this without the index on the JSONB column :

enter image description here

And with the index on the JSONB, I get this: enter image description here

My question is, Am I right to assume that applying the index on the JSONB column worked?

Table definition:

CREATE TABLE "public"."photos" (
    "id" int4 NOT NULL DEFAULT nextval('photos_id_seq'::regclass),
    "url" varchar,
    "created_at" int4,
    "deleted_at" int4,
    "keywords" jsonb,
    PRIMARY KEY ("id")
);

Postgres version: 11.7

Postgis version: 2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

5
  • 1
    That is called a functional index and should have worked as expected; it's over one order of magnitude faster! Try updating table stats; run 'VACUUM ANALYZE photos;` and check the EXPLAIN ANALYZE again. You may get off Heap or Bitmap Scans, but this depends on the table.
    – geozelot
    Mar 25, 2020 at 10:08
  • on a side note, coordinates should be expresses as longitude first, then latitude.
    – JGH
    Mar 25, 2020 at 12:40
  • @geozelot thanks for this confirmation! I did vacuum analyze but still giving my heap or bitmap scans, i think its fine for now i still get way faster queries!! If you make your comment an answer ill gladly accept it :)
    – ET2019
    Mar 25, 2020 at 16:12
  • @JGH Thanks for your comment! but which part are you referring to?
    – ET2019
    Mar 25, 2020 at 16:24
  • @ET2019 your variable is named lat_long, which is the opposite. If you data is in antartica, it is a naming issue. If you data is in the USA, it is a naming + coordinates swap
    – JGH
    Mar 25, 2020 at 17:49

1 Answer 1

1

You are correctly creating a functional (spatial) index right there, and it shows: one order of magnitude less execution time.

There may be subtle ways to coerce the planner to go for a more direct index lookup, but, assuming your data structure, none will have an improvement as significant to execution time as what you get from the current setup.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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