Thread: Help needed optimizing query
Hi People.
I need some help optimizing this query:
SELECT u.friendly_name, distance(transform(u.curr_location,32628), rc.agg_geometry) AS thedistance, u.mobile
FROM (SELECT transform(collect(r.geometry),32628) AS agg_geometry, t.county FROM pt_madeira_roads r, pt_madeira_toponymy t
WHERE r.name ILIKE '%salão são roque%' AND t.county='Funchal' AND r.geometry && t.geometry GROUP BY t.county) rc, units AS u
WHERE expand(rc.agg_geometry,1000) && transform(u.curr_location,32628) AND u.customer_id='14' AND (u.even_code='A' OR u.even_code='B')
ORDER BY thedistance LIMIT 10;
Total query runtime: 1578 ms.
16 rows retrieved.
this is the explain analyze output:
"Limit (cost=93.92..93.93 rows=1 width=78)"
" -> Sort (cost=93.92..93.93 rows=1 width=78)"
" Sort Key: distance(transform(u.curr_location, 32628), rc.agg_geometry)"
" -> Nested Loop (cost=85.59..93.91 rows=1 width=78)"
" Join Filter: (expand(rc.agg_geometry, 1000::double precision) && transform(u.curr_location, 32628))"
" -> HashAggregate (cost=85.59..85.60 rows=1 width=226)"
" -> Nested Loop (cost=0.00..85.58 rows=1 width=226)"
" -> Seq Scan on pt_madeira_toponymy t (cost=0.00..2.74 rows=10 width=15770)"
" Filter: (county = 'Funchal'::text)"
" -> Index Scan using pt_madeira_roads_idx on pt_madeira_roads r (cost=0.00..8.27 rows=1 width=213)"
" Index Cond: (r.geometry && t.geometry)"
" Filter: ((r.name ~~* '%salão são roque%'::text) AND (r.geometry && t.geometry))"
" -> Index Scan using "units_customerID_idx" on units u (cost=0.00..8.28 rows=1 width=46)"
" Index Cond: (customer_id = 14::bigint)"
" Filter: (((even_code)::text = 'A'::text) OR ((even_code)::text = 'B'::text))"
I still get Seq Scans although all used fields are indexed, hence the time used... :-(
Although this is almost 'targeted' to the beautiful SQL head of Regina's any input would be extremely appreciated... ;-)
Thank you!
I need some help optimizing this query:
SELECT u.friendly_name, distance(transform(u.curr_location,32628), rc.agg_geometry) AS thedistance, u.mobile
FROM (SELECT transform(collect(r.geometry),32628) AS agg_geometry, t.county FROM pt_madeira_roads r, pt_madeira_toponymy t
WHERE r.name ILIKE '%salão são roque%' AND t.county='Funchal' AND r.geometry && t.geometry GROUP BY t.county) rc, units AS u
WHERE expand(rc.agg_geometry,1000) && transform(u.curr_location,32628) AND u.customer_id='14' AND (u.even_code='A' OR u.even_code='B')
ORDER BY thedistance LIMIT 10;
Total query runtime: 1578 ms.
16 rows retrieved.
this is the explain analyze output:
"Limit (cost=93.92..93.93 rows=1 width=78)"
" -> Sort (cost=93.92..93.93 rows=1 width=78)"
" Sort Key: distance(transform(u.curr_location, 32628), rc.agg_geometry)"
" -> Nested Loop (cost=85.59..93.91 rows=1 width=78)"
" Join Filter: (expand(rc.agg_geometry, 1000::double precision) && transform(u.curr_location, 32628))"
" -> HashAggregate (cost=85.59..85.60 rows=1 width=226)"
" -> Nested Loop (cost=0.00..85.58 rows=1 width=226)"
" -> Seq Scan on pt_madeira_toponymy t (cost=0.00..2.74 rows=10 width=15770)"
" Filter: (county = 'Funchal'::text)"
" -> Index Scan using pt_madeira_roads_idx on pt_madeira_roads r (cost=0.00..8.27 rows=1 width=213)"
" Index Cond: (r.geometry && t.geometry)"
" Filter: ((r.name ~~* '%salão são roque%'::text) AND (r.geometry && t.geometry))"
" -> Index Scan using "units_customerID_idx" on units u (cost=0.00..8.28 rows=1 width=46)"
" Index Cond: (customer_id = 14::bigint)"
" Filter: (((even_code)::text = 'A'::text) OR ((even_code)::text = 'B'::text))"
I still get Seq Scans although all used fields are indexed, hence the time used... :-(
Although this is almost 'targeted' to the beautiful SQL head of Regina's any input would be extremely appreciated... ;-)
Thank you!
| -- Pedro Doria Meunier Ips da Olaria Edf. Jardins do Garajau, 4 r/c Y 9125-163 Caniço Madeira Portugal GSM: +351 96 17 20 188 Skype: pdoriam http://www.madeiragps.com |
Attachment
On Thu, Nov 29, 2007 at 01:26:00PM +0000, Pedro Doria Meunier wrote: > Hi People. > > I need some help optimizing this query: <snip> > I still get Seq Scans although all used fields are indexed, hence the > time used... :-( A seq scan on a table with 10 rows is *good*. An index would take longer. What you really need is to send the output of EXPLAIN ANALYZE so we can see where the time actually went. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy