[postgis-devel] optimizer prefers slower seq over faster index scan for raster &&

[Rémi Cura]

great catch,
such speed enhancement would be of much help working with postgis raster .
Cheers,
Rémi-C


2014-06-23 13:12 GMT+02:00 Sandro Santilli <strk at keybit.net>:

> I'm playing with GDAL driver for postgis raster and trying to understand
> how to speed it up. During the inspection, I found that the PostgreSQL-9.3
> panner makes bad choices when it comes to select a portion of the tileset.
>
> The table has 2048 rows.
> The query selects 128 of them.
> The query uses:
>
>   "rast" && ST_GeomFromText(..)
>
> Creating an index on ((rast::geometry)) makes the index a possible choice,
> but the planner decides to use a sequencial scan, which takes ~1 second:
>
>  Seq Scan on hyp_lr  (cost=0.00..206.75 rows=410 width=29) (actual
> time=927.443..1078.541 rows=128 loops=1)
>    Filter: ((rast)::geometry &&
> '0103000000010000000500000000000000008066C030939999991954C03AF9FFFFFF7F664030939999991954C03AF9FFFFFF7F664090F9FFFFFF3F54C000000000008066C090F9FFFFFF3F54C000000000008066C030939999991954C0'::geometry)
>    Rows Removed by Filter: 1920
>  Total runtime: 1078.588 ms
>
> Disabling sequencial scans generates a plan that executes in ~0.06 seconds
> instead:
>
>  Bitmap Heap Scan on hyp_lr  (cost=27.32..209.50 rows=410 width=29)
> (actual time=0.840..59.696 rows=128 loops=1)
>    Recheck Cond: ((rast)::geometry &&
> '0103000000010000000500000000000000008066C030939999991954C03AF9FFFFFF7F664030939999991954C03AF9FFFFFF7F664090F9FFFFFF3F54C000000000008066C090F9FFFFFF3F54C000000000008066C030939999991954C0'::geometry)
>    ->  Bitmap Index Scan on hyp_lr_rast_idx  (cost=0.00..27.22 rows=410
> width=0) (actual time=0.113..0.113 rows=128 loops=1)
>          Index Cond: ((rast)::geometry &&
> '0103000000010000000500000000000000008066C030939999991954C03AF9FFFFFF7F664030939999991954C03AF9FFFFFF7F664090F9FFFFFF3F54C000000000008066C090F9FFFFFF3F54C000000000008066C030939999991954C0'::geometry)
>  Total runtime: 59.764 ms
>
> The dataset is the "Land coloring basd on elevation", medium size:
>
> http://www.naturalearthdata.com/downloads/10m-raster-data/10m-cross-blend-hypso/
>
> The "funny" thing is that if I create a vector-only table with the
> output of rast::geometry, the planner makes a good choice (and better
> estimation):
>
>  Bitmap Heap Scan on test_planner  (cost=4.20..23.50 rows=7 width=120)
> (actual time=0.077..0.122 rows=128 loops=1)
>    Recheck Cond: (g &&
> '0103000000010000000500000000000000008066C030939999991954C03AF9FFFFFF7F664030939999991954C03AF9FFFFFF7F664090F9FFFFFF3F54C000000000008066C090F9FFFFFF3F54C000000000008066C030939999991954C0'::geometry)
>    ->  Bitmap Index Scan on test_planner_g_idx  (cost=0.00..4.20 rows=7
> width=0) (actual time=0.066..0.066 rows=128 loops=1)
>          Index Cond: (g &&
> '0103000000010000000500000000000000008066C030939999991954C03AF9FFFFFF7F664030939999991954C03AF9FFFFFF7F664090F9FFFFFF3F54C000000000008066C090F9FFFFFF3F54C000000000008066C030939999991954C0'::geometry)
>  Total runtime: 0.192 ms
>
> Is this a problem with the estimator, you think ?
> Can you imagine a way to make it smarter ?
>
> --strk;
>
>  ()  ASCII ribbon campaign  --  Keep it simple !
>  /\  http://strk.keybit.net/rants/ascii_mails.txt
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20140623/4cbc6a94/attachment.html>