2

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)
|improve this question
  • 1
    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. – Vince Aug 25 '17 at 13:03
3

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)

|improve this answer
  • do I need to Vacuum Analyze after that bcos it is not being used either :( – gameOne Aug 25 '17 at 13:07
  • Yes............ – HeikkiVesanto Aug 25 '17 at 13:35
  • Somehow the performance got worse after adding the above mentioned index – gameOne Aug 25 '17 at 13:38
  • @gameOne show the new query plan.. – Evan Carroll Aug 25 '17 at 23:10
  • @EvanCarroll Edited with new query plan – gameOne Aug 26 '17 at 3:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.