[postgis-devel] functions cost, or full table access faster via index !

[Sandro Santilli]

I just found a case in which a sequencial scan with a condition
selecting _all_ rows takes twice the time it takes for an index scan.

It is a raster table with 178,752 rows.

The query:

 select rast from <the_table>
 WHERE rast && <full_extent>

Explains as:
 
 -- With `set enable_seqscan to off`:

 Bitmap Heap Scan on timeseq_change00_12_backup  (cost=6646.86..71629.06 rows=176816 width=744) (actual time=46.363..10400.507 rows=178752 loops=1)
   Recheck Cond: ((rast)::geometry && '0103000000010000000500000000000000E03953C100000000402434C100000000E03953C10000000000F9254100000000004CFD400000000000F9254100000000004CFD4000000000402434C100000000E03953C100000000402434C1'::geometry)
   ->  Bitmap Index Scan on timeseq_change00_12_rast_gist  (cost=0.00..6602.65 rows=176816 width=0) (actual time=43.334..43.334 rows=178752 loops=1)
         Index Cond: ((rast)::geometry && '0103000000010000000500000000000000E03953C100000000402434C100000000E03953C10000000000F9254100000000004CFD400000000000F9254100000000004CFD4000000000402434C100000000E03953C100000000402434C1'::geometry)
 Total runtime: 10410.814 ms

 -- With `set enable_seqscan to on`:

 Seq Scan on timeseq_change00_12_backup  (cost=0.00..65490.40 rows=176816 width=744) (actual time=37.168..28962.642 rows=178752 loops=1)
   Filter: ((rast)::geometry && '0103000000010000000500000000000000E03953C100000000402434C100000000E03953C10000000000F9254100000000004CFD400000000000F9254100000000004CFD4000000000402434C100000000E03953C100000000402434C1'::geometry)
 Total runtime: 28974.226 ms

How can an index scan possibly be faster than a sequencial scan when it
comes to fetching all the rows ?
Is EXPLAIN ANALYZE not taking into account the actual fetching of the
output fields ?

I've been thinking that the difference is in the cost of
(rast)::geometry, which is pre-computed in the case of index access
while it has to be re-computed for the sequencial access case.

Indeed raising the cost of ST_ConvexHull(raster) to 1000 makes the
planner pick an index scan, but not with a cost of 100. The current
cost for ST_ConvexHull(raster) is 1. The cost for ST_ConvexHull(geometry)
is 100 !

I've seen some raster function are already marked as having a cost of
1000 (st_intersects, for example). Should all raster functions have
a cost of 1000 for the decompression cost ?

How should we deal with cost changes during upgrades ?

--strk; 

 ()  ASCII ribbon campaign  --  Keep it simple !
 /\  http://strk.keybit.net/rants/ascii_mails.txt