How to apply spatial index in my situation without using 'or' and 'union all'

I've been refactoring using the spatial index at Haversine,

I wanted to write a sql statement that checks posts within 10km with the point value (0,90) for information without distance, but I confirmed that index is not used if 'or' is used.

So I tried combining the two SELECT queries using UNION ALL, but the performance was just as slow as before, making it ineffective. I tested this with 50,000 rows of data, and there was no noticeable improvement — even though the spatial index was used properly in this case.

As a last resort, I thought about separating the posts without distance information and the posts with distance information to separate the API, but the UI has changed, and now the front is unable to refact, so I want to exclude it as much as possible. Thank you🙏

select b1_0.id,
   b1_0.title,
   b1_0.item_time,
   b1_0.item_price,
   b1_0.created_date,
   b1_0.chat_count,
   b1_0.scrap_count,
   st_distance_sphere(b1_0.location, ST_GeomFromText('POINT(127.0 37.5)', 4326)),
   b1_0.address,
   b1_0.board_state,
   (select i1_0.stored_file_name
    from image i1_0
    where i1_0.board_id = b1_0.id
    order by i1_0.id LIMIT 1)
from board b1_0
where b1_0.board_type = 'SELL'
  and (ST_Contains(ST_Buffer(ST_GeomFromText('POINT(127.0 37.5)', 4326), 0.1), b1_0.location)
           OR (latitude = 90.0 AND longitude = 0.0))
order by b1_0.created_date desc
limit 0, 8;

Solution

One trick you could use here would be to remove the OR in the WHERE clause and instead express your query as a union as follows:

select
    b1_0.id,
    b1_0.title,
    b1_0.item_time,
    b1_0.item_price,
    b1_0.created_date,
    b1_0.chat_count,
    b1_0.scrap_count,
    st_distance_sphere(b1_0.location, ST_GeomFromText('POINT(127.0 37.5)', 4326)),
    b1_0.address,
    b1_0.board_state,
    (select i1_0.stored_file_name
     from image i1_0
     where i1_0.board_id = b1_0.id
     order by i1_0.id)
from board b1_0
where b1_0.board_type = 'SELL'
    and (ST_Contains(ST_Buffer(ST_GeomFromText('POINT(127.0 37.5)', 4326), 0.1), b1_0.location)
union all
select
    b1_0.id,
    b1_0.title,
    b1_0.item_time,
    b1_0.item_price,
    b1_0.created_date,
    b1_0.chat_count,
    b1_0.scrap_count,
    st_distance_sphere(b1_0.location, ST_GeomFromText('POINT(127.0 37.5)', 4326)),
    b1_0.address,
    b1_0.board_state,
    (select i1_0.stored_file_name
     from image i1_0
     where i1_0.board_id = b1_0.id
     order by i1_0.id)
from board b1_0
where b1_0.board_type = 'SELL' and
      latitude = 90.0 and
      longitude = 0.0
order by created_date desc
limit 0, 8;

To optimize the above union query, we can creating an indexing strategy for both halves of the union.

-- for the top query

CREATE INDEX idx1 ON board (board_type, location);

-- for the bottom query

CREATE INDEX idx2 ON board (board_type, latitude, longitude);