<html>
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 10 (filtered)">
<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{font-family:Arial;
color:windowtext;}
@page Section1
{size:595.3pt 841.9pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-AU link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Hi list,</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Before I start, please excuse what might be a trivial question.
It’s regarding the explain statement. I’ve never really understood
how to read it correctly. I know that it shows the query execution plan, but
how am I meant to understand the output? For example could someone give me some
pointers as to what the execution plan is on following queries?</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><b><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial;font-weight:bold'>EXPLAIN</span></font></b></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>explain SELECT feature.id,feature.name </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>FROM feature,region </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>WHERE region.id=28 </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>AND (feature.the_geom && region.the_geom AND distance(feature.the_geom,
region.the_geom)=0);</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>NOTICE: QUERY PLAN:</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Nested </span></font><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Loop</span></font><font size=2
face=Arial><span style='font-size:10.0pt;font-family:Arial'> (cost=0.00..9.04
rows=44 width=80)</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> -> Index Scan using region_pkey on
region (cost=0.00..3.01 rows=1 width=32)</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> -> Index Scan using feature_the_geom_idx on
feature (cost=0.00..6.01 rows=1 width=48)</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><b><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial;font-weight:bold'>EXPLAIN ANALYZE</span></font></b></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>explain analyze SELECT feature.id,feature.name </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>FROM feature,region </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>WHERE region.id=28 </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>AND (feature.the_geom && region.the_geom AND distance(feature.the_geom,
region.the_geom)=0);</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>NOTICE: QUERY PLAN:</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Nested </span></font><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Loop</span></font><font size=2
face=Arial><span style='font-size:10.0pt;font-family:Arial'> (cost=0.00..9.04
rows=44 width=80) (actual time=114.00..30210.00 rows=4992 loops=1)</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> -> Index Scan using region_pkey on
region (cost=0.00..3.01 rows=1 width=32) (actual time=0.00..0.00 rows=1
loops=1)</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> -> Index Scan using feature_the_geom_idx on
feature (cost=0.00..6.01 rows=1 width=48) (actual time=105.00..21179.00
rows=5133 loops=1)</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Total runtime: 30238.00 msec</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Is this saying that it uses a loop and each loop it gets the
region and compares it to a feature? Is the time killing in the comparison or
the features?</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>If I change the query slightly by removing some brackets and
adding an order by I get the following results:</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><b><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial;font-weight:bold'>EXPLAIN</span></font></b></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>explain SELECT feature.id,feature.name </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>FROM feature,region </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>WHERE feature.the_geom && region.the_geom AND distance(feature.the_geom,
region.the_geom)=0 </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>AND region.id=28 </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>ORDER BY feature.name;</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>NOTICE: QUERY PLAN:</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Sort (cost=10.25..10.25 rows=44 width=80)</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> -> Nested </span></font><font size=2
face=Arial><span style='font-size:10.0pt;font-family:Arial'>Loop</span></font><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>
(cost=0.00..9.04 rows=44 width=80)</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> -> Index
Scan using region_pkey on region (cost=0.00..3.01 rows=1 width=32)</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> -> Index
Scan using feature_the_geom_idx on feature (cost=0.00..6.01 rows=1
width=48)</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><b><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial;font-weight:bold'>EXPLAIN ANALYZE</span></font></b></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>explain analyze SELECT feature.id,feature.name </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>FROM feature,region</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>WHERE feature.the_geom && region.the_geom AND distance(feature.the_geom,
region.the_geom)=0 </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>AND region.id=28 </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>ORDER BY feature.name;</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>NOTICE: QUERY PLAN:</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Sort (cost=10.25..10.25 rows=44 width=80) (actual
time=30720.00..30721.00 rows=4992 loops=1)</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> -> Nested </span></font><font size=2
face=Arial><span style='font-size:10.0pt;font-family:Arial'>Loop</span></font><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>
(cost=0.00..9.04 rows=44 width=80) (actual time=113.00..30678.00 rows=4992
loops=1)</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> -> Index
Scan using region_pkey on region (cost=0.00..3.01 rows=1 width=32)
(actual time=0.00..0.00 rows=1 loops=1)</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> -> Index
Scan using feature_the_geom_idx on feature (cost=0.00..6.01 rows=1
width=48) (actual time=105.00..21584.00 rows=5133 loops=1)</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Total runtime: 30725.00 msec</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>What is the order of things here? Is it first of all sorting
all the features by there name then looping through each feature comparing it
to the region?</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Thanks,</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Benjamin</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
</div>
</body>
</html>