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

[Sandro Santilli]

For the record, I filed a ticket to raise the COST value for
ST_ConvexHull(raster): http://trac.osgeo.org/postgis/ticket/2895

--strk;

On Fri, Aug 15, 2014 at 12:19:29PM +0200, Sandro Santilli wrote:
> 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