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

[Sandro Santilli]

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