Обсуждение: Optimizer is not choosing index

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"

Markus Innerebner <markus.innerebner@inf.unibz.it> writes:
> 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. 

This suggests that you have a datatype mismatch:

> "  Hash Cond: ((e.target)::numeric = n.id)"

Your index is presumably on e.target, not e.target::numeric, so it's not
applicable.  Try to make the join columns the same datatype.

            regards, tom lane

Hi Tom,

thanks for your suggestion:


> Markus Innerebner <markus.innerebner@inf.unibz.it> writes:
>> 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. 
>
> This suggests that you have a datatype mismatch:
>
>> "  Hash Cond: ((e.target)::numeric = n.id)"
>
> Your index is presumably on e.target, not e.target::numeric, so it's not
> applicable.  Try to make the join columns the same datatype.

indeed: the id column in the node table had as type numeric, while in edges the target is integer.

After changing it, the index is used again.

many thanks


cheers Markus