1

I have created 2 databases.

  1. First one has PGSnapshot Schema which looks like this. pgnsapshot schema

On this I fire query

select id,ST_AsText(geom)
from nodes
where
ST_Intersects(geom , ST_GeomFromText('POLYGON ((-125.59390619 46.536192670000005, -125.59390619 49.00241116, -117.19116211 49.00241116, -117.19116211 46.536192670000005, -125.59390619 46.536192670000005))',4326));

Total query runtime: 15 min 28 secs. 26732975 rows affected. Analyze for this query is: enter image description here

  1. Second is a schema created with just nodes/ways/relations table with just geometry column and id column as shown in this picture.(properties column is empty) enter image description here

When I fire the same query, the result is different.

select id,ST_AsText(geom)
from nodes
where
ST_Intersects(geom , ST_GeomFromText('POLYGON ((-125.59390619 46.536192670000005, -125.59390619 49.00241116, -117.19116211 49.00241116, -117.19116211 46.536192670000005, -125.59390619 46.536192670000005))',4326));

Total query runtime: 5 min 6 secs. 25624080 rows affected. Analyze for this query is: enter image description here

Only difference between the first and second table is that first schema has index on geometry column of nodes table and the second schema does not have any index on geometry column in nodes data. But due to index, the read time should be more as compared to the indexed schema.

What could be the reason for this?

Improve this question

1 Answer 1

Reset to default
0

Are your 2 tables really identical ? The second one has a geom of type Geometry whereas the first has the type Point so maybe the content is not the same ?

Also, for performance, a lot of things can impact. For example because parallel index scan is only for btree indexes, GIST index are scanned in mono thread. Compared to a parallel sequential scan, it can be less efficient if your result is a significant portion of your total table. In this case, it's possible that index scan is only efficient when you're looking for a small portion of your table.

You can try to VACUUM ANALYZE your table just to be sure the planner has every info. You can also try to drop your index to test. If you really want to keep your index an have this request be performant, maybe you can look for a way to skip the index or deactivate it, I don't know if it's possible. I know some postgis function can skip index (with there name starting with '_') but I don't see ST_Intersect version for this.

Improve this answer
3
  • Thanks for the above explaination. On the first part, the geom(Geometry) is also storing Point data internally and so its similar to geom(Point) type column. On 2nd, are you implying that parallel sequential scans are more effective than monothread gist index scans? I am trying to query data for washington region from database having US data. On 3rd, I have data in AWS RDS, which automatically runs VACUUM ANALYZE so I am not sure rerunning will help, and also you are right ST_INTERSECTS uses spatial index by default.
    – Yasha Jadwani
    Sep 22, 2022 at 7:56
  • You should try to drop the index to see if there is an improvement. If there is, it's possible that the parallel scan is the cause. But like I said, it can be difficult to track performance issue. IF you installed the server manually, you probably need to tweak the parameters to have decent performance (maybe look at this old answer of mine as a start).
    – robin loche
    Sep 22, 2022 at 8:32
  • I tried dropping the index, and the query time comes out to be less due to postgres performing parallel seq scans. Why is index scan taking more time than parallel seq scans? and how to let query planner decide the correct faster query even with the presence of index.
    – Yasha Jadwani
    Sep 22, 2022 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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