[postgis-devel] [PostGIS] #1165: Selectivity extimation of && operator
PostGIS
trac at osgeo.org
Sat Aug 20 06:52:50 PDT 2011
#1165: Selectivity extimation of && operator
----------------------------+-----------------------------------------------
Reporter: edoardopanfili | Owner: pramsey
Type: defect | Status: new
Priority: medium | Milestone: PostGIS 1.5.4
Component: postgis | Version: 1.5.X
Keywords: |
----------------------------+-----------------------------------------------
In am using postgres 8.4.8 and postgis 1.5.3 on Mac OS X 10.7.1
Using this query [q1]
{{{
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);
}}}
this is the plan:
{{{
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
}}}
My target is to obtain
''(cartellino join specie) join confini_regioni''
and not
''(cartellino join confini_regioni) join specie''
and this is possible with [q2]
{{{
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
If I try with
{{{
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'''
I did a try with 2.0SVN but I think that there are problems with my
database conversion because all the queryes are slower (q1=85931.595 ms
and q2=589.595 ms)
This is the thread on postgresl mailing list:
http://archives.postgresql.org/pgsql-general/2011-08/msg00664.php
--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/1165>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.
More information about the postgis-devel
mailing list