Help with cursor query that is intermittently slow

Hi,

I have a long query that returns an extremely large result set. In my application, I would like to report the results as they come in, so I am creating a cursor and fetching 1000 rows at a time. After I declare the cursor (declare C cursor for), I call "fetch 1000 from C" over and over. Usually, the result for the "fetch" query comes back very quickly (less than 100 milliseconds), but sometimes, however, it takes far longer for the result to come back (18 seconds, 27 seconds, 30 seconds, etc.).

I am trying to figure out why I get this intermittent slowness, and if there is anything I can do about it.

I'm running "PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 64-bit" on a Windows 7, 64-bit computer, 8 gb of ram.

My postgresql.conf file:
port = 53641
wal_level = minimal
archive_mode = off
max_wal_senders = 0
checkpoint_segments = 100
maintenance_work_mem = 807MB
work_mem = 81MB
shared_buffers = 2018MB
effective_cache_size = 6054MB
cursor_tuple_fraction = 1.0

Here is the query without a cursor. I ran this in the pgAdmin III application:
EXPLAIN (ANALYZE, BUFFERS) select POLYGON.ID,POLYGON.LAYER_ID,ST_AsBinary(POLYGON.GEOM),POLYGON.INDICES,POLYGON.PORTINSTANCE_ID
from POLYGON
where LAYER_ID = 1 and (ST_MakeEnvelope(-2732043.5012135925, -4077481.9752427186, 5956407.5012135925, 822435.9752427186, 0) && GEOM);

"Bitmap Heap Scan on polygon (cost=31524.04..700106.82 rows=1683816 width=235) (actual time=117.066..1237.018 rows=1691961 loops=1)"
" Recheck Cond: (layer_id = 1)"
" Filter: ('010300000001000000050000005AC427C005D844C1DFC0D4FCD41B4FC15AC427C005D844C17C0353F3471929412DE213E0CDB856417C0353F3471929412DE213E0CDB85641DFC0D4FCD41B4FC15AC427C005D844C1DFC0D4FCD41B4FC1'::geometry && geom)"
" Buffers: shared hit=84071"
" -> Bitmap Index Scan on polygon_layer_id_idx (cost=0.00..31103.09 rows=1683816 width=0) (actual time=103.354..103.354 rows=1691961 loops=1)"
" Index Cond: (layer_id = 1)"
" Buffers: shared hit=4629"
"Total runtime: 1273.132 ms"

Here is the polygon table and the related indexes:
CREATE TABLE public.polygon
(
id bigint NOT NULL DEFAULT nextval('polygon_id_seq'::regclass),
layer_id bigint NOT NULL,
geom geometry(Polygon) NOT NULL,
indices bytea NOT NULL,
portinstance_id bigint,
CONSTRAINT polygon_pkey PRIMARY KEY (id),
CONSTRAINT polygon_layer_id_fkey FOREIGN KEY (layer_id)
REFERENCES public.layerrow (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT polygon_portinstance_id_fkey FOREIGN KEY (portinstance_id)
REFERENCES public.portinstance (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.polygon
OWNER TO postgres;
CREATE INDEX polygon_layer_id_geom_idx
ON public.polygon
USING gist
(layer_id, geom);
CREATE INDEX polygon_layer_id_idx
ON public.polygon
USING btree
(layer_id);
CREATE INDEX polygon_portinstance_id_idx
ON public.polygon
USING btree
(portinstance_id);

The polygon table has about 20 million rows.

Here are the queries that my application is calling:
declare C cursor for select POLYGON.ID,POLYGON.LAYER_ID,ST_AsBinary(POLYGON.GEOM),POLYGON.INDICES,POLYGON.PORTINSTANCE_ID
from POLYGON where LAYER_ID = 1 and (ST_MakeEnvelope(-2732043.5012135925, -4077481.9752427186, 5956407.5012135925, 822435.9752427186, 0) && GEOM);
fetch 1000 from C;
fetch 1000 from C;
fetch 1000 from C;
...and so forth.

For example, in one trial, my application called "fetch 1000 from C" 1,659 times, with each result coming back in less than 100 ms. Then I get these response times for the fetches on the next few "fetch 1000 from C" calls:
1,142 ms
22,295 ms
6,551 ms
935 ms
809 ms
... and so forth.

By the way, my application is written in Java. I am using JDBC to communicate with the server. If there is any other information I could give you that would be helpful, please let me know.

Regards,
Drew Jetter
Senior Software Engineer
MicroNet Solutions, Inc
10501 Research RD SE, Suite C
Albuquerque, NM 87123
505-765-2490