pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimizer is not choosing index
3+ messages / 2 participants
[nested] [flat]
* Optimizer is not choosing index
@ 2012-02-15 22:40 Markus Innerebner <markus.innerebner@inf.unibz.it>
0 siblings, 1 reply; 3+ messages in thread
From: Markus Innerebner @ 2012-02-15 22:40 UTC (permalink / raw)
To: 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"
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Optimizer is not choosing index
@ 2012-02-15 23:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Markus Innerebner <markus.innerebner@inf.unibz.it>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2012-02-15 23:11 UTC (permalink / raw)
To: Markus Innerebner <markus.innerebner@inf.unibz.it>; +Cc: pgsql-performance
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 accessing on 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Optimizer is not choosing index
@ 2012-02-16 06:54 Markus Innerebner <markus.innerebner@inf.unibz.it>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Markus Innerebner @ 2012-02-16 06:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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 accessing on 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-02-16 06:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-15 22:40 Optimizer is not choosing index Markus Innerebner <markus.innerebner@inf.unibz.it>
2012-02-15 23:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-02-16 06:54 ` Markus Innerebner <markus.innerebner@inf.unibz.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox