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

[Andi Vajda]

On Fri, 12 Sep 2008, Gregory Williamson wrote:

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

Yes, that solves it.
Thanks !

Andi..

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