Skip to content

improve search perf on large DB

setop requested to merge setop/mobilizon:fix-search-perf into main

this MR adds a coarse grain rectangular filter to addresses, before the more accurate ST_DWithin filter.

the SQL query generated by Ecto becomes:

SELECT ... FROM "events" 
INNER JOIN "addresses" AS a1 ON a1."id" = e0."physical_address_id"
...
WHERE ...
 -- this fast one is added :
 AND (((((ST_X(a1."geom") > $2) AND (ST_X(a1."geom") < $3)) AND (ST_Y(a1."geom") > $4)) AND (ST_Y(a1."geom") < $5))
 -- to reduce dataset of this expensive one :
 AND ST_DWithin($6::geography, a1."geom"::geography, $7)) AND (e0."draft" = $8)
...

coupled with indexes :

create index idx_addresses_geom_x on addresses(st_x(geom));
create index idx_addresses_geom_y on addresses(st_y(geom));

it improve search speed by 2x to 5x faster on a db containing 200k addresses.

should fix #1047 (closed)

all tests of test/mobilizon/events/events_test.exs are passing.

Edited by setop

Merge request reports