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