A questions on planner choices - Mailing list pgsql-general

I apologize for my english and... also for the explanation perhaps not
very clear.
I have some doubt regarding the planner choice for my query, usually it
does a very good job and I would prefer to leave free the planner but
with this query I have some doubt:

I use tree tables, cartellino with 2 indexes
     "cartellino_punto_geom_4326" gist (the_geom)
     "specimen_idspecie" btree (idspecie)
A view named specienomi with an index on specienomi.nome
Postgres 8.4.8 with postgis 1.5.3
I can post a complete explain for each query.

This is the original query
SELECT specienomi.nome, cartellino.cont_nome,
     ST_AsGML(cartellino.the_geom)
FROM cartellino, specienomi, confini_regioni
WHERE confini_regioni.regione='UMBRIA'
     AND specienomi.nome like 'Quercus%'
     AND cartellino.idspecie=specienomi.id
     AND ST_Intersects(cartellino.the_geom,confini_regioni.the_geom4326);
it tooks 4481.933 ms
the planner does ((cartellino join confini_regioni) join specienomi) but
I think I want to try another way.


A very big enhancement with:
WITH temp_que AS (
     SELECT specienomi.nome AS nome,
         cartellino.cont_nome AS cont_nome,
         cartellino.id AS id, the_geom
     FROM cartellino, specienomi
     WHERE specienomi.nome like 'Quercus %'
         AND cartellino.idspecie=specienomi.id
)
SELECT temp_que.nome, temp_que.cont_nome, ST_AsGML(temp_que.the_geom)
FROM temp_que, confini_regioni
WHERE confini_regioni.regione='UMBRIA'
AND ST_Intersects(temp_que.the_geom,confini_regioni.the_geom4326);
The time is 45.026 ms

the question is: I am missing some index? or ST_Intersects behaves in a
way that i don't understand?



after re-reading the manual I did some other try:

set from_collapse_limit=1;
SELECT temp_que.nome, temp_que.cont_nome, ST_AsGML(temp_que.the_geom)
FROM confini_regioni,
     (SELECT specienomi.nome AS nome,
         cartellino.cont_nome AS cont_nome,
         cartellino.id AS id, the_geom
      FROM cartellino, specienomi
      WHERE specienomi.nome like 'Quercus %'
         AND cartellino.idspecie=specienomi.id
     ) AS temp_que
WHERE confini_regioni.regione='UMBRIA'
AND ST_Intersects(temp_que.the_geom,confini_regioni.the_geom4326)
ORDER BY temp_que.id;

works fine 50.126 ms


set join_collapse_limit=1;
SELECT specienomi.nome, ST_AsGML(cartellino.the_geom)
FROM confini_regioni full JOIN (
     cartellino full JOIN specienomi ON
     (cartellino.idspecie=specienomi.id)) ON
     ST_Intersects(cartellino.the_geom,confini_regioni.the_geom4326)
WHERE confini_regioni.regione='UMBRIA'
AND specienomi.nome like 'Quercus%'

is slow: 5750.499 ms
and
NOTICE:  LWGEOM_gist_joinsel called with incorrect join type

thank you
Edoardo



[1] Plan for the firts query

-------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=20.45..20.46 rows=1 width=931) (actual
time=4457.775..4457.786 rows=76 loops=1)
    Sort Key: cartellino.id
    Sort Method:  quicksort  Memory: 74kB
    ->  Hash Join  (cost=8.32..20.44 rows=1 width=931) (actual
time=243.679..4457.658 rows=76 loops=1)
          Hash Cond: (cartellino.idspecie = principale.id)
          ->  Nested Loop  (cost=0.00..9.81 rows=614 width=886) (actual
time=4.094..4439.024 rows=18370 loops=1)
                Join Filter: _st_intersects(cartellino.the_geom,
confini_regioni.the_geom4326)
                ->  Seq Scan on confini_regioni  (cost=0.00..1.25 rows=1
width=1473036) (actual time=0.017..0.021 rows=1 loops=1)
                      Filter: ((regione)::text = 'UMBRIA'::text)
                ->  Index Scan using cartellino_punto_geom_4326 on
cartellino  (cost=0.00..8.30 rows=1 width=886) (actual
time=0.059..94.148 rows=32200 loops=1)
                      Index Cond: (cartellino.the_geom &&
confini_regioni.the_geom4326)
          ->  Hash  (cost=8.28..8.28 rows=3 width=57) (actual
time=0.392..0.392 rows=74 loops=1)
                ->  Index Scan using i_specie_nome_specie_like on specie
principale  (cost=0.01..8.28 rows=3 width=57) (actual time=0.034..0.348
rows=74 loops=1)
                      Index Cond: ((esterna_nome(ibrido, proparte,
(genere)::text, [...]  (cultivar)::text) ~>=~ 'Quercus'::text) AND
(esterna_nome(ibrido, proparte, (genere)::text, [...] (cultivar)::text)
~<~ 'Quercut'::text))
                      Filter: (esterna_nome(ibrido, proparte,
(genere)::text, [...] (cultivar)::text) ~~ 'Quercus%'::text)
  Total runtime: 4481.933 ms