Hi,
we have come across a theoretical problem with a GIS database,
which I think worth discussing.
The database table is partitioned, it's already larger than 30TB.
The table is partitioned over the PostGIS && (overlaps) operator.
However, when SELECTing from that table, it goes through all
partitions. Example query is below:
select asbinary(force_collection(force_2d(the_geom)),'NDR')
from gll_h.parent as foo where the_geom && setsrid('BOX3D(3550500
5811500 0,3550600 5811600
0)'::BOX3D::geometry,31467)
After a little thinking, I would say it's natural that it considers all
partitions.
If A is the "super bounding box" in the CHECK contraint,
B is the value of "the_geom" and the query contains a geometry
that overlaps B but doesn't overlap A, it still needs to check
the subtable to get all correct records.
We are thinking about the following: partition the database
over the @ (contained operator) and add an extension
to the CREATE OPERATOR syntax so it can set up
a connection between two different operators, much like
the COMMUTATOR is now. Consider the following:
A is the "super bounding box" in the CHECK contraint,
B is the value of "the_geom" (B contained by A) and
the query checks whether a geometry C overlaps B.
1. "B contained by A" AND "C overlaps B" => "C overlaps A".
2. "B contained by A" AND "C doesn't overlap A" => "C doesn't overlap B"
Extending CREATE OPERATOR with this deduction feature
or the grammar with some other syntax, building a net of
deduction between functions this way, constraint exclusion
may work more efficiently.
Comments?
Thanks in advance,
Zoltán Böszörményi
--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/