PostGIS: Spatial Index not being used

Question

Below is the query I'm using :

SELECT osm_id FROM planet_osm_line where ST_DWITHIN((ST_Transform(way,4326)), st_setsrid(ST_Point(9.17193, 48.76768),4326) ,50000)
and time_cost =0.1 limit 100000;

I did Explain, I see that the index on geom column way is not being used.

I've two indexes, on time_cost and way

Below is the trace on Explain:

 Limit (cost=0.43..245639.85 rows=2975 width=8) (actual time=0.510..1360.168 rows=100000 loops=1)
 -> Index Scan using time_cost_idx on planet_osm_line (cost=0.43..245639.85 rows=2975 width=8) (actual time=0.509..1322.503 rows=100000 loops=1)
 Index Cond: (time_cost = '0.1'::double precision)
 Filter: ((st_transform(way, 4326) && '0103000020E610000001000000050000001AA88C7FDA68E8C0475A2A6FE763E8C01AA88C7FDA68E8C0B9A5D5901870E840E6577380256BE840B9A5D5901870E840E6577380256B
E840475A2A6FE763E8C01AA88C7FDA68E8C0475A2A6FE763E8C0'::geometry) AND ('0101000020E61000000E677E35075822408AE5965643624840'::geometry && st_expand(st_transform(way, 4326), '50000'::double pr
ecision)) AND _st_dwithin(st_transform(way, 4326), '0101000020E61000000E677E35075822408AE5965643624840'::geometry, '50000'::double precision))
 Planning time: 0.284 ms
 Execution time: 1377.343 ms
(6 rows)

Hers is the updated trace after adding index as suggested:

 Limit (cost=33699.36..183915.21 rows=2975 width=8) (actual time=639.022..2008.438 rows=100000 loops=1)
 -> Bitmap Heap Scan on planet_osm_line (cost=33699.36..183915.21 rows=2975 width=8) (actual time=639.020..1971.429 rows=100000 loops=1)
 Recheck Cond: ((time_cost = '0.1'::double precision) AND (st_transform(way, 4326) && '0103000020E610000001000000050000001AA88C7FDA68E8C0475A2A6FE763E8C01AA88C7FDA68E8C0B9A5D59018
70E840E6577380256BE840B9A5D5901870E840E6577380256BE840475A2A6FE763E8C01AA88C7FDA68E8C0475A2A6FE763E8C0'::geometry))
 Rows Removed by Index Recheck: 107717
 Filter: (('0101000020E61000000E677E35075822408AE5965643624840'::geometry && st_expand(st_transform(way, 4326), '50000'::double precision)) AND _st_dwithin(st_transform(way, 4326),
'0101000020E61000000E677E35075822408AE5965643624840'::geometry, '50000'::double precision))
 Heap Blocks: exact=15297 lossy=32836
 -> BitmapAnd (cost=33699.36..33699.36 rows=44623 width=0) (actual time=629.404..629.404 rows=0 loops=1)
 -> Bitmap Index Scan on time_cost_idx (cost=0.00..4169.81 rows=223117 width=0) (actual time=40.065..40.065 rows=252522 loops=1)
 Index Cond: (time_cost = '0.1'::double precision)
 -> Bitmap Index Scan on geom_way_line_idx (cost=0.00..29527.82 rows=651121 width=0) (actual time=580.924..580.924 rows=3255604 loops=1)
 Index Cond: (st_transform(way, 4326) && '0103000020E610000001000000050000001AA88C7FDA68E8C0475A2A6FE763E8C01AA88C7FDA68E8C0B9A5D5901870E840E6577380256BE840B9A5D5901870E
840E6577380256BE840475A2A6FE763E8C01AA88C7FDA68E8C0475A2A6FE763E8C0'::geometry)
 Planning time: 0.230 ms
 Execution time: 2025.651 ms
(13 rows)

• Vince: You're not querying on the geometry column, so the geometry index shouldn't be used. Try building a covering index on the cast of the ST_Transfom output to geography, then using explicit casts to geography on the first two ST_DWithin parameters.

Answer 1

Since the way is not used directly, there is little use for an index on it. You can try to add a new index on ST_Transform(way,4326)

• gameOne: do I need to Vacuum Analyze after that bcos it is not being used either :(
• HeikkiVesanto: Yes............
• gameOne: Somehow the performance got worse after adding the above mentioned index
• Evan Carroll: @gameOne show the new query plan..
• gameOne: @EvanCarroll Edited with new query plan