[postgis-users] Looking for more performance on select

[Obe, Regina]

I just noticed something else in your explain.  Its doing a seq scan on
your streets_db_edges.  I would have expected that to be an index scan.
 
Are the data types of your id and edge_id of the same type? If they
aren't then it would do a seq scan instead of indexed scan.  I know at
least in earlier versions  if you had an int8 in one table and an int4
in another, it would not do an index scan.  This might have changed in
the 8.2 version.

________________________________

From: Obe, Regina
Sent: Tue 12/26/2006 2:44 PM
To: ericfrancois at hotmail.com; PostGIS Users Discussion
Subject: RE: [postgis-users] Looking for more performance on select


I would change your index to a clustered index on your id  field in
streets_db_edges
 
For your other table streets_db_big - I'm guessing a clustered index on
your the_geom field would give you better performance than a cluster on
the edge_id field.  Having clustered indexes has improved my speeds a
lot.
 

________________________________

From: postgis-users-bounces at postgis.refractions.net on behalf of Eric
FRANCOIS
Sent: Tue 12/26/2006 10:12 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Looking for more performance on select



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:
('0103000020E61000000100000005000000CE70033E3F8CFA3FE275FD82DDB04540CE70
033E
3F8CFA3F43739D465A5E494021020EA14A4D094043739D465A5E494021020EA14A4D0940
E275
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:
('0103000020E61000000100000005000000CE70033E3F8CFA3FE275FD82DDB04540CE70
033E
3F8CFA3F43739D465A5E494021020EA14A4D094043739D465A5E494021020EA14A4D0940
E275
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

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users





-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally
privileged and/or exempt from disclosure pursuant to Massachusetts
law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and
delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20061226/221768ae/attachment.html>