[postgis-users] Strange Performance issue

[Markus Schaber]

Hi, Jerome,

Jerome Gasperi wrote:

>          ->  Index Scan using cntry_name_idx on country 
> (cost=0.00..805.94 rows=209 width=32) (actual time=0.043..0.288 rows=1
                          ^^^                                        ^^^
Hmm, that looks as it has a misestimation about the hits in the country
table, and so chooses a suboptimal query plan, especially as you said
that the country table had about 200 rows.

> Index Scan using world_1000000_geom_idx on world_1000000
> (cost=0.00..22.07 rows=5 width=113) (actual time=36.637..4352.006
                        ^^^
> rows=2282 loops=1)
       ^^^^

And here's the next mis-estimation.

Did you ran ANALYZE or VACUUM ANALYZE on those tables lately?

Btw, maybe the PostgreSQL Performance List at
<pgsql-performance at postgresql.org> has some more experienced experts at
query plan analysis.

Markus