NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

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's gravatar image

altopalo
5381013
accept rate: 0%

edited 11 Jul '19, 07:35


This question is off-topic here since it has nothing to do with OSM. Assuming that "acive" is in the same table as data_gis then you can likely fix the issue by creating a conditional index (create index foo on table on data_gis using gist(way) where active is null).

permanent link

answered 11 Jul '19, 08:11

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
82.5k927201273
accept rate: 23%

tn'x but active field is in ex_data table and way is in data_gis table.

(11 Jul '19, 08:32) altopalo

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×147
×134
×33

question asked: 11 Jul '19, 07:35

question was seen: 1,672 times

last updated: 11 Jul '19, 08:32

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum