ST_Contains

true when b is entirely inside a and their interiors overlap. that second clause is the surprise: a point sitting exactly on the border is not contained.

SELECT n.id, n.name
FROM   neighbourhoods n
WHERE  ST_Contains(n.geom, :point);

the boundary rule

SELECT ST_Contains(poly, pt) AS contains,
       ST_Covers(poly, pt)   AS covers
FROM (
  SELECT 'POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'::geometry AS poly,
         'POINT(0 5)'::geometry                             AS pt
) t;

 contains | covers
----------+--------
 f        | t

the point is on the western edge. ST_Contains says no, ST_Covers says yes.

for "which region does this address belong to", ST_Covers is the predicate that matches what you meant. it is also marginally cheaper, because it skips the interior test. ST_Contains is for when you genuinely need the border excluded, which is rarer than the function's name suggests.

a related consequence: a polygon contains itself, but it does not contain its own boundary linestring.

argument order

ST_Contains(region, point) and ST_Within(point, region) mean the same thing. swap the arguments and you do not get an error, you get false. that is the second most common bug here and it looks like missing data, not like a mistake.

predicate	boundary counts	use for
`ST_Contains(a, b)`	no	strict interior containment
`ST_Covers(a, b)`	yes	point in region lookups
`ST_Within(a, b)`	no	`ST_Contains` with the arguments flipped
`ST_Intersects(a, b)`	yes	any shared point at all
`ST_Disjoint(a, b)`	n/a	the negation, and it is not index-assisted

how it reaches the index

ST_Contains is index-assisted. it expands into a bounding box test followed by the exact predicate:

EXPLAIN ANALYZE
SELECT id FROM neighbourhoods WHERE ST_Contains(geom, :point);

Index Scan using neighbourhoods_geom_idx on neighbourhoods
  Index Cond: (geom ~ :point)
  Filter: st_contains(geom, :point)

~ is "bounding box contains". the index prunes by box, the exact test runs only on what survives. that is why containment against large polygons stays fast, and why it degrades when the polygons are detailed enough that the exact test dominates. the gist index covers what to do about it.

ST_Disjoint is the exception. there is no bounding box shortcut for "shares no point", so it always scans the table. write NOT ST_Intersects(a, b) instead and you keep the index.

gotchas

invalid polygons give undefined results. check with ST_IsValid, repair with ST_MakeValid. self intersecting rings are common in imported shapefiles and nothing warns you.
there is no ST_Contains for geography. cast to geometry, or use ST_Intersects, which does have a geography implementation.
overlapping regions return more than one row. if your model assumes a point belongs to exactly one polygon, enforce that in the data, not in the query.