On 2014-02-27 11:19, Rémi Cura wrote:
Maybe you can try to separate raster2pgsql and psql .
First use raster2pgsqland output it to a file on you filesystem.
Then use psql
to execute this file.
You could see how much time each step takes, and how much space the
sql temp file is.
The sql file went up to 6.6 GB.
Maybe you could use the "-Y" flag to make it faster?
(...)
INSERT 0 1
INSERT 0 1
INSERT 0 1
# Til here with the -Y flag was much faster, jumped from 1h 30 min to
only 13 min.
CREATE INDEX
ANALYZE
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding SRID
constraint
CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding
scale-X constraint
CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding
scale-Y constraint
CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding
blocksize-X constraint
CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding
blocksize-Y constraint
CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding
alignment constraint
CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding number
of bands constraint
CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding pixel
type constraint
CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding nodata
value constraint
CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Adding
out-of-database constraint
CONTEXT: PL/pgSQL function "addrasterconstraints" line 53 at RETURN
# Til here took about 30 minutes. And from here starts the slow part.
psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE: Unable to get
the extent of a sample raster. Attempting memory efficient (slower)
approach
CONTEXT: PL/pgSQL function "addrasterconstraints" line 100 at
assignment
PL/pgSQL function "addrasterconstraints" line 53 at RETURN
# 3h 30min have passed and still the same.
The message is clear and searching for this message I came across with
the code I think is executing [1].
There I can see that when this notice is raised it try again with
function 'st_memunion' (memory friendly but slower) after failing with
'st_collect'.
The machine where I am running this has 16 GB RAM and even so it was not
able to preform this task with 'st_collect'.
Any suggestions?
Thanks.
Cheers,
Eloi Ribeiro
[1]
https://git.osgeo.org/postgis/tags/2.0.3/raster/rt_pg/rtpostgis.sql.in.c
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
