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

Question

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.

Your buffer is of type buffer geometry(polygon,4326),You can't do an integer comparison, this is why your explain is failing. Fix this then you will see correct Explain output and you can go from there. — Gurmokh
– Gurmokh, Commented Jun 22, 2021 at 9:32
@VynlJunkie, Thank you for the explanation, but I am not sure how to use the Explain for the buffer geometry column, what can be done there ? — Aravinth
– Aravinth, Commented Jun 22, 2021 at 9:35
Haven't used geometry types. But look here. postgresql.org/docs/current/datatype-geometric.html According to this you would need something like "buffer=((x1,y1),...)" In your same fiddle you even use it correctly. "buffer = ST_Buffer(geom::geography,100)::geometry;" — Gurmokh
– Gurmokh, Commented Jun 22, 2021 at 9:55

JGH · Accepted Answer · 2021-06-22 14:10:45Z

2

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));

answered Jun 22, 2021 at 14:10

JGH

18.2k5 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related