[postgis-users] using 'IS TRUE' seems to disable geo index use

[Andi Vajda]

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..