Such an innocent looking query... I didn't expect it to do this!

One table has 500 rows, the other has 381k. Table stats are accurate. The planner still decides it will return 39M rows, roughly 20% of what a cartesian join. The screenshot is from pgMustard, plan here: https://app.pgmustard.com/#/explore/486276b3-d9e5-4bc8-a1eb-a3a23cd1caf4?highlighted=0

SELECT *
FROM store_potential_customers p
INNER JOIN osm.road_line r
ON r.route_motor
WHERE p.geom <-> r.geom < 100
;

@michristofides
#postgis #postgresql #postgres #performance