<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
Hi Markus,<br>
<br>
Here is the EXPLAIN ANALYSE of the query :<br>
<br>
Aggregate (cost=5433.45..5433.45 rows=1 width=0) (actual
time=4373.253..4373.254 rows=1 loops=1)<br>
-> Nested Loop (cost=0.00..5430.83 rows=1045 width=0) (actual
time=42.830..4368.146 rows=2282 loops=1)<br>
-> Index Scan using cntry_name_idx on country
(cost=0.00..805.94 rows=209 width=32) (actual time=0.049..0.324 rows=1
loops=1)<br>
Index Cond: ((cntry_name)::text = 'France'::text)<br>
-> Index Scan using world_1000000_geom_idx on
world_1000000 (cost=0.00..22.07 rows=5width=113) (actual
time=42.772..4360.901 rows=2282 loops=1)<br>
Index Cond: (world_1000000.the_geom &&
"outer".geometry_country)<br>
Total runtime: 4373.360 ms<br>
<br>
<br>
For comparaison, the EXPLAIN ANALYSE for request with precalculated
Bounding box :<br>
<br>
Aggregate (cost=6447.07..6447.07 rows=1 width=0) (actual
time=55.821..55.822 rows=1 loops=1)<br>
-> Index Scan using world_1000000_geom_idx on world_1000000
(cost=0.00..6442.99 rows=1632 width=0) (actual time=0.163..51.509
rows=2282 loops=1)<br>
Index Cond: (the_geom &&
'0103000020E61000000100000005000000040000E03E2913C0020000E0B5AE4440040000E03E2913C0070000A0A98B494000000080DB1F2340070000A0A98B494000000080DB1F2340020000E0B5AE4440040000E03E2913C0020000E0B5AE4440'::geometry)<br>
Total runtime: 56.328 ms<br>
<br>
<br>
Jerome<br>
<br>
<br>
Markus Schaber a écrit :
<blockquote cite="mid434E58A9.4080608@logix-tt.com" type="cite">
<meta http-equiv="Content-Type" content="text/html; ">
<meta name="Generator" content="MS Exchange Server version 6.5.7226.0">
<title>Re: [postgis-users] Strange Performance issue</title>
<!-- Converted from text/plain format -->
<p><font size="2">Hi, Jerome,</font>
</p>
<p><font size="2">Jerome Gasperi wrote:</font>
<br>
<font size="2">> For example, taking the 1000000 footprint table
within France :</font>
<br>
<font size="2">> </font>
<br>
<font size="2">> SELECT count(*) FROM world_1000000, country</font>
<br>
<font size="2">> WHERE country.cntry_name='France'</font>
<br>
<font size="2">> AND the_geom &&
country.geometry_country;</font>
<br>
<font size="2">> The \timing command gives 2882 results in
~4378 ms</font>
</p>
<p><font size="2">Can you send us the EXPLAIN ANALYZE output of this
query?</font>
</p>
<p><font size="2">Thanks,</font>
<br>
<font size="2">Markus</font>
<br>
<font size="2">_______________________________________________</font>
<br>
<font size="2">postgis-users mailing list</font>
<br>
<font size="2"><a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a></font>
<br>
<font size="2"><a
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a></font>
</p>
</blockquote>
<br>
<pre class="moz-signature" cols="72">--
Jerome GASPERI
DCT/PS/TIS - BPi 1218
CENTRE NATIONAL D'ETUDES SPATIALES
18, Av Ed BELIN
31401 TOULOUSE Cedex 4
tel : 05 61 28 25 23
fax : 05 61 27 31 67
</pre>
</body>
</html>