1

I am working with 3 tables with 3 x 500 000 rows. There is one column that gives each object a unique ID (UIDN), there also is a column with the geom data (the_geom). There also are some other columns but the 2 above are the most important.

When I run my code, it takes a very long time, beceause these 3 tables are so big (I tested the same code with smaller columns and there it runs much faster, so I know for sure that the problem is the size of these columns).

PostGIS automatically made a GIST index for the 'the_geom' columns. Beceause it was real slow, I added a btree index for the 'uidn' column. I hoped this would speed things up, but it doesn't.

What do I need to do? Do I need another type of index?

EDIT:

Here is an example of a real query found in my function

            SELECT wbn.uidn, wgo.uidn
            FROM wbn, wgo
            WHERE ST_Intersects (wgo.the_geom, wbn.the_geom) = 't'
              AND ST_Length (ST_Intersection (wgo.the_geom, wbn.the_geom)) > 2
              AND wbn.uidn = f_uidn
              AND wgo.type = f_type

wbn and wgo are two of the big tables I was talking about

  • 3
    What is the query? – Nathan W Apr 16 '14 at 12:36
  • It's a calculation, using 2 functions, so it's quite long... but the calculation goed real fast if the tables are small, and real slow when the tables are big. The query's used in the functions are something like SELECT the_geom FROM table WHERE uidn : *** – user28088 Apr 16 '14 at 12:54
  • 2
    Indexing often doesn't mean much when the query is doing a full table scan. The WHERE clause is the most important piece of the puzzle. You can strip it down to a representative sample, but if you leave it out of the question, you are unlikely to get a correct answer. – Vince Apr 16 '14 at 13:11
  • 4
    Real query and EXPLAIN ANALYZE plan of it are absolute must. – Jakub Kania Apr 16 '14 at 17:07
1

It's important to understand that you cannot use more than one index into any one table at one time, and that indexes on columns unrelated to queries are of no use. This is why building an index on 'uidn' is of no use on an ST_Intersects query that involves a 'the_geom' column.

One thing you can do to improve the efficiency of a spatial index is to spatially defragment the tables (recreate the 'wbn' and 'wgo' tables in spatial index order). This will let the spatial queries operate as efficiently as possible (with higher cache hits for neighboring pages). Unfortunately, there aren't many tools for this, but if you have columns in the table which correlate to spatial location (e.g., state, county, zipcode, phone prefix,..) or actual coordinates (e.g., {lat,lon},{cenx,ceny},...) you can fashion an ORDER BY clause in a CREATE TABLE clonename AS SELECT * FROM sourcename ... query that will improve query performance (if, in fact, the table was previously fragmented).

The only other thing you can do is try to avoid queries which join large tables to large tables. Even with an index, a 500k x 500k query has the potential of doing 25x10^10 (250 billion) comparisons. While a join on an ID column doesn't represent much work for a database, driving a query through a spatial operator does. Anything you can do to reduce the row count on the table driving the query will help.

  • 1
    The way to "spatially defragment" a table is with the CLUSTER command. See the Boundless tutorial for a nice writeup on this. – dbaston Apr 17 '14 at 13:37
  • 1
    Also, queries can take advantage of multiple indexes in a table if the planner estimates that to be more efficient than a sequential scan. That's what a bitmap scan is. – dbaston Apr 17 '14 at 13:39
0

I think your statement

PostGIS automatically made a GIST index for the 'the_geom' columns.

is incorrect. See PostGIS2 have automatic BBOX indexing?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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