Opened 11 years ago

Closed 11 years ago

Last modified 11 years ago

#2101 closed enhancement (fixed)

SQL hooks for calling selectivity functions

Reported by: pramsey Owned by: pramsey
Priority: medium Milestone: PostGIS 2.1.0
Component: postgis Version: master
Keywords: Cc:


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.

Change History (16)

comment:1 by strk, 11 years ago

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

comment:2 by pramsey, 11 years ago

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.

comment:3 by strk, 11 years ago

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 :D

comment:4 by pramsey, 11 years ago

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.

comment:5 by strk, 11 years ago

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';

comment:6 by pramsey, 11 years ago

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

comment:7 by pramsey, 11 years ago

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.

comment:8 by robe, 11 years ago

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.

comment:9 by pramsey, 11 years ago

Resolution: fixed
Status: newclosed

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.

comment:10 by pramsey, 11 years ago

Oh, see #945 for the actual patch.

comment:11 by strk, 11 years ago

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

comment:12 by pramsey, 11 years ago

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.

comment:13 by robe, 11 years ago

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.

comment:14 by strk, 11 years ago

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 :)

comment:15 by pramsey, 11 years ago

Done at r10806

comment:16 by pramsey, 11 years ago

Adding a test comment.

Note: See TracTickets for help on using tickets.