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

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.