[postgis-users] Using VIEW with MapServer is very slow

[Paul Ramsey]

Mapserver and QGIS both use binary cursors for performance, and you  
can only get a binary cursor by DECLARING it. So they DECLARE then  
they FETCH ALL (I thought Mapserver fetched in smaller blocks though,  
to keep it from getting too memory-large when running large maps).

I am not sure what the correct compromise is to get optimal performance.

P.

On 7-Jul-05, at 3:17 AM, Niccolo Rigacci wrote:

>> But the real problem is that the query on the VIEW is very slow!
>> Using debug from Postgres I see that the query is:
>>
>> BEGIN;
>> DECLARE mycursor BINARY CURSOR FOR
>>     SELECT
>> ...
>> ...
>> FETCH ALL IN mycursor;
>>
>> The query is extremely slow from the command line too (2 min).
>> But if I do only the SELECT (without using the CURSOR), the
>> answer is near immediate.
>>
>
> I got a feasible explanation from Richard Huxton, from the
> Postgres performance list:
>
>
>> So - if you ask for a cursor, PG weights things to give you the
>> first few rows as soon as possible, at the expense of fetching
>> *all* rows quickly. If you're only going to fetch e.g. the
>> first 20 rows this is exactly what you want. In your case,
>> since you're immediately issuing FETCH ALL, you're not really
>> using the cursor at all, but PG doesn't know that.
>>
>
> I found that a workaround is to include an "ORDER BY" caluse in
> the VIEW definition, so the Postgres planner uses the same
> algorythm for the SELECT and the CURSOR.
>
> By the way, thare are some explanation on how MapServer and QGIS
> build their queries? Why they use a CURSOR just to FETCH ALL? Is
> there a way to influence this?
>
> Thanks again for your help.
>
> -- 
> Niccolo Rigacci
> Firenze - Italy
>
> War against Iraq? Not in my name!
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>