From: Niccolo Rigacci niccolo at rigacci.org Subject: [postgis-users] Using VIEW with MapServer is very slow Date: Thu Jul 7 03:17:54 PDT 2005 > 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!