[postgis-devel] pgsql2shp dump optimization and change

[Jeremy Palmer]

That's strange, are you running the queries directly after one another on
the same client connect?

When I run a test queries I get this:

Table with 5062 records
Group by 1101ms 
Distinct select 1352ms

Table with 268294 records
Group by 5718ms 
Distinct select 11857ms

I'm using PostgreSQL 7.42 with default configuration.

Thanks for the message about views I tried it a while back and got seg
faults, however it does not happen any more.

Jeremy

-----Original Message-----
From: postgis-devel-bounces at postgis.refractions.net
[mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of
strk at refractions.net
Sent: Tuesday, September 21, 2004 10:11 PM
To: postgis-devel at postgis.refractions.net
Subject: Re: [postgis-devel] pgsql2shp dump optimization and change

On Tue, Sep 21, 2004 at 08:17:27PM +1200, Jeremy Palmer wrote:
> Hi,
> 
> I was just try to dump a shape from postgis that had 30 million rows was
> wondering why the initialisation was taking so long. I think I found the
> problem: On the geometry type validation pgsql2shp a distinct clause is
> being used to aggregate geometry types in the table. In PostgreSQL a group
> by aggregation would be must faster. i.e. change
> 
> 	if ( schema )
> 	{
> 		sprintf(query, "SELECT DISTINCT geometrytype(\"%s\") "
> 			"FROM \"%s\".\"%s\" WHERE NOT geometrytype(\"%s\") "
> 			"IS NULL", geo_col_name, schema, table,
> geo_col_name);
> 	}
> 	else
> 	{
> 		sprintf(query, "SELECT DISTINCT geometrytype(\"%s\") "
> 			"FROM \"%s\" WHERE NOT geometrytype(\"%s\") IS
> NULL",
> 			geo_col_name, table, geo_col_name);
> 	}
> 
> To
> 
> 	if ( schema )
> 	{
> 		sprintf(query, "SELECT geometrytype(\"%s\") "
> 			"FROM \"%s\".\"%s\" WHERE NOT geometrytype(\"%s\") "
> 			"IS NULL GROUP BY SELECT geometrytype(\"%s\")", 
>                   geo_col_name, schema, table, geo_col_name, geo_col_name
);
> 	}
> 	else
> 	{
> 		sprintf(query, "SELECT geometrytype(\"%s\") "
> 			"FROM \"%s\" WHERE NOT geometrytype(\"%s\") IS NULL
> ",
> 			"GROUP BY geometrytype(\"%s\")", 
>                   geo_col_name, table, geo_col_name, geo_col_name);
> 	}

mmm.. I've made a test on a 386,048-records table.
distinct() ran in 2 secs
group by   ran in 18 secs

> 
> This is a side topic but related: Could it be possible to implement
> pgsql2shp to dump a shape file for each geometry class in a geometry
table.
> Either that or also allow pgsql2shp to take a view or query as source
input.

pgsql2shp can already be run on views.

> 
> Thanks.
> 

--strk;
_______________________________________________
postgis-devel mailing list
postgis-devel at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-devel