[postgis-devel] New selectivity estimation for functional indexes

[Sandro Santilli]

For your information I've just committed a small refactoring
of the selectivity estimation code so that functional indexes
like the one created by the raster importer are correctly handled.

Before the change statistics gathered for the index's column were
not found, so the estimator always returned a default estimation,
which I've found to be often leaning toward preferring a very expensive
sequencial scan over a quick index scan.

The new code uses a PostgreSQL function to extract stats for either
a table or an index, if available, depending on the query.

REF: http://trac.osgeo.org/postgis/ticket/2839

I've tested it manually and it works pretty nicely.
Example: raster table with 1452 tiles, query matching 30 rows,
before the change estimate of 290 triggers seqscan for .66 seconds,
after the change estimate of 20 triggers indexscan for .05 seconds:

 BEFORE:
  Seq Scan on o_4_hyp_1250m 
     (           cost=0.00..35.51 rows=290 width=32)
     (actual time=293.060..664.169 rows=30 loops=1)
    Filter: ((rast)::geometry && '...'::geometry)
    Rows Removed by Filter: 1422
  Total runtime: 664.210 ms

 AFTER:
  Bitmap Heap Scan on o_4_hyp_1250m 
     (          cost=4.30..18.06 rows=20 width=32)
     (actual time=28.689..49.105 rows=30 loops=1)
    Recheck Cond: ((rast)::geometry && '...'::geometry)
    ->  Bitmap Index Scan on o_4_hyp_1250m_st_convexhull_idx 
          (         cost=0.00..4.30 rows=20 width=0)
          (actual time=0.064..0.064 rows=30 loops=1)
          Index Cond: ((rast)::geometry && '...'::geometry)
  Total runtime: 49.321 ms

As part of the work I've added some tests for estimation in
our testsuite (see r12813 and 12814) but if you can also run
some manual tests against your preferred tables that'd be nice.
The test would be running EXPLAIN ANALYZE and checking if the
estimates are still good. Tables do not need to be re-analyzed
for the code to take effect. If you want to test functional
indexes that'd also be good.

Also I locally tested against PostgreSQL 9.3.4 so any test with
other versions is welcome.

--strk;

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