CREATE TABLE "public"."geo" (
  "id" int8 NOT NULL DEFAULT nextval('geo_id_seq'::regclass),
  "parent_id" int8,
  "name" varchar(1000) COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Indexes structure for table geo
-- ----------------------------
CREATE INDEX "pkParent" ON "public"."geo" USING btree (
  "parent_id" "pg_catalog"."int8_ops" ASC NULLS LAST
);

-- ----------------------------
-- Primary Key structure for table geo
-- ----------------------------
ALTER TABLE "public"."geo" ADD CONSTRAINT "geo_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Foreign Keys structure for table geo
-- ----------------------------
ALTER TABLE "public"."geo" ADD CONSTRAINT "geo_parent_id_fkey" FOREIGN KEY ("parent_id") REFERENCES "public"."geo" ("id") ON DELETE CASCADE ON UPDATE NO ACTION;

select geo.* from geo LEFT JOIN geo as p ON geo.id = p.parent_id where p.id ISNULL; 

Limit  (cost=320462.73..792817.22 rows=1 width=49) (actual time=10291.636..12699.384 rows=100 loops=1)
  ->  Gather  (cost=320462.73..792817.22 rows=1 width=49) (actual time=10291.632..12699.371 rows=100 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Parallel Hash Left Join  (cost=319462.73..791817.12 rows=1 width=49) (actual time=10251.338..10251.521 rows=34 loops=3)
              Hash Cond: (geo.id = p.parent_id)
              Filter: (p.id IS NULL)
              Rows Removed by Filter: 53
              ->  Parallel Seq Scan on geo  (cost=0.00..212933.33 rows=6128432 width=49) (actual time=0.019..773.833 rows=4902746 loops=3)
              ->  Parallel Hash  (cost=212933.33..212933.33 rows=6128432 width=16) (actual time=6822.487..6822.487 rows=4902746 loops=3)
                    Buckets: 131072  Batches: 256  Memory Usage: 3808kB
                    ->  Parallel Seq Scan on geo p  (cost=0.00..212933.33 rows=6128432 width=16) (actual time=0.021..5358.471 rows=4902746 loops=3)
Planning Time: 0.636 ms
Execution Time: 12699.580 ms

SELECT   geo.* from geo LEFT JOIN geo as p ON geo.id = p.parent_id where p.id IS NULL ORDER BY geo.name;

set track_io_timing to on;
explain (analyze, costs, buffers, timing) select geo.* from geo where not exists (select from geo as p where geo.id = p.parent_id); 

Merge Anti Join  (cost=8.86..1288015.58 rows=13994306 width=49) (actual time=0.075..40279.078 rows=11768450 loops=1)
  Merge Cond: (geo.id = p.parent_id)
  Buffers: shared hit=718468 read=219043
  I/O Timings: read=29869.283
  ->  Index Scan using geo_pkey on geo  (cost=0.43..533612.01 rows=14708238 width=49) (actual time=0.008..2733.825 rows=14708238 loops=1)
        Buffers: shared hit=151637 read=40188
        I/O Timings: read=576.221
  ->  Index Only Scan using "pkParent" on geo p  (cost=0.43..533780.01 rows=14708238 width=8) (actual time=0.058..33983.615 rows=14708231 loops=1)
        Heap Fetches: 14708231
        Buffers: shared hit=566831 read=178855
        I/O Timings: read=29293.062
Planning Time: 0.936 ms
Execution Time: 40668.958 ms

Limit  (cost=8.86..18.06 rows=100 width=49) (actual time=0.024..0.117 rows=100 loops=1)
  Buffers: shared hit=26
  ->  Merge Anti Join  (cost=8.86..1288015.58 rows=13994306 width=49) (actual time=0.023..0.108 rows=100 loops=1)
        Merge Cond: (geo.id = p.parent_id)
        Buffers: shared hit=26
        ->  Index Scan using geo_pkey on geo  (cost=0.43..533612.01 rows=14708238 width=49) (actual time=0.009..0.024 rows=123 loops=1)
              Buffers: shared hit=7
        ->  Index Only Scan using "pkParent" on geo p  (cost=0.43..533780.01 rows=14708238 width=8) (actual time=0.008..0.052 rows=137 loops=1)
              Heap Fetches: 137
              Buffers: shared hit=19
Planning Time: 0.272 ms
Execution Time: 0.140 ms

Limit  (cost=0.87..52.70 rows=100 width=49) (actual time=0.027..0.446 rows=100 loops=1)
  Buffers: shared hit=395
  ->  Nested Loop Anti Join  (cost=0.87..7253021.03 rows=13994306 width=49) (actual time=0.025..0.434 rows=100 loops=1)
        Buffers: shared hit=395
        ->  Index Scan Backward using geo_pkey on geo  (cost=0.43..533612.01 rows=14708238 width=49) (actual time=0.013..0.057 rows=122 loops=1)
              Buffers: shared hit=7
        ->  Index Only Scan using "pkParent" on geo p  (cost=0.43..0.85 rows=21 width=8) (actual time=0.002..0.002 rows=0 loops=122)
              Index Cond: (parent_id = geo.id)
              Heap Fetches: 22
              Buffers: shared hit=388
Planning Time: 1.877 ms
Execution Time: 0.490 ms