sqlpostgresqlindexinggist-index

The created gist index is not considered during the operation for 50m rows in a table

I am trying to write a new column based on the intersection of two polygon features which have 50m rows in one table, I have created the "gist" index before running them but it takes forever and I cannot get the result in Postgresql. The gist index is not used in the computation. How can I use the index for the operation?

The model is attached in the fiddle below,

dbfiddle

CREATE INDEX idx_operation_geom ON public."Operation" USING gist (geom);

CREATE INDEX idx_operation_buffer ON public."Operation" USING gist (buffer);
CREATE INDEX idx_residential_geom ON public."Residential" USING gist (geom);

SELECT 
  ST_Intersection(ST_MakeValid(r.geom),o.buffer) AS intersection,   
  ST_Area(ST_Intersection(ST_MakeValid(r.geom),o.buffer))/ST_Area(r.geom)*100 
FROM public."Residential" r, public."Operation" o 
WHERE ST_Intersects(o.buffer,ST_MakeValid(r.geom));

I have tried to check the index using "Explain" but I am not sure how it works,

ERROR: operator does not exist: geometry = integer LINE 1: EXPLAIN SELECT * FROM public."Operation" where buffer = 2;

Any input will be very helpful.

Solution

  • The index is not used because you have indexed geom

    CREATE INDEX idx_residential_geom ON public."Residential" USING gist (geom);

    but in the function that could call the index, you are using ST_MakeValid(geom)

    Either you fix your geometries when you insert them, and you can drop the st_makeValid() statements in all other queries, or you would have to index the output of ST_MakeValid()

    CREATE INDEX idx_residential_valid_geom ON public."Residential" USING gist (ST_MakeValid(geom));