[postgis-users] Partitioning spatial table

[Arnaud Lesauvage]

Markus Schaber a écrit :
> 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.

Yes, your are absolutely right ! Sorry for that. I am not at work 
right now, but I'll recheck this query on monday !

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

Well, I have read the doc (a large part of it at least), but I 
believe I won't be able to "think in PostgreS way" until I've read 
it 2 or 3 more times. ;-)

>>> 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.

My server is reloading the data during this week-end (it needs 
some hours to do that).
I think I will first try to cluster on the Gist index.
If I create a multicolumn index (with the geometry column first), 
would it be a better idea to cluster the table on this index instead ?



Many thanks again ! I'll have another look at the docs this 
week-end ;-)

Regards
--
Arnaud