[postgis-users] Partitioning spatial table

[Markus Schaber]

Hi, Arnaud,

Arnaud Lesauvage schrieb:

>> partitioning, especially on road data, which usually is write once and
>> then read-only.
> Actually, it was just for the benefit of the constraint exclusion that I
> wanted to try this.

Constraint Exclusion itsself has its overhead, that's why there's a
configuration option to enable it.

>> Could you send us the output of "EXPLAIN ANALYZE <your query>;"? If
>> estimations and reality are very different, increasing the statistics
>> target will help.
> 
> Sure !
> "Index Scan using nw_nw_geometry_gist on nw  (cost=0.00..6.02 rows=1
> width=141)"
> "  Index Cond: ('<the box>'::geometry && nw_geometry)"
> "  Filter: ((frc = 2) AND ('<the box>'::geometry && nw_geometry))"
> 
> Reality is ~5 minutes.

It looks like that is the output of "EXPLAIN <query>" instead of
"EXPLAIN ANALYZE <query>". The difference is that the latter one
actually performs the query with some profiling, and provides the real
times as well as the estimations.

> But I don't know what you mean by increasing the statistics (sorry for
> my lack of knowledge, I am new to postgresql, I am moving from mysql).

Please see the PostgreSQL docs on
http://www.postgresql.org/docs/8.1/static/planner-stats.html

>> If you don't have NULL geometries, add a NOT NULL constraint to your
>> geometry column and CLUSTER your table on the geometry index.
> I'll check the nullity of geometries, I am not sure of this.

The problem is that AFAIR PostGIS geometry indices (and all other GIST
type indices) have a problem with NULL values that prevent them from
being CLUSTERed on.

> How do you cluster a table on an index (again, sorry but these concepts
> are unkown in the mysql world...)

It's all explained here:
http://www.postgresql.org/docs/8.1/static/sql-cluster.html

Btw, generally, you should look into the PostgreSQL manuals, especially
http://www.postgresql.org/docs/8.1/static/maintenance.html - it is
always a good adivise to read the manuals when changing to a new
product, there are subtle differences between PostgreSQL and MySQL. (The
same is true for DB2, Oracle, MS Sequel server etc., of course.) It will
need some weeks or month until you can "think in PostgreS way". :-)

Also, have a look at
http://www.postgresql.org/docs/faqs.FAQ.html#item3.3 - there are some
useful links.

You can also ask the pgsql-performance at postgresql.org mailing list if
you have specific questions that are not answered in the docs.

>> You may also want to use partial indices in addition to the full one,
>> e. G.
> That sound very good to me !
> I usually filter data on both the road class AND the geometry location,
> so it definitively makes sense to filter on both !

Yes, and partial indices basically do the same than constraint
exclusion, but are more lightweight.

HTH,
Markus