I am copying a PostGIS database from my computer to our webserver. I always had problems to copy the entire database using pg_dump and pg_restore so I am creating a new database on the server and copy the tables individually using
CREATE DATABASE mydb TEMPLATE = postgis_template;
pg_dump -U user -t mytab mydb > file.sql
psql -U user -d mydb -q -f "file.sql"
I am using this process for quiet some time now and never had any trouble. Now there is some trouble:
After copying the tables the spatial indices of the tables are not used any more. I tried to refresh and renew them. No success. Also I created a new database on my computer and copied the tables (so same OS same PostGIS version etc.). Same problem.
So when I look at the new database everything seems to be just like the old database but for some mysterious reason the spatial indices won't be used any more. So the same query on the same machine is not using the spatial indices any more...
The entire query is a bit long and confusing to post here but the spatial operator is "<->" to get polylines nearby. More or less as described here: http://boundlessgeo.com/2011/09/indexed-nearest-neighbour-search-in-postgis/ But as the same query is working totaly fine on the orignal database I assuming that the problem is the database (or the process of copying it) and not the query.
Is there any parameter I have to set in the database? Or is there a more convenient way to copy my database?
I found a workaround for my specific problem. My query is like the one from boundless (link above) just that I have added a WHERE clause using bitwise AND in the inner query. Like:
with index_query as ( select st_distance(geom, 'SRID=3005;POINT(1011102 450541)') as distance, parcel_id, address from parcels WHERE (parcel_type & 3) = 3 order by geom <#> 'SRID=3005;POINT(1011102 450541)' limit 100 ) select * from index_query order by distance limit 10;
When I place the where clause in the outer query like:
with index_query as ( select st_distance(geom, 'SRID=3005;POINT(1011102 450541)') as distance, parcel_id, address from parcels order by geom <#> 'SRID=3005;POINT(1011102 450541)' limit 100 ) select * from index_query WHERE (parcel_type & 3) = 3 order by distance limit 10;
the spatial index is beeing used again. Still that does not explain why the first query worked on the original database but not on it's copy. When I don't use a binary operation in the inner clause it also works fine...