[postgis-users] Slow query performance on large table

[Dylan Keon]

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)


"Explain analyze" results on Table A (small table) for a simple query:

# explain analyze SELECT X(geom) AS geom_x, Y(geom) AS geom_y, x, y,
depth FROM gebco WHERE y=9876 AND x=2245;
                                                              QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on gebco  (cost=569.47..585.54 rows=4 width=31)
(actual time=134.482..134.488 rows=1 loops=1)
   Recheck Cond: ((x = 2245) AND (y = 9876))
   ->  BitmapAnd  (cost=569.47..569.47 rows=4 width=0) (actual
time=127.104..127.104 rows=0 loops=1)
         ->  Bitmap Index Scan on gebco_x_idx  (cost=0.00..62.38
rows=10680 width=0) (actual time=51.057..51.057 rows=10801 loops=1)
               Index Cond: (x = 2245)
         ->  Bitmap Index Scan on gebco_y_idx  (cost=0.00..506.84
rows=88812 width=0) (actual time=73.028..73.028 rows=21600 loops=1)
               Index Cond: (y = 9876)
 Total runtime: 134.695 ms
(8 rows)


Same query on Table B (large table):

# explain analyze SELECT X(geom) AS geom_x, Y(geom) AS geom_y, x, y,
depth FROM gebco30 WHERE y=4640 AND x=1568;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on gebco30  (cost=2020.59..2056.74 rows=9 width=33)
(actual time=8022.185..8022.196 rows=1 loops=1)
   Recheck Cond: ((x = 1568) AND (y = 4640))
   ->  BitmapAnd  (cost=2020.59..2020.59 rows=9 width=0) (actual
time=8002.672..8002.672 rows=0 loops=1)
         ->  Bitmap Index Scan on gebco30_x_idx  (cost=0.00..151.09
rows=26311 width=0) (actual time=7871.390..7871.390 rows=21600
loops=1)
               Index Cond: (x = 1568)
         ->  Bitmap Index Scan on gebco30_y_idx  (cost=0.00..1869.25
rows=328357 width=0) (actual time=122.604..122.604 rows=43200 loops=1)
               Index Cond: (y = 4640)
 Total runtime: 8022.569 ms
(8 rows)


Why the huge discrepancy?  Table B has ~4x as many rows as A.  But the
query takes ~60x longer.

The data were inserted in the following manner:

insert id1,x1,y1,d1,g1
insert id2,x2,y1,d2,g2
.
.
insert id43200,x43200,y1,d43200,g43200
insert id43201,x1,y2,d43201,g43201
insert id43202,x2,y2,d43202,g43202
.
.
and so on.

Any ideas why we might be getting such slow query performance on the
large table?  The server (RHEL5, dual Xeon 3.0GHz, 4GB memory, fast
SCSI disks) doesn't seem to be getting particularly hammered during
the queries.  Are our versions of PostgreSQL and PostGIS getting too
long in the tooth?

Thanks,
Dylan