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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
