Re: Understanding the output of EXPLAIN ANALYSE

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.000 rows=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.000 rows=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.000 rows=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))::double preci (..)"
" -> Index Scan using bobs_grid_index on g580000266058 grids (cost=0.00..6.01 rows=1 width=116) (actual time=0.000..0.000 rows=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=1 loops=1)"
" -> Seq Scan on mdgridregistrtions reg (cost=0.00..1.50 rows=50 width=26) (actual time=0.000..0.000 rows=50 loops=1)"
" -> Index Scan using bobs_grid_index on g580000266058 grids (cost=0.00..6.01 rows=1 width=116) (actual time=0.000..0.000 rows=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=1 loops=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))::double precision)) (..)"

"Total runtime: 110.000 ms"