You were absolutely right! :-)
I mean about language/terminology, and about the need of an oid index.
Now query/nquery with PostGIS is as fast as if I use shapefile.
Thank you very much!
Daniele
P.S.: I'll add an issue to bugzilla, as you told.
-----Original Message-----
From: UMN MapServer Users List [mailto:MAPSERVER-USERS at LISTS.UMN.EDU] On
Behalf Of Paul Ramsey
Sent: Friday, February 11, 2005 5:02 PM
To: MAPSERVER-USERS at LISTS.UMN.EDU
Subject: Re: [UMN_MAPSERVER-USERS] PostGIS vs. Shapefile
I believe I see the problem here, which is one of language and
terminology. I was baffled by this thread for a number of days, as I
suppose all the participants were, since PostGIS should not be much
different than Shape files for performance.
Daniele, to confirm my understanding:
- When you draw a map in mapserver map mode or browse mode with
mapserver/postgis it is just as fast as mapserver/shape.
- When you draw a map or run a query in mapserver query or nquery mode,
it is quite a bit slower than mapserver/shape.
The source of confusion here seems to be the use of the work "query". Of
course, when drawing maps the postgis connection generates queries
against the database, no matter what the mode. But you are referring to
a problem specifically with the speed of your mapserver in query/nquery
mode?
CREATE INDEX table_oid_idx ON table (oid);
You only need two indexes for your table:
- a spatial index on your spatial column
- an oid index
You optionally should have an index on any attribute you are querying
against in itemquery mode.
The first index makes map drawing faster.
The second index allows queries to function quickly given mapserver's
funny two-phase query system.
Please add an issue to bugzilla that indicates that the
mapserver/postgis query mechanism should be upgraded to automatically
use the primary key on a table if such a key exists.
Thanks.
Paul
On 11-Feb-05, at 1:30 AM, Daniele Margotti wrote:
> In /var/log I only have a 0-byte pgsql file (but in my postgresql.conf
> I didn't set any debug option...).
>
> Inspecting top on the server (which normally is 100% idle), I can see
> that during elaboration of the map (/cgi-bin/mapserv?mode=browse)
> involved processes are "mapserv" and "postmaster", that use 100% CPU
> (sharing it in various %) for about 2 seconds.
>
> During query, the process "postmaster" monopolizes CPU with 99.9%
> load, for many seconds, during which sometimes appear "mapserv" and
> "httpd"
> processes: such situation starts when I click with the mouse and ends
> when the page is completely transmitted to the browser (I can see the
> page slowly composing, as if I had a 14.4 Kbps connection, instead of
> being LAN-connected!).
>
> With shapefile query, the page is instantaneously shown.
>
> Daniele
>
>
> -----Original Message-----
> From: Umberto Nicoletti [mailto:unicoletti at prometeo.it]
> Sent: Friday, February 11, 2005 9:29 AM
> To: Daniele Margotti
> Cc: Mapserver ML
> Subject: Re: [UMN_MAPSERVER-USERS] PostGIS vs. Shapefile
>
>
> They could be in /var/log then.
> Try running top on the server to see if there is load during the
> request and what process is causing it.
>
> Please make sure you understand how to index data in postgis. They
> have rather good documentation on how to speed up mapserver queries.
>
> Regards,
> Umberto
>
> On Fri, 2005-02-11 at 09:16 +0100, 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
>>
>>
>>
>> -----Original Message-----
>> From: UMN MapServer Users List [mailto:MAPSERVER-USERS at LISTS.UMN.EDU]
>> On Behalf Of Umberto Nicoletti
>> Sent: Friday, February 11, 2005 8:29 AM
>> To: MAPSERVER-USERS at LISTS.UMN.EDU
>> Subject: Re: [UMN_MAPSERVER-USERS] PostGIS vs. Shapefile
>>
>>
>> Ciao Daniele,
>> Could be a name resolution problem. Are you using an ip address or a
>> host name to specify the postgres connection string? If you are using
>> a name please make sure that it can be resolved correctly by
>> registering it in dns and/or in the /etc/hosts file.
>>
>> Did you try localhost in the connection string?
>> Also have a look at the log files in /var/lib/postgres/data.
>>
>> HTH,
>> Umberto
>>
>> On Thu, 2005-02-10 at 17:01 +0100, Daniele Margotti wrote:
>>> Yes, Mapserver is in the same computer of PostGIS: a Linux Red Hat
>>> 9. Computer is rather fast: I can dump a 33000-row table (14 Mb
>>> dumped to
>>> file) in about 3 seconds.
>>>
>>> And also when I connect from a Windows XP (via Lan) using ODBC
>>> drivers, data are read very fast.
>>>
>>> Only Mapserver is slow...
>>>
>>> Daniele
>>>
>>>
>>>> -----Original Message-----
>>>> From: UMN MapServer Users List
>>>> [mailto:MAPSERVER-USERS at LISTS.UMN.EDU]
>>> On Behalf Of Lyndon Tiu
>>>> Sent: Thursday, February 10, 2005 4:36 PM
>>>> To: MAPSERVER-USERS at LISTS.UMN.EDU
>>>> Subject: Re: [UMN_MAPSERVER-USERS] PostGIS vs. Shapefile
>>>>
>>>>
>>>> On February 10, 2005 02:03 am, Daniele Margotti wrote:
>>>>> Hi all,
>>>>> I have made some spatial queries (with cgi-bin Mapserver 4.2.3)
>>>>> alternately with data stored in shapefiles and PostGIS (imported
>>>>> from
>>>
>>>>> these shapefiles).
>>>>>
>>>>> My query (304 elements on 5 layers) takes 5 seconds (elaboration,
>
>>>>> and
>>>
>>>>> then output on browser) if data are read from shapefiles, and
>>>>> about
>>> 50
>>>>> seconds if data are read from PostGIS.
>>>>>
>>>>> The query is the same: I simply press "reload" on my browser
>>>>> (after
>>
>>>>> manually changed .map file and query template files) to be sure of
>>>>> it...
>>>>>
>>>>> Is it normal?
>>>>>
>>>>
>>>> Is Postgis in the same computer as mapserver?
>>>>
>>>> --
>>>> Lyndon Tiu
>>>>
>> --
>> Umberto Nicoletti <unicoletti at prometeo.it>
>> Prometeo SRL
> --
> Umberto Nicoletti <unicoletti at prometeo.it>
> Prometeo SRL