3

I have a JSONB column with an array of locations. I want to do a Postgis GiST index on each of the array items.

For example:

[{
  "location":{
         "type": "Point",
         "coordinates": [
            20.1068,
            45.8061
         ]
      }
}, ...

What I've tried is indexing just the first item, because that's what I'm using currently:

CREATE INDEX "property_location" ON "public"."contracts" USING GIST (st_geomfromgeojson(((properties -> 0) ->> 'location'::text)));

When running a query, it doesn't look like it's being used.

explain analyze select * 
FROM
  contracts
WHERE
  ST_DWithin(
    ST_GeomFromGeoJSON(properties -> 0 -> 'location'),
    ST_GeomFromEWKT(
      'SRID=4326;POINT(19.8398 45.2477)'
    )::geography,
    50
  );

Output:

Gather  (cost=1000.00..7654358.90 rows=69 width=1014) (actual time=343.863..2555.811 rows=22 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on contracts  (cost=0.00..7653352.00 rows=29 width=1014) (actual time=323.943..2523.153 rows=7 loops=3)
"        Filter: st_dwithin((st_geomfromgeojson((((properties -> 0) -> 'location'::text))::text))::geography, '0101000020E6100000492EFF21FDD633400F9C33A2B49F4640'::geography, '50'::double precision, true)"
        Rows Removed by Filter: 230260
Planning Time: 0.176 ms
Execution Time: 2555.851 ms

How do I properly create the GiST index?

Ideally on all array items – even though in this example I'm only targeting the first item.

3
  • try adding an index on the jsonb column CREATE INDEX yourtable_jsonb_idx ON yourtable USING gin (jsonbcolumn); Also check this out scalegrid.io/blog/…
    – jbalk
    Oct 31 at 19:35
  • 4
    Usually PG is pretty picky about using the exact same expression in the query as in the index definition. Try using either -> 'location' or ->> 'location'::text (Eventually this this)
    – JGH
    Oct 31 at 19:53
  • 2
    @JGH is right, the index expression technically differs from the query expression! You may need to expect a higher threshold for index lookup invocation due to the high cost value assigned for that function. Apart from that, you cannot expand set items into an index expression; you will have to dump those geometries into a table.
    – geozelot
    Oct 31 at 21:25

0

Your Answer

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

Browse other questions tagged or ask your own question.