I have created 2 databases.
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:
- 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)
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:
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?