[postgis-users] Help with Bad Query Plan

[Paul Ramsey]

I just tried to load up the geometry portion of the problem only, and
I am *not* seeing the same effect.  Does the geometry-only part of the
query also exhibit the long query time for you?

select count(*) from gdors_geography g1, gdors_geography g2 where
st_intersects(g1.the_geom, g2.the_geom) and g2.gid = 3;

With an index this takes me 9s and without it takes 15s (hooray,
prepared geometry, this would probably have been a multi-minute
problem in the good old days).

If you're I/O bound that *could* be part of the problem, however, for
something like Asia. It's a big geometry, it's stored in toast tuples,
if it's too big to cache, it would be a lot of work to haul it out
over and over and over again. Still, that should pertain for the
non-indexed cases too.  Is the index forcing a full spatial table join
to happen before the individual record is pulled (I can't read plans,
it's tragic)?

P.

On Fri, Jan 9, 2009 at 10:45 AM, Paragon Corporation <lr at pcorp.us> wrote:
> 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
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>