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
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
> WH
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) (actua
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
> FR
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 widt
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
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 choos
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
