PostGIS vs. Shapefile

[Daniele Margotti]

A SQL query (using psql) like this:

SELECT * FROM ra_strade WHERE gid >= 0 ;

(i.e., all 33000 records) outputed to a file, takes about 3 seconds.


I will try debug options (decommenting and setting them to "true"), to
see if there's something strange.

        Daniele


-----Original Message-----
From: Miroslav Šulc [mailto:miroslav.sulc at startnet.cz] 
Sent: Friday, February 11, 2005 9:38 AM
To: Daniele Margotti
Subject: Re: [UMN_MAPSERVER-USERS] PostGIS vs. Shapefile


And how long does it take when you perform some SELECTs with WHERE 
clause directly at the PosgreSQL server (with psql or something 
similar)? I don't know how to create PostGIS WHERE clauses so I cannot 
give you a tip but maybe someone can help or you can find an example 
somewhere. It would be best to issue the same query as mapserver does.

I think that if the queries will take long time then the problem may be 
in PostgreSQL else the problem may be somewhere between mapserver and 
PostgreSQL or mapserver itself.

You can also try to use some of these debug options in your
postgresql.conf: #debug_print_parse = false #debug_print_rewritten =
false #debug_print_plan = false #debug_pretty_print = false
#log_connections = false #log_disconnections = false #log_duration =
false I have PostgreSQL 8.0.1 and I don't know whether these options are

present in your version. Some of these options look pretty interesting.

Miroslav Šulc



Daniele Margotti wrote:

>My connection string is:
>
>DATA "the_geom from my_table01"
>CONNECTION "user=postgres dbname=my_database host=localhost port=5432" 
>CONNECTIONTYPE postgis
>
>Where can I find logs?
>I installed PostgreSQL via RPMs, which did put the program in
>/var/lib/pgsql:
>
># ls -al /var/lib/pgsql/data/
>totale 52
>drwx------    6 postgres postgres     4096 10 feb 16:37 .
>drwx------    4 postgres postgres     4096 10 feb 17:02 ..
>drwx------    5 postgres postgres     4096 10 feb 12:38 base
>drwx------    2 postgres postgres     4096 11 feb 09:09 global
>drwx------    2 postgres postgres     4096  8 feb 15:57 pg_clog
>-rw-------    1 postgres postgres     2572 10 feb 10:13 pg_hba.conf
>-rw-------    1 postgres postgres     1441  8 feb 15:58 pg_ident.conf
>-rw-------    1 postgres postgres        4  8 feb 15:57 PG_VERSION
>drwx------    2 postgres postgres     4096 10 feb 13:09 pg_xlog
>-rw-------    1 postgres postgres     5238  9 feb 14:03 postgresql.conf
>-rw-------    1 postgres postgres       32 10 feb 15:17 postmaster.opts
>-rw-------    1 postgres postgres       45 10 feb 15:17 postmaster.pid
>
>In pg_clog and pg_xlog I have no text logs, only data files that are 
>not comprehensible.
>
>        Daniele
>  
>