Re: Understanding the output of EXPLAIN ANALYSE - Mailing list pgsql-novice

Here is the full output:


"Nested Loop  (cost=78.63..101.14 rows=1 width=256) (actual time=0.000..16.000 rows=2818 loops=1)"
"  ->  Nested Loop  (cost=78.63..95.12 rows=1 width=148) (actual time=0.000..0.000 rows=1 loops=1)"
"        ->  Merge Join  (cost=78.63..89.24 rows=1 width=100) (actual time=0.000..0.000 rows=1 loops=1)"
"              Merge Cond: (("outer".gridid = "inner".gridid) AND ("outer".subcenterid = "inner".subcenterid) AND
("outer".centerid= "inner".centerid))" 
"              ->  Sort  (cost=43.69..45.19 rows=600 width=80) (actual time=0.000..0.000 rows=1 loops=1)"
"                    Sort Key: datasetdir.gridid, datasetdir.subcenterid, datasetdir.centerid"
"                    ->  Seq Scan on mdgriddatasetdir datasetdir  (cost=0.00..16.00 rows=600 width=80) (actual
time=0.000..0.000rows=1 loops=1)" 
"              ->  Sort  (cost=34.94..36.09 rows=460 width=26) (actual time=0.000..0.000 rows=50 loops=1)"
"                    Sort Key: reg.gridid, reg.subcenterid, reg.centerid"
"                    ->  Seq Scan on mdgridregistrtions reg  (cost=0.00..14.60 rows=460 width=26) (actual
time=0.000..0.000rows=50 loops=1)" 
"        ->  Index Scan using mdgridaois_pkey on mdgridaois aois  (cost=0.00..5.87 rows=1 width=56) (actual
time=0.000..0.000rows=1 loops=1)" 
"              Index Cond: (aois.aoiid = "outer".aoiid)"
"              Filter: ((northlat > 18.0) AND (southlat < 30.0) AND (round((((distance(((eastlon - westlon))::double
precision)+ distance(((116.0 - eastlon))::double precision)) + distance(12::double precision)) + distance(((westlon -
128.0))::doublepreci (..)" 
"  ->  Index Scan using bobs_grid_index on g580000266058 grids  (cost=0.00..6.01 rows=1 width=116) (actual
time=0.000..0.000rows=2818 loops=1)" 
"        Index Cond: (("outer".datasetid = grids.datasetid) AND (grids.validtime >= 1197867600) AND (grids.validtime <=
1197954000))"
"Total runtime: 16.000 ms"


Now we added a couple of indexes, and it seemingly slowed down, but the estimated cost was lower:

"Nested Loop  (cost=0.00..15.28 rows=1 width=256) (actual time=0.000..110.000 rows=2818 loops=1)"
"  ->  Nested Loop  (cost=0.00..9.40 rows=1 width=208) (actual time=0.000..32.000 rows=2818 loops=1)"
"        ->  Nested Loop  (cost=0.00..3.38 rows=1 width=100) (actual time=0.000..0.000 rows=1 loops=1)"
"              Join Filter: (("outer".centerid = "inner".centerid) AND ("outer".subcenterid = "inner".subcenterid) AND
("outer".gridid= "inner".gridid))" 
"              ->  Seq Scan on mdgriddatasetdir datasetdir  (cost=0.00..1.01 rows=1 width=80) (actual time=0.000..0.000
rows=1loops=1)" 
"              ->  Seq Scan on mdgridregistrtions reg  (cost=0.00..1.50 rows=50 width=26) (actual time=0.000..0.000
rows=50loops=1)" 
"        ->  Index Scan using bobs_grid_index on g580000266058 grids  (cost=0.00..6.01 rows=1 width=116) (actual
time=0.000..0.000rows=2818 loops=1)" 
"              Index Cond: (("outer".datasetid = grids.datasetid) AND (grids.validtime >= 1197867600) AND
(grids.validtime<= 1197954000))" 
"  ->  Index Scan using mdgridaois_pkey on mdgridaois aois  (cost=0.00..5.87 rows=1 width=56) (actual time=0.022..0.028
rows=1loops=2818)" 
"        Index Cond: (aois.aoiid = "outer".aoiid)"
"        Filter: ((northlat > 18.0) AND (southlat < 30.0) AND (round((((distance(((eastlon - westlon))::double
precision)+ distance(((116.0 - eastlon))::double precision)) + distance(12::double precision)) + distance(((westlon -
128.0))::doubleprecision)) (..)" 

"Total runtime: 110.000 ms"