PostGIS Spatial Index not used after copying table

Question

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;

and

pg_dump -U user -t mytab mydb > file.sql

and

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?

EDIT:

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

• Michael Stimson: That sounds totally strange. Are the software versions the same? (PostGIS and PostgreSQL). Is it the webserver that's ignoring the index and not the database? I use pg_dump for my backups and have never had a problem: two rebuilds of the server and only half hour of lost production time! After restore they were good to go - except when there was a different version of PostGIS applied by the I.T. OP doing the rebuild.
• Yojimbo: Even I tried to copy it in a new database on the same machine. So there is the same versions of Postgres and Postgis and still the indices aren't used

Answer 1

The only thing I can imagine is that, since the loads are perfectly fresh, stats might not have been gathered yet, so the planner is unaware of the indexes. Try running 'ANALYZE' and see if things get better after stats are gathered.

• Yojimbo: I tried ANALYZE and EXPLAIN ANALYZE. Still the same problem.
• Yojimbo: It seems like the problem was related to the bitwise where clause in the inner query (see edit above). Still that does not explain why it worked in the first place but did not after copying. Is there a smarter way to add a (bitwise) where clause to the nearest neighbour search?
• Paul Ramsey: There's no real reason I can see to expect changes unless you have pgsql or postgis version changes underneath. In that case, changes in the planner/stats code could cause behaviour differences like you expected.