Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t (geom geometry(point,4326));
INSERT INTO t 
SELECT ('SRID=4326;POINT('||floor(random() * 50)||' ' ||floor(random() * 50) ||')')
FROM generate_series(1,50000);

CREATE TABLE

INSERT 0 50000

-- Without gist index
EXPLAIN ANALYSE
SELECT * FROM t
WHERE ST_DWithin('SRID=4326;POINT(1 1)',geom,1);

QUERY PLAN

Seq Scan on t  (cost=0.00..1252068.48 rows=5 width=32) (actual time=0.586..32.248 rows=100 loops=1)

  Filter: st_dwithin('0101000020E6100000000000000000F03F000000000000F03F'::geometry, geom, '1'::double precision)

  Rows Removed by Filter: 49900

Planning Time: 2.225 ms

Execution Time: 32.315 ms

EXPLAIN

CREATE INDEX idx_t_geom ON t USING gist (geom);

CREATE INDEX

-- With gist index 
EXPLAIN ANALYSE
SELECT * FROM t
WHERE ST_DWithin('SRID=4326;POINT(1 1)',geom,1);

QUERY PLAN

Bitmap Heap Scan on t  (cost=4.44..147.74 rows=5 width=32) (actual time=0.814..1.107 rows=100 loops=1)

  Filter: st_dwithin('0101000020E6100000000000000000F03F000000000000F03F'::geometry, geom, '1'::double precision)

  Rows Removed by Filter: 85

  Heap Blocks: exact=147

  ->  Bitmap Index Scan on idx_t_geom  (cost=0.00..4.44 rows=5 width=0) (actual time=0.638..0.639 rows=185 loops=1)

        Index Cond: (geom && st_expand('0101000020E6100000000000000000F03F000000000000F03F'::geometry, '1'::double precision))

Planning Time: 1.358 ms

Execution Time: 1.593 ms

EXPLAIN