add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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 extension postgis;

create table business(
id int generated by default as identity primary key,
business_name text,
x numeric,
y numeric,
geom geometry(Point,4326) generated always as
(st_setsrid(st_makepoint(x,y),4326)) stored,
geohash text generated always as
(st_geohash(st_setsrid(st_makepoint(x,y),4326))) stored);

select setseed(.2);
insert into business (business_name,x,y)
select 'business_'||n,
x,
y
from generate_series(1,1e5,1)a(n),
lateral(select n,random()*180 x,90-random()*180 y)b(n1,x,y);

create index geom_idx on business using gist(geom);

select * from business limit 5;
CREATE EXTENSION

CREATE TABLE
setseed 
SELECT 1

INSERT 0 100000

CREATE INDEX
id business_name x y geom geohash
1 business_1 164.198762706876 -60.6093960164277 0101000020E61000004DB69B435C866440516F4CB0004E4EC0 pthz0qmusmxtb4tk0gn4
2 business_2 135.922177684985 -45.6027176727663 0101000020E610000092C3C67A82FD604052A04ADA25CD46C0 ppbssfr8bm3909hypcm9
3 business_3 146.157254340583 -8.15575344776737 0101000020E6100000A83E413A08456240DACC78EABE4F20C0 rnxctgk3qc7gdkg9p0zr
4 business_4 90.7522998666652 -3.82861139384553 0101000020E61000002E0757AE25B05640B646AD02FFA00EC0 qp2d1p5b9z57qjbxp3ux
5 business_5 128.173390874322 -9.1418396817286 0101000020E610000069D4046B8C0560404999F4359F4822C0 qy75uxv0bmx61kw8wheb 
SELECT 5
vacuum analyze business;
VACUUM
prepare postgis_based as
select id,business_name,x,y,geohash
,(select geom from business where id=42)<->geom distance
from business
order by distance
limit 5;

explain analyze execute postgis_based;
execute postgis_based;
PREPARE
QUERY PLAN
Limit (cost=8.59..12.21 rows=5 width=71) (actual time=0.161..0.177 rows=5 loops=1)
  InitPlan 1 (returns $0)
    -> Index Scan using business_pkey on business business_1 (cost=0.29..8.31 rows=1 width=32) (actual time=0.008..0.009 rows=1 loops=1)
          Index Cond: (id = 42)
  -> Index Scan using geom_idx on business (cost=0.28..72434.28 rows=100000 width=71) (actual time=0.160..0.174 rows=5 loops=1)
        Order By: (geom <-> $0)
Planning Time: 0.312 ms
Execution Time: 0.241 ms 
EXPLAIN
id business_name x y geohash distance
42 business_42 164.28489159467 -45.2105222171467 pxuyf3ste7vf53zk17fj 0
3417 business_3417 164.0246591859 -44.9101408376368 r8h8s2r57wfwb34cdcyk 0.3974290876754137
6309 business_6309 164.818000468193 -45.2836125675001 pxvnqs2gcfb9hsmg5tru 0.5380959675966127
83713 business_83713 164.855609921369 -45.0914942385985 pxvprrk5n0hr95qn5u8w 0.5829983431429043
8656 business_8656 164.605777003552 -45.7474173859692 pxv59xymbx73es431s2v 0.6254789108661462 
SELECT 5
create extension pg_trgm ;
create index on business using gist (geohash gist_trgm_ops);
CREATE EXTENSION

CREATE INDEX
vacuum analyze business;
VACUUM
prepare geohash_based as
select id,business_name,x,y,geohash
,(select geohash from business where id=42)<->geohash distance
from business
order by distance
limit 5;

explain analyze execute geohash_based;
execute geohash_based;
PREPARE
QUERY PLAN
Limit (cost=8.59..9.32 rows=5 width=67) (actual time=2.129..35.471 rows=5 loops=1)
  InitPlan 1 (returns $0)
    -> Index Scan using business_pkey on business business_1 (cost=0.29..8.31 rows=1 width=21) (actual time=0.007..0.008 rows=1 loops=1)
          Index Cond: (id = 42)
  -> Index Scan using business_geohash_idx on business (cost=0.28..14624.28 rows=100000 width=67) (actual time=2.128..35.467 rows=5 loops=1)
        Order By: (geohash <-> $0)
