By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE points (gid serial, geom geometry(point,4326));
INSERT INTO points (geom) VALUES
(ST_SetSRID(ST_Point(110.18, -7.96),4326)), -- intersects
(ST_SetSRID(ST_Point(110.181, -7.961),4326)), -- intersects
(ST_SetSRID(ST_Point(44.42, 8.42),4326)); -- does not intersect
CREATE TABLE zona_bahaya (geom geometry(polygon,4326));
INSERT INTO zona_bahaya VALUES ('SRID=4326;POLYGON((110.16558131450346 -7.951060382538983,110.19570788615873 -7.951060382538983,110.19570788615873 -7.9660210885201845,110.16558131450346 -7.9660210885201845,110.16558131450346 -7.951060382538983))'::geometry);
3 rows affected
1 rows affected
SELECT *
FROM zona_bahaya
JOIN points ON ST_Intersects(points.geom,zona_bahaya.geom)
ORDER BY gid
FETCH FIRST ROW ONLY;
| geom | gid | geom |
|---|---|---|
| 0103000020E61000000100000005000000BFA75EE2988A5B40CF1ADEC5E2CD1FC0BFA75E7A868C5B40CF1ADEC5E2CD1FC0BFA75E7A868C5B4016C3D9A134DD1FC0BFA75EE2988A5B4016C3D9A134DD1FC0BFA75EE2988A5B40CF1ADEC5E2CD1FC0 | 1 | 0101000020E6100000EC51B81E858B5B40D7A3703D0AD71FC0 |
SELECT *
FROM zona_bahaya
JOIN points ON ST_Intersects(points.geom,zona_bahaya.geom)
| geom | gid | geom |
|---|---|---|
| 0103000020E61000000100000005000000BFA75EE2988A5B40CF1ADEC5E2CD1FC0BFA75E7A868C5B40CF1ADEC5E2CD1FC0BFA75E7A868C5B4016C3D9A134DD1FC0BFA75EE2988A5B4016C3D9A134DD1FC0BFA75EE2988A5B40CF1ADEC5E2CD1FC0 | 1 | 0101000020E6100000EC51B81E858B5B40D7A3703D0AD71FC0 |
| 0103000020E61000000100000005000000BFA75EE2988A5B40CF1ADEC5E2CD1FC0BFA75E7A868C5B40CF1ADEC5E2CD1FC0BFA75E7A868C5B4016C3D9A134DD1FC0BFA75EE2988A5B4016C3D9A134DD1FC0BFA75EE2988A5B40CF1ADEC5E2CD1FC0 | 2 | 0101000020E6100000DD240681958B5B40F2D24D6210D81FC0 |