Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have a "huge" spatial database: 72GB, 200 millions documents with geometries (points) I have a GIST index on the geometries

the database:

CREATE TABLE resources
(
id bigint NOT NULL,
accuracy varchar(3),
owner varchar(100),
title varchar(500),
posted timestamp,   
nbtags int,
url text,
description text,
rawtags text,
tags text,
geom geometry NOT NULL,
CONSTRAINT resources_pkey PRIMARY KEY (id)
);
 INDEX resources_index_rep ON resources USING GIST (geom);

CREATE RULE resources_rule AS ON INSERT TO resources
WHERE EXISTS(SELECT 1 FROM resources
            WHERE id =NEW.id)
DO INSTEAD NOTHING;

Of course I want to query this database. Nevertheless, it's very very slow ... (I did analyzes and Vacuums)

for example with area of France:

EXPLAIN ANALYZE
SELECT count(*) 
FROM resources
WHERE st_intersects(st_geomfromtext('POLYGON((-5.134723 41.364166,-5.134723 51.09111,9.562222 51.09111,9.562222 41.364166,-5.134723 41.364166))',4326), geom)

QUERY PLAN
Aggregate  (cost=14411182.45..14411182.46 rows=1 width=0) (actual time=17368911.185..17368911.186 rows=1 loops=1)
 ->  Bitmap Heap Scan on resources  (cost=890493.27..14391392.83 rows=7915848 width=0) (actual time=2627029.235..17357552.440 rows=21381758 loops=1)
    Recheck Cond: ('0103000020E6100000010000000500000077137CD3F48914C05628D2FD9CAE444077137CD3F48914C0562B137EA98B4940F52EDE8FDB1F2340562B137EA98B4940F52EDE8FDB1F23405628D2FD9CAE444077137CD3F48914C05628D2FD9CAE4440'::geometry && geom)
    Filter: _st_intersects('0103000020E6100000010000000500000077137CD3F48914C05628D2FD9CAE444077137CD3F48914C0562B137EA98B4940F52EDE8FDB1F2340562B137EA98B4940F52EDE8FDB1F23405628D2FD9CAE444077137CD3F48914C05628D2FD9CAE4440'::geometry, geom)
    ->  Bitmap Index Scan on resources_index_rep  (cost=0.00..888514.31 rows=23747545 width=0) (actual time=2626090.609..2626090.609 rows=21381788 loops=1)
          Index Cond: ('0103000020E6100000010000000500000077137CD3F48914C05628D2FD9CAE444077137CD3F48914C0562B137EA98B4940F52EDE8FDB1F2340562B137EA98B4940F52EDE8FDB1F23405628D2FD9CAE444077137CD3F48914C05628D2FD9CAE4440'::geometry && geom)
Total runtime: 17368946.473 ms
7 row(s)

it took almost 5 hours.

and with Switzerland:

EXPLAIN ANALYZE
SELECT count(*) 
FROM resources
WHERE st_intersects(st_geomfromtext('POLYGON((5.96611 45.829437,5.96611 47.806938,10.488913 47.806938,10.488913 45.829437,5.96611 45.829437))',4326), geom)

QUERY PLAN
Aggregate  (cost=5623425.68..5623425.69 rows=1 width=0) (actual time=6798842.064..6798842.064 rows=1 loops=1)
->  Bitmap Heap Scan on resources  (cost=88982.29..5621452.65 rows=789212 width=0) (actual time=783383.496..6797250.482 rows=2539057 loops=1)
    Recheck Cond: ('0103000020E61000000100000005000000AF5A99F04BDD1740D28BDAFD2AEA4640AF5A99F04BDD174028F38FBE49E74740B22D03CE52FA244028F38FBE49E74740B22D03CE52FA2440D28BDAFD2AEA4640AF5A99F04BDD1740D28BDAFD2AEA4640'::geometry && geom)
    Filter: _st_intersects('0103000020E61000000100000005000000AF5A99F04BDD1740D28BDAFD2AEA4640AF5A99F04BDD174028F38FBE49E74740B22D03CE52FA244028F38FBE49E74740B22D03CE52FA2440D28BDAFD2AEA4640AF5A99F04BDD1740D28BDAFD2AEA4640'::geometry, geom)
    ->  Bitmap Index Scan on resources_index_rep  (cost=0.00..88784.99 rows=2367636 width=0) (actual time=782664.847..782664.847 rows=2539063 loops=1)
          Index Cond: ('0103000020E61000000100000005000000AF5A99F04BDD1740D28BDAFD2AEA4640AF5A99F04BDD174028F38FBE49E74740B22D03CE52FA244028F38FBE49E74740B22D03CE52FA2440D28BDAFD2AEA4640AF5A99F04BDD1740D28BDAFD2AEA4640'::geometry && geom)
Total runtime: 6798852.955 ms
7 row(s)

it took almost 2 hours.

I'm looking for how to improve the performance?

About our server:

- CPU: 4
- RAM: 16 GB
- OS: Ubuntu 12.04 LTS 64bit
share|improve this question
3  
How big are the documents? The size of the 'description' field generally? If the records are tending to exceed 8K (the page size), you'll end up with many of them being toasted into side tuples, which tends to slow things by about a factor of 10 (which, coincidentally is the difference between how long the planning things execution will take and how long it does take). A solution might be to break the table into a geometry table, and a contents table, and join the result set back onto them for query purposes. –  Paul Ramsey Mar 7 '13 at 23:55
    
I didn't make statistics on it, they can be quite long. I planned to make a table Geometries (id, geom). But extracting it from main table, and then updating the main table, by replacing the geometry by the id was taking ages (days) ... I will definitively try again in this way if it can be about a factor 10 –  Ulu Mar 8 '13 at 9:46
    
Does having the polygon from text enable a spatial index on that polygon to be created? So you might want to actually load in France as a single polygon, create a spatial index on it then run the query. I have also been wondering if clustering on the spatial index might help too. It is something I have yet to try but just a thought. –  tjmgis Mar 8 '13 at 17:07
    
The polygon is a single constant value, spatial indexing it will make no difference at all. –  Paul Ramsey Mar 8 '13 at 17:32
    
I see you have quite a lot of RAM on the server. Have you increased the PostgreSQL shared_buffers setting to take advantage of it? –  Paul Ramsey Mar 8 '13 at 17:33

1 Answer 1

Don't have too much experience on db performance improving. But I find this might be helpful.

share|improve this answer

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

    
thanks for the link. I applied some of the advices already. I will look if others can be helpful. I added details about the server as it's important. CPU and RAM are fine but I don't know about hard drive performance of our server. –  Ulu Mar 8 '13 at 10:01

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.