Link to home
Create AccountLog in
Avatar of babuno5
babuno5Flag for India

asked on

PostgreSQL Spatial Query Optimization

i have a table called road

It has

 select count(*) from road;
  count
---------
 1098822

I want to fire this query in the database

select name, astext(the_geom) from road where lower(city) like '%mumbai%' and lower(name) like '%carter%';


 explain analyze  select name, astext(the_geom) from road where lower(city) like '%mumbai%' and lower(name) like '%carter%';
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Seq Scan on road  (cost=0.00..45164.62 rows=1 width=478) (actual time=282.400..1299.810 rows=16 loops=1)
   Filter: ((lower((city)::text) ~~ '%mumbai%'::text) AND (lower((name)::text) ~~ '%carter%'::text))
 Total runtime: 1299.864 ms
(3 rows)

I would like suggestions from experts as to a way to reduce the time for execution or may be optimize the same for better performance
ASKER CERTIFIED SOLUTION
Avatar of grant300
grant300

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of babuno5

ASKER

Thanks