[postgis-users] Slow query performance on large table

[Raphaël Jacquot]

On Thu, Mar 05, 2009 at 03:26:37PM -0800, Dylan Keon wrote:
> Hi folks,
> 
> Not sure if this is a better fit for the pgsql-general list -
> hopefully you can help me out.  We store the full GEBCO bathy/topo
> grids in PostgreSQL/PostGIS and are having slow query performance with
> the largest table.  We are running PostgreSQL 8.1.0 and PostGIS 1.1.5.
> 
> Table A (~233M rows):
> 
>     Table "public.gebco"
>  Column |   Type   | Modifiers
> --------+----------+-----------
>  id     | integer  |
>  x      | smallint |
>  y      | smallint |
>  depth  | smallint |
>  geom   | geometry |
> Indexes:
>     "gebco_geom_idx" gist (geom)
>     "gebco_x_idx" btree (x)
>     "gebco_y_idx" btree (y)
> Check constraints:
>     "enforce_dims_geom" CHECK (ndims(geom) = 2)
>     "enforce_geotype_geom" CHECK (geometrytype(geom) = 'POINT'::text
> OR geom IS NULL)
>     "enforce_srid_geom" CHECK (srid(geom) = 4326)
> 
> 
> Table B (~933M rows):
> 
>     Table "public.gebco30"
>  Column |   Type   | Modifiers
> --------+----------+-----------
>  id     | integer  |
>  x      | integer  |
>  y      | smallint |
>  depth  | smallint |
>  geom   | geometry |
> Indexes:
>     "gebco30_geom_idx" gist (geom)
>     "gebco30_x_idx" btree (x)
>     "gebco30_y_idx" btree (y)
> Check constraints:
>     "enforce_dims_geom" CHECK (ndims(geom) = 2)
>     "enforce_geotype_geom" CHECK (geometrytype(geom) = 'POINT'::text
> OR geom IS NULL)
>     "enforce_srid_geom" CHECK (srid(geom) = 4326)

one difference I can see is that x is a smallint in one case, and an integer in the other

apart from that, you should probably create an index on (x, y), it should make things much
faster.
see multicolumn index here 
http://www.postgresql.org/docs/8.3/interactive/indexes-multicolumn.html