[postgis-users] Help with Bad Query Plan

[Paragon Corporation]

Oliver,

Did you try the && instead of ST_Intersects.  That would help confirm if its
an issue with && or _ST_Intersects.

What is strange is that in all the plans, it looks like its doing the right
thing.

Did you change your postgresql.conf or is it still at its default settings.
If still at its default you could just be IO bound and need to up those
settings. 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Oliver
Snowden
Sent: Friday, January 09, 2009 12:44 PM
To: postgis-users at postgis.refractions.net
Subject: RE: [postgis-users] Help with Bad Query Plan

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();
    }
}

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