improve search perf on large DB
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