I just want to see if I can duplicate the result, having the original data makes that a faster process, since I can copy your use case exactly.
P On Fri, Jun 6, 2008 at 10:53 AM, Steve Kondik <[EMAIL PROTECTED]> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Paul Ramsey wrote: >> Steve, >> >> Any chance you could share your data with the developers? this is a >> very strange result. >> >> P >> > > Sure, I exported the table with pgsql2shp. > > http://n0rp.chemlab.org/geography.tbz2 > > There's nothing spectacular about the data, it's just > state/county/zipcode from the US census. > > >> On Fri, Jun 6, 2008 at 9:37 AM, Steve Kondik <[EMAIL PROTECTED]> wrote: >> Steve Kondik wrote: >>>>> Mark Cave-Ayland wrote: >>>>>> Steve Kondik wrote: >>>>>>> -----BEGIN PGP SIGNED MESSAGE----- >>>>>>> Hash: SHA1 >>>>>>> >>>>>>> I'm seeing this same kind of slowness. I am in the process of migrating >>>>>>> an application from Oracle to Postgres. One query that seems to take >>>>>>> longer than it should is a very simple point-in-bbox query which takes 3 >>>>>>> seconds. There are 32000 rows in the table, and the query is as simple >>>>>>> as it can be: >>>>>>> >>>>>>> select count(*) from geography where type='Z' and centroid && (select >>>>>>> geometry from geography where id=69495); >>>>>>> >>>>>>> The second geography is the USA, so this query gets all zipcodes inside >>>>>>> the bbox, using precalculated centroids. The query plan looks fine as >>>>>>> well: >>>>>>> >>>>>>> Aggregate (cost=16.56..16.57 rows=1 width=0) (actual >>>>>>> time=2407.123..2407.125 rows=1 loops=1) >>>>>>> InitPlan >>>>>>> -> Index Scan using geography_pkey on geography (cost=0.00..8.28 >>>>>>> rows=1 width=4528) (actual time=0.027..0.030 rows=1 loops=1) >>>>>>> Index Cond: (id = 69495) >>>>>>> -> Index Scan using idx_geography_centroid_z on geography >>>>>>> (cost=0.00..8.28 rows=1 width=0) (actual time=1.417..2355.049 rows=29687 >>>>>>> loops=1) >>>>>>> Index Cond: (centroid && $0) >>>>>>> Filter: (centroid && $0) >>>>>>> Total runtime: 2407.194 ms >>>>>>> (8 rows) >>>>>>> >>>>>>> >>>>>>> Any thoughts? This query takes 200ms on Oracle. It seems like >>>>>>> something is wrong for it to take 3 seconds on PostGIS. It's faster for >>>>>>> smaller geometries (like a state), but I don't understand why since its >>>>>>> just point-inside-bbox. >>>>>> Hi Steve, >>>>>> Well from the above you can see fairly easily that the time is >>>>>> disappearing into the second index scan. The above output doesn't look >>>>>> quite right somehow (it's missing at least one node indicator) - can you >>>>>> try cutting and pasting the exact output from EXPLAIN into an email >>>>>> again? Also we'll need the output of "\d geography" from within psql. >>>>> >>>>>> ATB, >>>>>> Mark. >>>>> >>>>> >>>>> Sure thing. It's taking quite a bit longer now because we're doing some >>>>> bulk loading. When nothing else is running, the query takes about 4 >>>>> seconds. >>>>> >>>>> >>>>> health_central_22=> explain analyze select count(*) from geography where >>>>> type='Z' and centroid && (select geometry from geography where id=69495); >>>>> >>>>> QUERY PLAN >>>>> >>>>> - >>>>> --------------------------------------------------------------------------------------------------------------------------------------------------- >>>>> Aggregate (cost=16.56..16.57 rows=1 width=0) (actual >>>>> time=16534.739..16534.741 rows=1 loops=1) >>>>> InitPlan >>>>> -> Index Scan using geography_pkey on geography (cost=0.00..8.28 >>>>> rows=1 width=4528) (actual time=0.076..0.080 rows=1 loops=1) >>>>> Index Cond: (id = 69495) >>>>> -> Index Scan using idx_geography_centroid_z on geography >>>>> (cost=0.00..8.28 rows=1 width=0) (actual time=25.453..16192.427 >>>>> rows=29687 loops=1) >>>>> Index Cond: (centroid && $0) >>>>> Filter: (centroid && $0) >>>>> Total runtime: 16534.809 ms >>>>> (8 rows) >>>>> >>>>> health_central_22=> \d geography >>>>> Table "public.geography" >>>>> Column | Type | Modifiers >>>>> --------------+------------------------+----------- >>>>> type | character(1) | not null >>>>> id | bigint | not null >>>>> centroid | geometry | >>>>> geometry | geometry | >>>>> name | character varying(32) | not null >>>>> population | bigint | >>>>> abbreviation | character varying(2) | >>>>> po_name | character varying(100) | >>>>> id_geo_state | bigint | >>>>> Indexes: >>>>> "geography_pkey" PRIMARY KEY, btree (id) >>>>> "idx_geography_2" btree (name) >>>>> "idx_geography_3" btree (abbreviation) >>>>> "idx_geography_4" btree (id_geo_state) >>>>> "idx_geography_centroid" gist (centroid) >>>>> "idx_geography_centroid_c" gist (centroid) WHERE type = 'C'::bpchar >>>>> "idx_geography_centroid_s" gist (centroid) WHERE type = 'S'::bpchar >>>>> "idx_geography_centroid_z" gist (centroid) WHERE type = 'Z'::bpchar >>>>> "idx_geography_geometry" gist (geometry) CLUSTER >>>>> "idx_geography_geometry_c" gist (geometry) WHERE type = 'C'::bpchar >>>>> "idx_geography_geometry_r" gist (geometry) WHERE type = 'R'::bpchar >>>>> "idx_geography_geometry_s" gist (geometry) WHERE type = 'S'::bpchar >>>>> "idx_geography_geometry_z" gist (geometry) WHERE type = 'Z'::bpchar >>>>> Foreign-key constraints: >>>>> "fk_geography_1" FOREIGN KEY (id_geo_state) REFERENCES geography(id) >>>>> >> Adding force_2d speeds this up immensely. Not sure I understand why >> this is necessary, but I'm still getting used to PostGIS. >> >> >> health_central_22=> explain analyze select id,name from geography where >> type='Z' and centroid && (select force_2d(geometry) from geography where >> id=69495); >> QUERY >> PLAN >> - >> ------------------------------------------------------------------------------------------------------------------------------------------ >> Index Scan using idx_geography_centroid on geography (cost=8.29..16.57 >> rows=1 width=14) (actual time=0.263..125.338 rows=29687 loops=1) >> Index Cond: (centroid && $0) >> Filter: ((centroid && $0) AND (type = 'Z'::bpchar)) >> InitPlan >> -> Index Scan using geography_pkey on geography (cost=0.00..8.29 >> rows=1 width=4441) (actual time=0.158..0.161 rows=1 loops=1) >> Index Cond: (id = 69495) >> Total runtime: 171.229 ms >> (7 rows) > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users >>> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://postgis.refractions.net/mailman/listinfo/postgis-users > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.6 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iD8DBQFISXmUMrBfzfMVwMcRAk0/AJsHZwP3p8AUVgSvWpPHFoGW/KCnTQCfY1QN > ZpmBnxOzQ3KQX2TG+03yVxM= > =z/t9 > -----END PGP SIGNATURE----- > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
