Hi and sorry for my poor english
I have a problem with some spatial-query performance, my table have 8342 records with a spatial column (geometry)

The query
select objectid_1,comune,provincia,regione,tipo,st_asewkb (setSRID(shape,-1)) AS shape, mem_size(shape) from gdp_test where ((shape && public.ST_GeomFromWKB('POLYGON((6.086 45.351,14.636 45.699, 19.182 39.606, 9.745 38.976, 6.086 45.351))'::geometry,4326)) = 't')


the explain plan (on the geometry column is defined a spatial index)

"Seq Scan on gdp_test (cost=0.00..451.68 rows=6475 width=49042) (actual time=7.929..2152.717 rows=6129 loops=1)"
" Filter: (shape && '0103000020E61000000100000005000000F2D24D621058184 0B0726891EDAC46401283C0CAA1452D40B6F3FDD478D946403 BDF4F8D972E334021B0726891CD43403D0AD7A3707D2340B07 26891ED7C4340F2D24D6210581840B0726891EDAC4640'::ge ometry)"
"Total runtime: 2161.603 ms"

if i change the value of parameter enable_seqscan and enable_bitmapscan the explain plan is

"Index Scan using idx_gdp_test on gdp_test (cost=0.00..1486.13 rows=6475 width=49042) (actual time=4.667..1571.182 rows=6129 loops=1)"
" Index Cond: (shape && '0103000020E61000000100000005000000F2D24D621058184 0B0726891EDAC46401283C0CAA1452D40B6F3FDD478D946403 BDF4F8D972E334021B0726891CD43403D0AD7A3707D2340B07 26891ED7C4340F2D24D6210581840B0726891EDAC4640'::ge ometry)"
"Total runtime: 1580.071 ms"

Reading a post on the internet it seems that the problem is due to the fact that the spatial-data are in the TOAST-table and therefore access to these data is much slower, which also explains why, by making the query without spatial-column, the query is immediate:

Query (without spatial-column):
select objectid_1,comune,provincia,regione,tipo,/*st_asewkb(setSRID(shape,-1)) AS shape, */ mem_size(shape) from gdp_test where ((shape && public.ST_GeomFromWKB('POLYGON((6.086 45.351,14.636 45.699, 19.182 39.606, 9.745 38.976, 6.086 45.351))'::geometry,4326)) = 't')

"Index Scan using idx_gdp_test on gdp_test (cost=0.00..1453.75 rows=6475 width=49042) (actual time=1.614..561.353 rows=6129 loops=1)"
" Index Cond: (shape && '0103000020E61000000100000005000000F2D24D621058184 0B0726891EDAC46401283C0CAA1452D40B6F3FDD478D946403 BDF4F8D972E334021B0726891CD43403D0AD7A3707D2340B07 26891ED7C4340F2D24D6210581840B0726891EDAC4640'::ge ometry)"
"Total runtime: 570.073 ms"

doing some test with the data, it seems (I remember that I have not much experience with this type of analysis) that the problem is on the "size" of the data contained within the spatial-column, is there a way to improve the query for retrieve this data (since apparently the query does not have "problems")?

Giovanni

So, you're saying that removing "st_asewkb (setSRID(shape,-1))" from the SELECT cause fixed your performance problem? I find that very strange, as "shape" needs to be retrieved regardless, since it's also in the WHERE clause. Then again, I'm no expert on the internals of either PostgreSQL or PostGIS. You may want to ask this on one or both of their mailing lists if you get no better response here.

Yes, if i remove spatial columns from the select the query is "fast"... is so strange