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

[Niccolo Rigacci]

> 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!