I'm using Postgis 2.1.
I have this table with about 50k rows:
CREATE TABLE rilevazioni ( geom geometry(Polygon), stimolo integer, tmstmp timestamp without time zone, id bigserial NOT NULL, CONSTRAINT rilevazioni_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); ALTER TABLE rilevazioni OWNER TO postgres; and this spatial index
CREATE INDEX spatial_index_geom ON rilevazioni USING gist(geom); If I run this query:
SELECT stimolo FROM rilevazioni WHERE ST_Intersects(geom, ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))')) the execution time is 500ms.
"Bitmap Heap Scan on rilevazioni (cost=429.40..4570.96 rows=3742 width=4) (actual time=18.717..521.334 rows=56126 loops=1)" " Recheck Cond: (geom && '0103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000'::geometry)" " Filter: _st_intersects(geom, '0103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000'::geometry)" " Heap Blocks: exact=1195" " -> Bitmap Index Scan on spatial_index_geom (cost=0.00..428.46 rows=11225 width=0) (actual time=18.430..18.430 rows=56126 loops=1)" " Index Cond: (geom && '0103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000'::geometry)" "Planning time: 0.223 ms" "Execution time: 528.747 ms" If a drop the spatial index, the execution time for the same query is anyhow 500ms.
"Seq Scan on rilevazioni (cost=0.00..15928.08 rows=3742 width=4) (actual time=0.073..523.207 rows=56126 loops=1)" " Filter: ((geom && '0103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000'::geometry) AND _st_intersects(geom, '0103000000 (...)" "Planning time: 0.273 ms" "Execution time: 531.010 ms" What's the problem?
أكثر...
I have this table with about 50k rows:
CREATE TABLE rilevazioni ( geom geometry(Polygon), stimolo integer, tmstmp timestamp without time zone, id bigserial NOT NULL, CONSTRAINT rilevazioni_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); ALTER TABLE rilevazioni OWNER TO postgres; and this spatial index
CREATE INDEX spatial_index_geom ON rilevazioni USING gist(geom); If I run this query:
SELECT stimolo FROM rilevazioni WHERE ST_Intersects(geom, ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))')) the execution time is 500ms.
"Bitmap Heap Scan on rilevazioni (cost=429.40..4570.96 rows=3742 width=4) (actual time=18.717..521.334 rows=56126 loops=1)" " Recheck Cond: (geom && '0103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000'::geometry)" " Filter: _st_intersects(geom, '0103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000'::geometry)" " Heap Blocks: exact=1195" " -> Bitmap Index Scan on spatial_index_geom (cost=0.00..428.46 rows=11225 width=0) (actual time=18.430..18.430 rows=56126 loops=1)" " Index Cond: (geom && '0103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000'::geometry)" "Planning time: 0.223 ms" "Execution time: 528.747 ms" If a drop the spatial index, the execution time for the same query is anyhow 500ms.
"Seq Scan on rilevazioni (cost=0.00..15928.08 rows=3742 width=4) (actual time=0.073..523.207 rows=56126 loops=1)" " Filter: ((geom && '0103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000'::geometry) AND _st_intersects(geom, '0103000000 (...)" "Planning time: 0.273 ms" "Execution time: 531.010 ms" What's the problem?
أكثر...