Optimizer is not choosing index

From: Markus Innerebner <markus(dot)innerebner(at)inf(dot)unibz(dot)it>
To: pgsql-performance(at)postgresql(dot)org
Subject: Optimizer is not choosing index
Date: 2012-02-15 22:40:13
Message-ID: DF644560-967D-4B1B-BEE0-49020DC1359F@inf.unibz.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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,whose target 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 accessing on 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::double precision)) AND _st_dwithin(geometry, '01010000208A7A00005451EB5AD9922541702C40E612151FC1'::geometry, 1860::double precision))"
" -> Bitmap Index Scan on it_nodes_geometry_gist (cost=0.00..63.94 rows=1959 width=0) (actual time=1.153..1.153 rows=1237 loops=1)"
" Index Cond: (geometry && '01030000208A7A000001000000050000005451EB5A51842541702C40E622321FC15451EB5A51842541702C40E602F81EC15451EB5A61A12541702C40E602F81EC15451EB5A61A12541702C40E622321FC15451EB5A51842541702C40E622321FC1'::geometry)"
"Total runtime: 3254.927 ms"

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2012-02-15 23:11:20 Re: Optimizer is not choosing index
Previous Message Marti Raudsepp 2012-02-15 19:12:04 Re: UPDATE on NOT JOIN