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

[Gregory Williamson]

"IS NULL" is ok SQL, IS TRUE is not really ... try ST_Within(g.shape, location.hull) = 't' (may need to cast that 't'::boolean in 8.3 ... ). Just "AND ST_Within(g.shape, location.hull)" is fine because it returns a boolean and is correct for a logical AND. The "IS" confuses the planner it looks like.

= test for equality.
IS is a special operator that doesn't behave the same way.

HTH,

Greg Williamson
Senior DBA
DigitalGlobe

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)



-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net on behalf of Andi Vajda
Sent: Fri 9/12/2008 3:59 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] using 'IS TRUE' seems to disable geo index use
 

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
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080912/daed6195/attachment.html>