Hello,
I have a very slow query,
if I remove the
WHERE active is null
Its work great
(there is an index on that field of course)
Query:
SELECT data_gis.id, ex_data.title, ex_data.description, ex_data.ex_text,
ST_Y(ST_Transform(way, 4326)) as lat, ST_X(ST_Transform(way, 4326)) as lon, active
FROM ex_data join data_gis
on ex_data.data_gis_id = data_gis.id
WHERE
active is null
and ST_DWithin(ST_Transform(ST_SetSrid(ST_Point(51.758783,-1.152665), 4326), 3857), way, 5000.0)
ORDER BY
way <-> ST_Transform(ST_SetSrid(ST_Point(51.758783,-1.152665), 4326), 3857)
LIMIT 25;
Query plan:
Limit (cost=60141.06..60143.98 rows=25 width=269)
-> Gather Merge (cost=60141.06..60175.13 rows=292 width=269)
Workers Planned: 2
-> Sort (cost=59141.04..59141.40 rows=146 width=269)
Sort Key: ((data_gis.way <-> '010100....'::geometry))
-> Nested Loop (cost=285.26..59135.79 rows=146 width=269)
-> Parallel Bitmap Heap Scan on ex_data (cost=284.82..28888.62 rows=10972 width=148)
Recheck Cond: (active IS NULL)
-> Bitmap Index Scan on ex_data_org_lang_pkey (cost=0.00..278.24 rows=26334 width=0)
Index Cond: (org_lang IS NULL)
-> Index Scan using data_gis_pkey on data_gis (cost=0.44..2.76 rows=1 width=137)
Index Cond: (id = ex_data.data_gis_id)
Filter: ((way && '010300002......'::geometry) AND ('010100002011.....'::geometry && st_expand(way, '5000'::double precision)) AND _st_dwithin('01010...'::geometry, way, '5000'::double precision))
(13 rows)
asked
11 Jul '19, 07:35
altopalo
53●8●10●13
accept rate:
0%