[postgis-users] Strange Performance issue

[Markus Schaber]

Hi, Jerome,

Jerome Gasperi wrote:

> Here is the EXPLAIN ANALYSE of the query :
> 
> Aggregate  (cost=5433.45..5433.45 rows=1 width=0) (actual
> time=4373.253..4373.254 rows=1 loops=1)
>    ->  Nested Loop  (cost=0.00..5430.83 rows=1045 width=0) (actual
> time=42.830..4368.146 rows=2282 loops=1)
>          ->  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)
>                Index Cond: ((cntry_name)::text = 'France'::text)
>          ->  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)
>                Index Cond: (world_1000000.the_geom &&
> "outer".geometry_country)
>  Total runtime: 4373.360 ms

Okay, I see, it seems that it does a FULL JOIN on both tables, and then
drop the columns that don't match your country selection.

Could you try the following two:

SELECT count(*) FROM world_1000000
      WHERE the_geom &&
            (SELECT contry.geometry_country
             FROM country
             WHERE country.cntry_name='France');

SELECT count(*) FROM world_1000000 JOIN country
      ON the_geom && country.geometry_country
      WHERE country.cntry_name='France';

(The first one will only work if there's only a single match on country.)

HTH,
Markus