PostgreSQL Geo Spatial Query is slow
First, use EXPLAIN ANALYZE (not mere EXPLAIN) and show the result of \d on the table.
(psql). As a first point this,

ST_GeographyFromText('POINT(47.4667 8.3167)')


should be written as ST_MakePoint(47.4667, 8.3167)::geography

Your problem here is this pattern,

SELECT ST_Distance( ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2) AS dist
...
ORDER  BY dist
LIMIT 100 OFFSET 1000;


Every time you do that, you must at least calculate the distance to 1100 rows. That said, it shouldn't be slow. It's slow because in order to that like that you're having to calculate the ST_Distance on all rows. We can stop that using KNN there using the &lt;-&gt; operator. MySQL doesn't support KNN.

SELECT hash_id, town
     , ST_Distance(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2) AS dist
FROM   member_profile
WHERE  ST_DWithin(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2, 250000)
ORDER  BY ST_MakePoint(47.4667, 8.3167)::geography &lt;-&gt; geo_pt2
OFFSET 1000
FETCH NEXT 100 ROWS ONLY;


As a style critique, I personally prefer OFFSET/FETCH (the standardized method limit/offset).

Pagination

I'm not sure this will work. but, it may be worth trying (keep us updated).

SELECT hash_id, town
     , ST_Distance(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2) AS dist
     , ST_MakePoint(47.4667, 8.3167)::geography &lt;-&gt; geo_pt2 AS myknn
FROM member_profile
WHERE ST_DWithin(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2, 250000)
  AND ST_MakePoint(47.4667, 8.3167)::geography &lt;-&gt; geo_pt2 &gt; OLD_VALUE
ORDER BY ST_MakePoint(47.4667, 8.3167)::geography &lt;-&gt; geo_pt2
FETCH NEXT 100 ROWS ONLY;


So the first time you run this you save the value of last value of myknn then the second time you run this, you can replay that value back in this clause as OLD_VALUE,

AND ST_MakePoint(47.4667, 8.3167)::geography &lt;-&gt; geo_pt2 &gt; OLD_VALUE


So each time you run it you're saving the new point from which to continue at, and using FETCH NEXT x ROWS ONLY.

myknn and dist may be the same for you, if so you can just remove one of them.