[postgis-users] Looking for more performance on select

[Mark Cave-Ayland]

On Tue, 2006-12-26 at 16:12 +0100, Eric FRANCOIS wrote:
> 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


Hi Eric,

It looks like you've got your basic tuning done, so it's time to speak
to the PostgreSQL experts. If you haven't already done so, join the
pgsql-performance list on postgresql.org and post your query there for
more feedback.


Kind regards,

Mark.