[postgis-users] Partitionning using geometry

[Arnaud Lesauvage]

Hi Rémi,
have you taken a look at ST_GeoHash ?
It might be a good candidate for your constraint exclusion check.
There are restrictions though. Coordinates must be in lat/lon for instance.
http://postgis.net/docs/ST_GeoHash.html

Arnaud

Le 3/04/2015 10:49, Rémi Cura a écrit :
> Now postgres offers partitioning, that is that you build a hierarchy of
> tables.
>
> In this case, you would have one empty father table, and all the
> thousand table would be declared as child of the father table.
> Now when you write
> select * from father;
> you query in fact all the child tables.
>
> This is all good and working, but it will be inefficient, because each
> time you look for geometry within a rectangle, you would have to read
> all tables (using their index).
> Of course having thousands of indexes in memory is not possible, so it
> would be very slow.
>
> Postgres offers a solution for that, which is to declare constraints
> (check) on table.
>
> So you would say, table child_1 is entirely contained in a rectangle R1,
> table child_2 entirely contained in a rectangle R2, etc.
>
> That way, when you query the father table and asking all the geometry
> inside a rectangle R0,
> the planner will first check in which tables i Ri intersects R0,
> then it will only consider those tables, instead of considering all the
> tables.
>
> This is the theory. In practice the planner was not using those
> constraints (check).