FAQ
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,
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
" 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
Portugal
GSM: +351 96 17 20 188 Skype: pdoriam

## Search Discussions

• at Nov 29, 2007 at 9:58 pm ⇧ 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

## Related Discussions

 view thread | post
Discussion Overview
 group pgsql-general categories postgresql posted Nov 29, '07 at 1:26p active Nov 29, '07 at 9:58p posts 2 users 2 website postgresql.org irc #postgresql

### 2 users in discussion

Content

People

Support

Translate

site design / logo © 2022 Grokbase