Postgres 14

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?.

select version();
select postgis_full_version();

-- drop table building

create table building(
    id serial4 not null,
    latitude numeric(9, 7) null,
    longitude numeric(9, 7) null,
    constraint building_pkey primary key (id)
);

-- drop table customer

create table customer(
    id serial4 not null,
    building_id int4 not null,
    constraint customer_pkey primary key (id),
    constraint customer_building_building_id_fkey foreign key (building_id) references building(id)
);

insert into building(latitude, longitude)
select
    random() + 32.2772270,
    random() - 97.2971710
from
    generate_series(1, 16000);

insert into customer (building_id)
select 
    gs1 as building_id
from
    generate_series(1, 16000) gs1;

-- This query works as expected and takes 60ms returning 6K rows
explain analyze select b.*
from building b
where
    st_dwithin(
        st_makepoint(b.longitude, b.latitude)::geography,
        st_makepoint(-96.7804060, 33.2471770)::geography,
        50000
    );

-- This query is an order of magnitude slower and takes 3000 ms
explain analyze select b.*
from building b
join customer c
    on c.building_id = b.id
where
    st_dwithin(
        st_makepoint(b.longitude, b.latitude)::geography,
        st_makepoint(-96.7804060, 33.2471770)::geography,
        50000);

-- This query is fast again and takes 60ms
explain analyze select b.*
from building b
join customer c
    on c.building_id = b.id
where
    case st_dwithin(
        st_makepoint(b.longitude, b.latitude)::geography,
        st_makepoint(-96.7804060, 33.2471770)::geography,
        50000)
        when true then 1
        else 0
    end = 1;

version
PostgreSQL 14.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit

SELECT 1

postgis_full_version
POSTGIS="3.2.5 e9106e2" [EXTENSION] PGSQL="140" GEOS="3.12.0-CAPI-1.18.0" PROJ="9.2.1" LIBXML="2.9.7" LIBJSON="0.13.1" LIBPROTOBUF="1.3.0" WAGYU="0.5.0 (Internal)" (core procs from "3.2.1 5fae8e5" need upgrade)

SELECT 1

CREATE TABLE

CREATE TABLE

INSERT 0 16000

INSERT 0 16000

QUERY PLAN
Seq Scan on building b (cost=0.00..348878.76 rows=1 width=32) (actual time=29.283..115.460 rows=6357 loops=1)
Filter: st_dwithin((st_makepoint((longitude)::double precision, (latitude)::double precision))::geography, '0101000020E61000008AE6012CF23158C065A9F57EA39F4040'::geography, '50000'::double precision, true)
Rows Removed by Filter: 9643
Planning Time: 0.253 ms
Execution Time: 116.281 ms

EXPLAIN

QUERY PLAN
Nested Loop (cost=0.00..349310.80 rows=1 width=32) (actual time=0.062..17271.976 rows=6357 loops=1)
Join Filter: (b.id = c.building_id)
Rows Removed by Join Filter: 101705643
-> Seq Scan on building b (cost=0.00..348878.76 rows=1 width=32) (actual time=0.048..162.060 rows=6357 loops=1)
Filter: st_dwithin((st_makepoint((longitude)::double precision, (latitude)::double precision))::geography, '0101000020E61000008AE6012CF23158C065A9F57EA39F4040'::geography, '50000'::double precision, true)
Rows Removed by Filter: 9643
-> Seq Scan on customer c (cost=0.00..231.46 rows=16046 width=4) (actual time=0.008..1.457 rows=16000 loops=6357)
Planning Time: 0.374 ms
Execution Time: 17273.936 ms

EXPLAIN

QUERY PLAN
Hash Join (cost=432.04..349484.88 rows=80 width=32) (actual time=8.405..87.510 rows=6357 loops=1)
Hash Cond: (b.id = c.building_id)
-> Seq Scan on building b (cost=0.00..348913.44 rows=69 width=32) (actual time=0.038..75.705 rows=6357 loops=1)
Filter: (CASE st_dwithin((st_makepoint((longitude)::double precision, (latitude)::double precision))::geography, '0101000020E61000008AE6012CF23158C065A9F57EA39F4040'::geography, '50000'::double precision, true) WHEN CASE_TEST_EXPR THEN 1 ELSE 0 END = 1)
Rows Removed by Filter: 9643
-> Hash (cost=231.46..231.46 rows=16046 width=4) (actual time=8.345..8.346 rows=16000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 691kB
-> Seq Scan on customer c (cost=0.00..231.46 rows=16046 width=4) (actual time=0.011..2.021 rows=16000 loops=1)
Planning Time: 0.177 ms
Execution Time: 88.105 ms

EXPLAIN