Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group