[postgis-users] Help with Bad Query Plan
Obe, Regina
robe.dnd at cityofboston.gov
Fri Jan 9 13:00:21 PST 2009
Oliver,
Disregard my last comment - I see you did try plain &&. So as expected it is faster
than ST_Intersects and given your geometries are so large, this is not surprising. Yes it && should be slower than doing a btree search too.
I think Paul is on to something. for those large geometries, I think you will need a lot of memory and with non-prepared (pre geos 3.1) that asia would be copied for each loop. So if you are still at your default postgresql settings, then its no wonder you have problems. You could try using ST_DWithin instead of ST_Intersects.
Somethink like ST_DWithin(a.the_geom, b.the_geom, 0.001)
ST_DWithin pre 3.1 tended to perform better with large multipolygons than ST_Intersects. 3.1 I would say ST_Intersects would always win out.
Now why having all indexes utilized actually makes things worse sometimes (hmm I think that is what I saw) is a bit mysterious except as Paul suggests -- the spatial index is making the spatial search more attractive looking to the planner than it should and looking at the plan (at least for &&, it is being applied first). Also trying to utilize more indexes would require more memory -- so could be your memory settings on postgresql.conf are just too low.
Yes text Plans are hard to read -- I do much better reading graphical ones and hmm I think I've pretty much forgotten how to read the text ones aside from checking whether an index is used or not, the strategy, actual vs. relative row count, and flipping the plan upside down to get a sense of sequencing.
Did we ever ask the obvious question of which Geos are you using?
SELECT postgis_full_version();
Hope that helps,
Regina
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net on behalf of Paul Ramsey
Sent: Fri 1/9/2009 1:54 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Help with Bad Query Plan
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
>
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090109/07049112/attachment.html>
More information about the postgis-users
mailing list