[postgis-users] Slow ST_Intersects and Materialized Views
I have two tables both which have a gemo_4326 columns with a GIST index. - gis.parcels (approximately 1 million records) - gis.layers (approximately 1 thousand records) The gis.layers table has large multipolygons that can include thousands of gis.parcel intersects. When I run ST_Intersects from a gis.parcel row to gis.layers the query is pretty quick (sub 100 ms). When I run an ST_Intersects on a large multipolygon from gis.layers to gis.parcels to find which parcels intersect with the gis.layer, the queries can take upwards of 10 minutes. Here's my query: SELECT DISTINCT parcel.apn FROM gis.parcels as parcel, gis.layers as layer WHERE layer.id = 339 AND ST_Intersects(layer.geom_4326, parcel.geom_4326); Two questions: - How can I improve performance on the ST_Intersects from gis.layers -> gis.parcels? - I'm considering caching the results in a Materialized View, but based on the current performance this would take a couple days. Is there an alternative approach for caching the results? -- Alexander W. Rolek ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[postgis-users] Slow ST_Intersects and Materialized Views
I appreciate the quick responses. Here's the EXPLAIN for the following query: EXPLAIN SELECT DISTINCT parcel.apn FROM gis.parcels as parcel, gis.layers as layer WHERE layer.id = 339 AND ST_Intersects(layer.geom_4326, parcel.geom_4326); Output: "HashAggregate (cost=543.84..805.23 rows=26139 width=11)" " Group Key: parcel.apn" " -> Nested Loop (cost=5.40..478.49 rows=26139 width=11)" "-> Index Scan using layers_pkey on layers layer (cost=0.27..8.29 rows=1 width=56409)" " Index Cond: (id = 339)" "-> Bitmap Heap Scan on parcels parcel (cost=5.13..469.83 rows=37 width=1018)" " Recheck Cond: (layer.geom_4326 && geom_4326)" " Filter: _st_intersects(layer.geom_4326, geom_4326)" " -> Bitmap Index Scan on parcels_geom_4326 (cost=0.00..5.12 rows=111 width=0)" "Index Cond: (layer.geom_4326 && geom_4326)" @Steve.Toutant, I tried your approach, but I'm still getting really slow queries(minutes). Any other ideas? Alexander Rolek ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Slow ST_Intersects and Materialized Views
Hey, the type of info you should consider to give for a meaningfull answer : https://wiki.postgresql.org/wiki/Slow_Query_Questions The slowness could come from many things. For instance layer.id = 339 AND ST_Intersects(layer.geom_4326, parcel.geom_4326); You use 2 conditions, postgres might choose to use first the bad one. You could try to manually force postgres to use first the spatial condition for instance. I'm guessing you have an index on layer.id anyway. Your query feels akward 2015-05-26 22:01 GMT+02:00 Alexander W. Rolek : > I have two tables both which have a gemo_4326 columns with a GIST index. > >- gis.parcels (approximately 1 million records) >- gis.layers (approximately 1 thousand records) > > The gis.layers table has large multipolygons that can include thousands of > gis.parcel intersects. When I run ST_Intersects from a gis.parcel row to > gis.layers the query is pretty quick (sub 100 ms). When I run an > ST_Intersects on a large multipolygon from gis.layers to gis.parcels to > find which parcels intersect with the gis.layer, the queries can take > upwards of 10 minutes. > > Here's my query: > > SELECT DISTINCT > parcel.apn > FROM > gis.parcels as parcel, > gis.layers as layer > WHERE > layer.id = 339 AND > ST_Intersects(layer.geom_4326, parcel.geom_4326); > > Two questions: > >- How can I improve performance on the ST_Intersects from gis.layers >-> gis.parcels? >- I'm considering caching the results in a Materialized View, but >based on the current performance this would take a couple days. Is there an >alternative approach for caching the results? > > > -- > Alexander W. Rolek > > > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Slow ST_Intersects and Materialized Views
Damn, send too early. Your query feels awkward, if you want the link between layer and parcel, you should have at least 2 terms in the select. When you precise a little bit why your query is slow, it will be possible to propose solutions adapted. Cheers, Rémi-C 2015-05-27 10:19 GMT+02:00 Rémi Cura : > Hey, > the type of info you should consider to give for a meaningfull answer : > https://wiki.postgresql.org/wiki/Slow_Query_Questions > > The slowness could come from many things. > > For instance > layer.id = 339 AND > ST_Intersects(layer.geom_4326, parcel.geom_4326); > You use 2 conditions, postgres might choose to use first the bad one. > > You could try to manually force postgres to use first the spatial > condition for instance. > > I'm guessing you have an index on layer.id anyway. > > > Your query feels akward > > > > > > > > 2015-05-26 22:01 GMT+02:00 Alexander W. Rolek : > >> I have two tables both which have a gemo_4326 columns with a GIST index. >> >>- gis.parcels (approximately 1 million records) >>- gis.layers (approximately 1 thousand records) >> >> The gis.layers table has large multipolygons that can include thousands >> of gis.parcel intersects. When I run ST_Intersects from a gis.parcel row to >> gis.layers the query is pretty quick (sub 100 ms). When I run an >> ST_Intersects on a large multipolygon from gis.layers to gis.parcels to >> find which parcels intersect with the gis.layer, the queries can take >> upwards of 10 minutes. >> >> Here's my query: >> >> SELECT DISTINCT >> parcel.apn >> FROM >> gis.parcels as parcel, >> gis.layers as layer >> WHERE >> layer.id = 339 AND >> ST_Intersects(layer.geom_4326, parcel.geom_4326); >> >> Two questions: >> >>- How can I improve performance on the ST_Intersects from gis.layers >>-> gis.parcels? >>- I'm considering caching the results in a Materialized View, but >>based on the current performance this would take a couple days. Is there >> an >>alternative approach for caching the results? >> >> >> -- >> Alexander W. Rolek >> >> >> ___ >> postgis-users mailing list >> postgis-users@lists.osgeo.org >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> > > ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Slow ST_Intersects and Materialized Views
Can you redo that EXPLAIN with EXPLAIN ANALYZE VERBOSE? That'll give use what the database actually does... On Thu, May 28, 2015 at 8:09 AM, Alexander W. Rolek wrote: > I appreciate the quick responses. Here's the EXPLAIN for the following > query: > > EXPLAIN > SELECT DISTINCT > parcel.apn > FROM > gis.parcels as parcel, > gis.layers as layer > WHERE > layer.id = 339 AND > ST_Intersects(layer.geom_4326, parcel.geom_4326); > > Output: > > "HashAggregate (cost=543.84..805.23 rows=26139 width=11)" > " Group Key: parcel.apn" > " -> Nested Loop (cost=5.40..478.49 rows=26139 width=11)" > "-> Index Scan using layers_pkey on layers layer > (cost=0.27..8.29 rows=1 width=56409)" > " Index Cond: (id = 339)" > "-> Bitmap Heap Scan on parcels parcel (cost=5.13..469.83 > rows=37 width=1018)" > " Recheck Cond: (layer.geom_4326 && geom_4326)" > " Filter: _st_intersects(layer.geom_4326, geom_4326)" > " -> Bitmap Index Scan on parcels_geom_4326 > (cost=0.00..5.12 rows=111 width=0)" > "Index Cond: (layer.geom_4326 && geom_4326)" > > > @Steve.Toutant, I tried your approach, but I'm still getting really slow > queries(minutes). Any other ideas? > > Alexander Rolek > > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[postgis-users] Slow ST_Intersects and Materialized Views (EXPLAIN ANALYZE VERBOSE)
Bborie Park - Here's the expanded query: EXPLAIN ANALYZE VERBOSE SELECT DISTINCT parcel.apn FROM gis.parcels as parcel, gis.layers as layer WHERE layer.id = 339 AND ST_Intersects(layer.geom_4326, parcel.geom_4326); and the output: "HashAggregate (cost=543.84..805.23 rows=26139 width=11) (actual time=465830.953..465835.537 rows=11967 loops=1)" " Output: parcel.apn" " Group Key: parcel.apn" " -> Nested Loop (cost=5.40..478.49 rows=26139 width=11) (actual time=1326.282..465782.372 rows=14707 loops=1)" "Output: parcel.apn" "-> Index Scan using layers_pkey on gis.layers layer (cost=0.27..8.29 rows=1 width=56409) (actual time=0.215..0.218 rows=1 loops=1)" " Output: layer.id, layer.type, layer.source, layer.data, layer.wiki, layer.geom_4326, layer.created, layer.updated" " Index Cond: (layer.id = 339)" "-> Bitmap Heap Scan on gis.parcels parcel (cost=5.13..469.83 rows=37 width=1018) (actual time=1326.060..465748.934 rows=14707 loops=1)" " Output: parcel.id, parcel.apn, parcel.source, parcel.data, parcel.wiki, parcel.geom_4326, parcel.created, parcel.updated" " Recheck Cond: (layer.geom_4326 && parcel.geom_4326)" " Rows Removed by Index Recheck: 116269" " Filter: _st_intersects(layer.geom_4326, parcel.geom_4326)" " Rows Removed by Filter: 901418" " Heap Blocks: exact=34190 lossy=132122" " -> Bitmap Index Scan on parcels_geom_4326 (cost=0.00..5.12 rows=111 width=0) (actual time=1278.238..1278.238 rows=916125 loops=1)" "Index Cond: (layer.geom_4326 && parcel.geom_4326)" "Planning time: 23.746 ms" "Execution time: 465846.118 ms" -- Alexander W. Rolek ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[postgis-users] Slow ST_Intersects and Materialized Views (EXPLAIN ANALYZE VERBOSE)
Paul - I already have GIST indexes on both tables: CREATE INDEX layers_geom_4326 ON gis.layers USING gist (geom_4326); CREATE INDEX parcels_geom_4326 ON gis.parcels USING gist (geom_4326); And from the output it looks like it's doing an "Bitmap index scan on parcels_geom_4326". Is the gist index not being used? -- Alexander W. Rolek ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Slow ST_Intersects and Materialized Views (EXPLAIN ANALYZE VERBOSE)
CREATE INDEX parcels_gix ON gis.parcels USING GIST (geom_4326); ANALYZE; On Thu, May 28, 2015 at 10:58 AM, Alexander W. Rolek wrote: > Bborie Park - > > Here's the expanded query: > > EXPLAIN ANALYZE VERBOSE > SELECT DISTINCT > parcel.apn > FROM > gis.parcels as parcel, > gis.layers as layer > WHERE > layer.id = 339 AND > ST_Intersects(layer.geom_4326, parcel.geom_4326); > > and the output: > > "HashAggregate (cost=543.84..805.23 rows=26139 width=11) (actual > time=465830.953..465835.537 rows=11967 loops=1)" > " Output: parcel.apn" > " Group Key: parcel.apn" > " -> Nested Loop (cost=5.40..478.49 rows=26139 width=11) (actual > time=1326.282..465782.372 rows=14707 loops=1)" > "Output: parcel.apn" > "-> Index Scan using layers_pkey on gis.layers layer > (cost=0.27..8.29 rows=1 width=56409) (actual time=0.215..0.218 rows=1 > loops=1)" > " Output: layer.id, layer.type, layer.source, layer.data, > layer.wiki, layer.geom_4326, layer.created, layer.updated" > " Index Cond: (layer.id = 339)" > "-> Bitmap Heap Scan on gis.parcels parcel (cost=5.13..469.83 > rows=37 width=1018) (actual time=1326.060..465748.934 rows=14707 loops=1)" > " Output: parcel.id, parcel.apn, parcel.source, parcel.data, > parcel.wiki, parcel.geom_4326, parcel.created, parcel.updated" > " Recheck Cond: (layer.geom_4326 && parcel.geom_4326)" > " Rows Removed by Index Recheck: 116269" > " Filter: _st_intersects(layer.geom_4326, parcel.geom_4326)" > " Rows Removed by Filter: 901418" > " Heap Blocks: exact=34190 lossy=132122" > " -> Bitmap Index Scan on parcels_geom_4326 (cost=0.00..5.12 > rows=111 width=0) (actual time=1278.238..1278.238 rows=916125 loops=1)" > "Index Cond: (layer.geom_4326 && parcel.geom_4326)" > "Planning time: 23.746 ms" > "Execution time: 465846.118 ms" > > > -- > Alexander W. Rolek > > > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
