hi all
In my query I have two tables (edges 3,600,000 tuples and nodes 1,373,00 tuples), where I want to obtain all
edges,whosetarget vertex is within a given euclidean range starting from a query point q:
the query is formulated as following:
SELECT
E.ID, E.SOURCE,E.SOURCE_MODE,E.TARGET,E.TARGET_MODE,E.LENGTH,E.EDGE_MODE,E.ROUTE_ID,E.SOURCE_OUTDEGREE
FROM it_edges E, it_nodes N
WHERE
E.TARGET=N.ID AND
ST_DWITHIN(N.GEOMETRY,ST_PointFromText('POINT( 706924.6775765815 -509252.7248541778)',31370),1860.0)
the index are set on:
- nodes: unique index on ID defined as primary key and a spatial index on the geometry column
- edges: btree index on TARGET
the selectivity of the ST_DWITHIN is 0.07 of the total nodes table
and 0.08% of the total edes table
The query plan says, that a sequential scan is performed on the edge table. I consider it strange that he is not
accessingon the (btree) index one the edge table.
Any idea or suggestion?
Output Query plan:
"Hash Join (cost=7007.11..149884.46 rows=1 width=34) (actual time=6.219..3254.692 rows=3126 loops=1)"
" Hash Cond: ((e.target)::numeric = n.id)"
" -> Seq Scan on it_edges e (cost=0.00..124621.23 rows=3651223 width=34) (actual time=0.012..2403.982 rows=3651223
loops=1)"
" -> Hash (cost=7007.09..7007.09 rows=1 width=8) (actual time=5.613..5.613 rows=1028 loops=1)"
" -> Bitmap Heap Scan on it_nodes n (cost=63.94..7007.09 rows=1 width=8) (actual time=1.213..5.025 rows=1028
loops=1)"
" Recheck Cond: (geometry &&
'01030000208A7A000001000000050000005451EB5A51842541702C40E622321FC15451EB5A51842541702C40E602F81EC15451EB5A61A12541702C40E602F81EC15451EB5A61A12541702C40E622321FC15451EB5A51842541702C40E622321FC1'::geometry)"
" Filter: (('01010000208A7A00005451EB5AD9922541702C40E612151FC1'::geometry && st_expand(geometry,
1860::doubleprecision)) AND _st_dwithin(geometry, '01010000208A7A00005451EB5AD9922541702C40E612151FC1'::geometry,
1860::doubleprecision))"
" -> Bitmap Index Scan on it_nodes_geometry_gist (cost=0.00..63.94 rows=1959 width=0) (actual
time=1.153..1.153rows=1237 loops=1)"
" Index Cond: (geometry &&
'01030000208A7A000001000000050000005451EB5A51842541702C40E622321FC15451EB5A51842541702C40E602F81EC15451EB5A61A12541702C40E602F81EC15451EB5A61A12541702C40E622321FC15451EB5A51842541702C40E622321FC1'::geometry)"
"Total runtime: 3254.927 ms"