Planning Time: 0.227 ms
Execution Time: 35.548 ms 
EXPLAIN
id business_name x y geohash distance
42 business_42 164.28489159467 -45.2105222171467 pxuyf3ste7vf53zk17fj 0
27178 business_27178 163.55869981402 -45.1986585985445 pxuqcggf9zq529sgkqtw 0.9230769
84095 business_84095 168.108089046286 -66.6049916056051 pspt16utf3st0111wbzb 0.9230769
45989 business_45989 163.18008358432 -45.2833096893293 pxun3k2jee1rcebxd1yt 0.9230769
63097 business_63097 163.400676754028 -45.0472671309063 pxupwr2sxsz2xdd72fj7 0.9230769 
SELECT 5
prepare postgis_based2 as
select id,business_name,x,y,geohash
from business
where st_dwithin((select geom from business where id=42),geom,1)
limit 5;

explain analyze execute postgis_based2;
execute postgis_based2;
PREPARE
QUERY PLAN
Limit (cost=8.71..93.30 rows=5 width=63) (actual time=0.114..0.121 rows=5 loops=1)
  InitPlan 1 (returns $0)
    -> Index Scan using business_pkey on business business_1 (cost=0.29..8.31 rows=1 width=32) (actual time=0.007..0.008 rows=1 loops=1)
          Index Cond: (id = 42)
  -> Index Scan using geom_idx on business (cost=0.41..169.58 rows=10 width=63) (actual time=0.113..0.119 rows=5 loops=1)
        Index Cond: (geom && st_expand($0, '1'::double precision))
        Filter: st_dwithin($0, geom, '1'::double precision)
        Rows Removed by Filter: 1
Planning Time: 0.368 ms
Execution Time: 0.187 ms 
EXPLAIN
id business_name x y geohash
22307 business_22307 165.213429396186 -45.3420063495878 pxvqp98xxvmudmj97vkf
83713 business_83713 164.855609921369 -45.0914942385985 pxvprrk5n0hr95qn5u8w
93720 business_93720 164.990241156698 -45.0564514554435 pxvrdmwppk8tgw7n39vb
42 business_42 164.28489159467 -45.2105222171467 pxuyf3ste7vf53zk17fj
6309 business_6309 164.818000468193 -45.2836125675001 pxvnqs2gcfb9hsmg5tru 
SELECT 5
prepare geohash_based2 as
select id,business_name,x,y,geohash
from business
where geohash ^@ (select left(geohash,2) from business where id=42)
limit 5;

explain analyze execute geohash_based2;
execute geohash_based2;
PREPARE
QUERY PLAN
Limit (cost=8.31..36.20 rows=5 width=63) (actual time=0.032..0.268 rows=5 loops=1)
  InitPlan 1 (returns $0)
    -> Index Scan using business_pkey on business business_1 (cost=0.29..8.31 rows=1 width=32) (actual time=0.012..0.013 rows=1 loops=1)
          Index Cond: (id = 42)
  -> Seq Scan on business (cost=0.00..2789.00 rows=500 width=63) (actual time=0.032..0.266 rows=5 loops=1)
        Filter: (geohash ^@ $0)
        Rows Removed by Filter: 982
Planning Time: 0.110 ms
Execution Time: 0.283 ms 
EXPLAIN
id business_name x y geohash
42 business_42 164.28489159467 -45.2105222171467 pxuyf3ste7vf53zk17fj
169 business_169 165.382741038554 -47.1873159689498 pxtee3u1cnv8p866kqhh
431 business_431 163.974226091001 -47.4297871644213 pxsd5mdmkqufgzdm2tfe
555 business_555 163.248698839505 -50.3743565450652 pxh16vdh47kd3q2cvdpd
987 business_987 157.535974430581 -46.4991816873455 px8p2z44wk22zke1jqpm 
SELECT 5