[postgis-users] Query crashed

[Mark Cave-Ayland]

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
> bounces at postgis.refractions.net] On Behalf Of Stephen Woodbridge
> Sent: 22 November 2005 02:30
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Query crashed
> 
> Mark,
> 
> Here are the EXPLAIN ANALYZE plans for the three query variation. I
> would love to have your insight on these and how you analyze them as it
> will probably help me in the future.
> 
> Thank you,
>    -Steve W.


Hi Steve,

Thank you for the info. It is fairly apparent that the expand() function has
changed the query plan, but I'll take a look at that later. The first thing
that stands out is your basic index query below:


> canada=# EXPLAIN ANALYZE select count(*) from roadseg r, streets s where
> r.the_geom && s.the_geom;
>                                                                   QUERY
> PLAN
> --------------------------------------------------------------------------
> -------------------------------------------------------------------
>   Aggregate  (cost=70651393.10..70651393.10 rows=1 width=0) (actual
> time=5037110.049..5037110.050 rows=1 loops=1)
>     ->  Nested Loop  (cost=0.00..70633249.98 rows=7257247 width=0)
> (actual time=196.615..5027698.511 rows=4795163 loops=1)
>           ->  Seq Scan on roadseg r  (cost=0.00..139617.69 rows=1843769
> width=143) (actual time=19.554..13494.148 rows=1843769 loops=1)
>           ->  Index Scan using streets_gidx on streets s
> (cost=0.00..38.12 rows=9 width=152) (actual time=1.321..2.705 rows=3
> loops=1843769)
>                 Index Cond: ("outer".the_geom && s.the_geom)
>   Total runtime: 5037110.212 ms
> (6 rows)
> 
> Time: 5037273.857 ms


This is doing a nested loop scan so for each of your 1.8m rows in roadseg
you are extracting 3 rows from streets to give just under 5m rows - but
extracting these 5m rows is taking over an hour an half which seems a very
long time indeed.

Can you confirm your PostgreSQL configuration by doing SHOW ALL and posting
back the output? My guess is that your shared_buffers setting is far too
low, although if you let us know the RAM in your database machine then we
can work out using a "rule of thumb" some new parameters for your
postgresql.conf file.


Kind regards,

Mark.

------------------------
WebBased Ltd
17 Research Way
Plymouth
PL6 8BT

T: +44 (0)1752 797131
F: +44 (0)1752 791023

http://www.webbased.co.uk   
http://www.infomapper.com
http://www.swtc.co.uk  

This email and any attachments are confidential to the intended recipient
and may also be privileged. If you are not the intended recipient please
delete it from your system and notify the sender. You should not copy it or
use it for any purpose nor disclose or distribute its contents to any other
person.