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)