[postgis-users] Looking for more performance on select

[Eric FRANCOIS]

Hello,

I'm looking for help to improve postgres performance query.

I've 2 tables:
streets_db_egdes with 6 millions record, field 'id' is the primary of the
table
streets_db_big with 800000 records with an index on a field named edge_id
and on a geometry field name the_geom.

vaccum analyse is run on the database twice a day.

The following  query is runned in 15 seconds:
SELECT gid as id FROM streets_db_big as m
where  setsrid('BOX3D(1.65924 43.38176,3.16274 50.73713)'::BOX3D,4326) &&
m.the_geom




The query with a join between the 2 table runs in near 60s:
SELECT gid as id, source::integer, target::integer, cost::double precision
as cost,reverse_cost::double precision as reverse_cost FROM streets_db_edges
as s,streets_db_big as m
where m.edge_id=s.id and setsrid('BOX3D(1.65924 43.38176,3.16274
50.73713)'::BOX3D,4326) && m.the_geom

the explain:

Hash Join  (cost=85806.40..329366.66 rows=174676 width=28) (actual
time=22946.953..56012.747 rows=164889 loops=1)
  Hash Cond: (s.id = m.edge_id)
  ->  Seq Scan on streets_db_edges s  (cost=0.00..153395.40 rows=5894540
width=28) (actual time=16.912..17790.444 rows=5894540 loops=1)
  ->  Hash  (cost=85369.71..85369.71 rows=174676 width=8) (actual
time=22898.034..22898.034 rows=164889 loops=1)
        ->  Bitmap Heap Scan on streets_db_big m  (cost=5530.26..85369.71
rows=174676 width=8) (actual time=5446.865..22464.712 rows=164889 loops=1)
              Filter:
('0103000020E61000000100000005000000CE70033E3F8CFA3FE275FD82DDB04540CE70033E
3F8CFA3F43739D465A5E494021020EA14A4D094043739D465A5E494021020EA14A4D0940E275
FD82DDB04540CE70033E3F8CFA3FE275FD82DDB04540'::geometry && the_geom)
              ->  Bitmap Index Scan on streets_db_big_the_geom_idx
(cost=0.00..5530.26 rows=174676 width=0) (actual time=5437.166..5437.166
rows=164889 loops=1)
                    Index Cond:
('0103000020E61000000100000005000000CE70033E3F8CFA3FE275FD82DDB04540CE70033E
3F8CFA3F43739D465A5E494021020EA14A4D094043739D465A5E494021020EA14A4D0940E275
FD82DDB04540CE70033E3F8CFA3FE275FD82DDB04540'::geometry && the_geom)
Total runtime: 56367.929 ms


Is there a way to increase my performances in this type of select queries?


Thanks for help

ERIC

My conf:

Toshiba Tecra A4 with 1 Go Ram running PostgreSQl 8.2
Database on external disk, pg_xlog on local disk

shared_buffers = 228MB
work_mem=50MB
maintenance_work_mem=100MB
effective_cache_size = 500MB