[postgis-users] Partitioning spatial table

[Arnaud Lesauvage]

Hi Markus, thanks for your answer !

Markus Schaber a écrit :
>>From my personal experience, 5 million rows do not yet justify
> 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.

> Which indices did you put on the table? (send us the output of psql
> command "\d tablename").

The table has many columns, so I spare them (frc, i.e. the 
road-class, is a smallint).

Index :
     «nw_pkey» PRIMARY KEY, btree (gid)
     «nw_frc_btree» btree (frc)
     «nw_nw_geometry_gist» gist (nw_geometry)


> Have you recently VACUUMed and ANALYZed the table?

Yes, and the data never changes (has you said in your post, it is 
a write once - read many dataset).

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


> 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.
How do you cluster a table on an index (again, sorry but these 
concepts are unkown in the mysql world...)


> 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 !
I'll try this ASAP !

Thanks again Markus !

Regards
--
Arnaud