The following two queries differ only in the IS TRUE being added after the
call to ST_Within(). The first one, without IS TRUE, has an explanation that
mentions the geo index (fbgeom_shape_idx). The second one with IS TRUE, has
an explanation that does not.
I expected the IS TRUE to be only redundant. I had it in the query because it
was a more convenient implementation for SQL generator producing this query.
Is that a bug or is there something I'm not understanding correctly ?
version(): PostgreSQL 8.3.3 on x86_64-pc-linux-gnu, compiled by GCC
x86_64-pc-linux-gnu-gcc (GCC) 4.1.1 (Gentoo 4.1.1-r3)
postgis_full_version(): POSTGIS="1.3.3" GEOS="3.0.0-CAPI-1.4.1" USE_STATS
EXPLAIN ANALYZE SELECT t.typeid, count(g.topicid)
FROM fbgeom g, fbtype t,
(SELECT hull
FROM fbgeom
WHERE topicid='9202a8c04000641f800000000000d0f6')
AS location
WHERE t.topicid=g.topicid
AND t.typeid='9202a8c04000641f8000000000000669'
AND ST_Within(g.shape, location.hull)
GROUP BY 1
ORDER BY 2 DESC;
Sort (cost=34.75..34.75 rows=1 width=65) (actual time=3775.908..3775.910
rows=1 loops=1)
Sort Key: (count(g.topicid))
Sort Method: quicksort Memory: 25kB
-> GroupAggregate (cost=0.00..34.74 rows=1 width=65) (actual
time=3775.888..3775.890 rows=1 loops=1)
-> Nested Loop (cost=0.00..34.72 rows=1 width=65) (actual
time=10.702..3762.826 rows=6547 loops=1)
-> Nested Loop (cost=0.00..21.06 rows=1 width=32) (actual
time=2.940..3101.948 rows=47690 loops=1)
Join Filter: _st_within(g.shape, fbgeom.hull)
-> Index Scan using fbgeom_topicid_idx on fbgeom
(cost=0.00..8.53 rows=1 width=169) (actual time=0.074..0.116 rows=8 loops=1)
Index Cond: (topicid =
'9202a8c04000641f800000000000d0f6'::bpchar)
-> Index Scan using fbgeom_shape_idx on fbgeom g
(cost=0.00..12.50 rows=2 width=189) (actual time=0.202..27.259 rows=6257 loops=8)
Index Cond: (g.shape && fbgeom.hull)
Filter: (g.shape && fbgeom.hull)
-> Index Scan using fbtype_pkey on fbtype t (cost=0.00..13.61
rows=4 width=66) (actual time=0.009..0.009 rows=0 loops=47690)
Index Cond: ((t.topicid = g.topicid) AND (t.typeid =
'9202a8c04000641f8000000000000669'::bpchar))
Total runtime: 3775.993 ms
EXPLAIN ANALYZE SELECT t.typeid, count(g.topicid)
FROM fbgeom g, fbtype t,
(SELECT hull
FROM fbgeom
WHERE topicid='9202a8c04000641f800000000000d0f6')
AS location
WHERE t.topicid=g.topicid
AND t.typeid='9202a8c04000641f8000000000000669'
AND ST_Within(g.shape, location.hull) IS TRUE
GROUP BY 1
ORDER BY 2 DESC;
Sort (cost=22329.62..22329.62 rows=1 width=65) (actual
time=19325.717..19325.719 rows=1 loops=1)
Sort Key: (count(g.topicid))
Sort Method: quicksort Memory: 25kB
-> GroupAggregate (cost=0.00..22329.61 rows=1 width=65) (actual
time=19325.698..19325.700 rows=1 loops=1)
-> Nested Loop (cost=0.00..22329.59 rows=1 width=65) (actual
time=2038.875..19311.412 rows=6547 loops=1)
-> Nested Loop (cost=0.00..22315.93 rows=1 width=32) (actual
time=651.252..18691.565 rows=47690 loops=1)
Join Filter: (((g.shape && fbgeom.hull) AND
_st_within(g.shape, fbgeom.hull)) IS TRUE)
-> Index Scan using fbgeom_topicid_idx on fbgeom
(cost=0.00..8.53 rows=1 width=169) (actual time=0.070..0.144 rows=8 loops=1)
Index Cond: (topicid =
'9202a8c04000641f800000000000d0f6'::bpchar)
-> Seq Scan on fbgeom g (cost=0.00..15022.56 rows=485656
width=189) (actual time=0.010..870.361 rows=485656 loops=8)
-> Index Scan using fbtype_pkey on fbtype t (cost=0.00..13.61
rows=4 width=66) (actual time=0.009..0.009 rows=0 loops=47690)
Index Cond: ((t.topicid = g.topicid) AND (t.typeid =
'9202a8c04000641f8000000000000669'::bpchar))
Total runtime: 19325.803 ms
Thanks !
Andi..
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
