SQL hooks for calling selectivity functions

[pramsey]

Perhaps these can just be in SQL, doing OID lookups into the system catalogs to drive the existing functions, but there needs to be a user-friendly way to run a test of the selectivity system, which can then be fed into a regression testing suite.

[strk]

good idea. of the same registry it'd be lovely to get to "see" the histogram (RASTER could help for that).

[pramsey]

Put in one for geometry 2d simple selectivity at r10720 on trunk,

geometry_gist_selectivity(schemaname varchar, tablename varchar, attrname varchar, geom geometry)

this is not a public function, it's for developers only.

[strk]

didn't we have a policy on internal functions ? Like _postgis_something ? Or postgis_something…

Also, would it be hard to accept a regclass instead of the first two args ? .. and of course the last one should be an Envelope type

[pramsey]

Right, we don't have any examples of an "_postgis" function yet, but we could certainly do that instead.

Explain "regclass" to me, I'm not recognizing the term.

[strk]

SELECT 'public.geometry_columns'::regclass;
SELECT 'geometry_columns'::regclass;
SELECT 'geometry_columns'::regclass::oid;
SELECT oid::regclass::text FROM pg_class where relname = 'geometry_columns';

[pramsey]

OK, the signature is now

_postgis_geometry_sel(tbl regclass, att_name text, geom geometry)

and no SQL wrapper is required! Thanks regclass! in trunk at r10725

[pramsey]

Oh, and for those wondering (like me) how to create a 'regclass' so you can call this function, regclass just takes in table names as strings, schema qualified or not, and happily spits out the right object, so

select _postgis_geometry_sel('mytable','mycol','LINESTRING(0 0, 30 30)');

will work thanks to the magic of auto-casting.

[robe]

shouldn't get too attached to autocasting. That is how all bad wars start.

best to be explicit.

select _postgis_geometry_sel('mytable'::regclass,'mycol'::text,'LINESTRING(0 0, 30 30)'::geometry);

We've already got issue with raster with lazy casting and then adding a default arg screws up everything.

[pramsey]

functions available now

_postgis_selectivity(table, column, geometry_literal, mode)
_postgis_join_selectivity(table1, column1, table1, column1, mode)
_postgis_stats(table, column, mode)

the 'mode' in all cases can be '2' or 'N' to get the results in a 2D or ND domain. We actually store different stats histograms for 2D and ND, which is why the _postgis_stats also has a mode flag.

[pramsey]

Oh, see #945 for the actual patch.

[strk]

Lovely ! Could you also add documentation for the new functions ? I can't wait to look at those histograms

[pramsey]

Wondering where to document them? I don't want them to be used by users, is it still OK to put them it the users manual ( we don't for example have documentation on _ST_Intersects, if I'm not mistaken).

Also, there is a static function (nd_stats_to_grid) to print out the first 2D histogram, but it's not tied to SQL right now. Your talk about outputting to raster got me interested, but I didn't actually do it (a raster expert like yourself on the other hand..?) And of course, ideally we'd be able to output the N-D histograms, but I'm not sure what the best way to expose that is, given the limitations of our brains/media.

[robe]

Good question. We could put them in Exceptional functions section where we put other stuff that in theory people should not need to use except for troubleshooting.

​http://www.postgis.org/documentation/manual-svn/reference.html#Exceptional_Functions

[strk]

we should surely write that people should _not_ use them as they are subject to change between even patch-level releases… Maybe a gory details section.

Hey, I'd also be happy with a description in comments above the signature in the .sql file

[pramsey]

Done at r10806

[pramsey]

Adding a test comment.