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