Opened 8 years ago

Closed 8 years ago

Last modified 8 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 Changed 8 years ago by strk

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

comment:2 Changed 8 years ago by 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.

comment:3 Changed 8 years ago by 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 :D

comment:4 Changed 8 years ago by 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.

comment:5 Changed 8 years ago by 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';

comment:6 Changed 8 years ago by 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

comment:7 Changed 8 years ago by 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.

comment:8 Changed 8 years ago by 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.

comment:9 Changed 8 years ago by pramsey

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 Changed 8 years ago by pramsey

Oh, see #945 for the actual patch.

comment:11 Changed 8 years ago by strk

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

comment:12 Changed 8 years ago by 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.

comment:13 Changed 8 years ago by 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.

comment:14 Changed 8 years ago by 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 :)

comment:15 Changed 8 years ago by pramsey

Done at r10806

comment:16 Changed 8 years ago by pramsey

Adding a test comment.

Note: See TracTickets for help on using tickets.