[postgis-users] Help with Bad Query Plan

[Oliver Snowden]

Hi Mark/Regina, I have installed PostgreSQL 8.2.  Unfortunately that is
still slow.  I am not sure how difficult it is for you to recreate the
database but I have embedded some Java code to create some sample report
data, should you want to/have time.  10000 refers to the report entries to
make, [21619] + 1 refers to the number of geometries.

All the best, Oliver.

-- Installed PostgreSQL 8.2.9-1

-- Query did not finish
SELECT geolink.report_id, geography.gid
FROM gdors_geolink geolink, gdors_geography geography, gdors_geography
selected_geography
WHERE geolink.temp_report_date BETWEEN '2008-01-06' AND '2009-01-06'
AND geolink.geom_id = geography.gid
AND selected_geography.gid=3
AND ST_Intersects(selected_geography.the_geom, geography.the_geom);
"Nested Loop  (cost=0.00..91.85 rows=1 width=8)"
"  ->  Nested Loop  (cost=0.00..16.56 rows=1 width=4)"
"        Join Filter: _st_intersects(selected_geography.the_geom,
geography.the_geom)"
"        ->  Index Scan using gdors_geography_pkey on gdors_geography
selected_geography  (cost=0.00..8.27 rows=1 width=3470)"
"              Index Cond: (gid = 3)"
"        ->  Index Scan using gdors_geography_the_geom on gdors_geography
geography  (cost=0.00..8.27 rows=1 width=3474)"
"              Index Cond: (selected_geography.the_geom &&
geography.the_geom)"
"              Filter: (selected_geography.the_geom && geography.the_geom)"
"  ->  Index Scan using gdors_geolink_pkey on gdors_geolink geolink
(cost=0.00..75.28 rows=1 width=8)"
"        Index Cond: (geolink.geom_id = geography.gid)"
"        Filter: ((temp_report_date >= '2008-01-06'::date) AND
(temp_report_date <= '2009-01-06'::date))"

-- Query with && - at least we get a result...although slower than without
the spatial index.
-- 81 rows, ~21000ms.
SELECT geolink.report_id, geography.gid
FROM gdors_geolink geolink, gdors_geography geography, gdors_geography
selected_geography
WHERE geolink.temp_report_date BETWEEN '2008-01-06' AND '2009-01-06'
AND geolink.geom_id = geography.gid
AND selected_geography.gid=3
AND selected_geography.the_geom && geography.the_geom;
"Hash Join  (cost=16.61..231.58 rows=1 width=8)"
"  Hash Cond: (geolink.geom_id = geography.gid)"
"  ->  Seq Scan on gdors_geolink geolink  (cost=0.00..214.00 rows=257
width=8)"
"        Filter: ((temp_report_date >= '2008-01-06'::date) AND
(temp_report_date <= '2009-01-06'::date))"
"  ->  Hash  (cost=16.56..16.56 rows=4 width=4)"
"        ->  Nested Loop  (cost=0.00..16.56 rows=4 width=4)"
"              ->  Index Scan using gdors_geography_pkey on gdors_geography
selected_geography  (cost=0.00..8.27 rows=1 width=3470)"
"                    Index Cond: (gid = 3)"
"              ->  Index Scan using gdors_geography_the_geom on
gdors_geography geography  (cost=0.00..8.27 rows=1 width=3474)"
"                    Index Cond: (selected_geography.the_geom &&
geography.the_geom)"
"                    Filter: (selected_geography.the_geom &&
geography.the_geom)"

Sample report data:

package samplereportdata;

import java.text.SimpleDateFormat;
import java.util.Date;

// quick hack
public class Main {

    private static void getSample() {
        java.util.Random r = new java.util.Random();
        long timeNow = java.util.Calendar.getInstance().getTimeInMillis();

        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String sDate = "";

        for (int i = 0; i < 10000; i++) {
            int j = r.nextInt(21619) + 1;
            r.nextLong();
            date.setTime(new Float(timeNow * r.nextFloat()).longValue());
            sDate = sdf.format(date);

            System.out.println("INSERT INTO gdors_geolink(report_id,
geom_id, lastupdated, temp_report_date) VALUES
("+(i+1)+","+j+",'2006-06-01', '"+sDate +"');");
        }
    }

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        getSample();
    }
}