Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A month ago i got acquainted with PostgreSQL and only now i've noticed that some of my queries are pretty slow. Well, for example, i have table:

CREATE TABLE sometable
(
  gid serial NOT NULL,
  geom geometry(LineString) NOT NULL,
  CONSTRAINT sometable_pkey PRIMARY KEY (gid)
)
WITH (
  OIDS=FALSE
);
CREATE INDEX sometable_geom_gist
  ON sometable
  USING gist
  (geom);

The table contains about 150 000 rows and when i try to get all rows by SELECT * FROM sometable it takes 10 seconds! I doubt that its normal for PG.

When i'm trying to use spatial indexes by querying something like

SELECT ST_AsBinary(geom) FROM sometable
    WHERE   ST_Intersects(geom,
            ST_MakeEnvelope(-10000, -20000, 10000, 15000))

It takes 9 sec. (100 000 rows). And

SELECT ST_AsBinary(geom) from sometable

takes 13 sec.


EXPLAIN ANALYZE
SELECT * FROM sometable
Seq Scan on sometable (cost=0.00..3406.47 rows=151547 width=69)
(actual time=0.021..102.959 rows=151547 loops=1) 
Total runtime: 165.174 ms

EXPLAIN ANALYZE
SELECT ST_AsBinary(geom) FROM sometable
Seq Scan on sometable (cost=0.00..3785.34 rows=151547
width=65) (actual time=0.030..234.241 rows=151547 loops=1) 
Total runtime: 296.704 ms

EXPLAIN ANALYZE
SELECT ST_AsBinary(geom) FROM sometable
        WHERE   ST_Intersects(geom,
                ST_MakeEnvelope(-10000, -20000, 10000, 15000))
Bitmap Heap Scan on sometable (cost=3142.47..30805.90 rows=32623 width=65) (actual time=38.644..1066.704 rows=98469 loops=1)
  Recheck Cond: (geom && '01030000000100000005000000000000A2941A6DC2000000A2941A6DC2000000A2941A6DC2000000A2941A6D42000000A2941A6D42000000A2941A6D42000000A2941A6D42000000A2941A6DC2000000A2941A6DC2000000A2941A6DC2'::geometry)
  Filter: _st_intersects(geom, '01030000000100000005000000000000A2941A6DC2000000A2941A6DC2000000A2941A6DC2000000A2941A6D42000000A2941A6D42000000A2941A6D42000000A2941A6D42000000A2941A6DC2000000A2941A6DC2000000A2941A6DC2'::geometry)
  ->  Bitmap Index Scan on sometable_geom_gist  (cost=0.00..3134.31 rows=97870 width=0) (actual time=38.109..38.109 rows=98469 loops=1)
        Index Cond: (geom && '01030000000100000005000000000000A2941A6DC2000000A2941A6DC2000000A2941A6DC2000000A2941A6D42000000A2941A6D42000000A2941A6D42000000A2941A6D42000000A2941A6DC2000000A2941A6DC2000000A2941A6DC2'::geometry)
Total runtime: 1125.249 ms

Well, would you be kind enough as give me some tips how to solve the problem or explain the situation? Thank you.

share|improve this question
2  
It looks like you have problems with getting or displaying the results. The EXPLAIN ANALYZE output shows that the DB backend fetches all the 150k rows in 165 milliseconds - the 10 seconds is lost somewhere else. The same can be seen from the other queries (297 ms vs 13 second and 1125 ms vs 9 secs). For me this means that either the network is very slow between the client and the DB server or the client is sluggish to show the results (note that with 150,000 rows you can't expect displaying to be fast). –  dezso Oct 17 '13 at 7:46
    
@dezso The client and the server are on the same machine. –  Andy Zubowsky Oct 17 '13 at 8:00
1  
@AndyZubowsky: but still the client must read and process 150.000 rows. Check the code that is looping through those results - there you'll find your bottleneck. –  a_horse_with_no_name Jan 7 at 21:57
    
@dezso: I think your comment should be the (accepted) answer. The lion's share is obviously spent in transfer / display, not in the db. –  Erwin Brandstetter Jan 8 at 16:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.