Postgres 13

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 TABLE tbl (
  id int 
, longitude numeric(9,6)
, latitude  numeric(9,6)
);

INSERT INTO tbl VALUES
  ( 1, 9.876543, 1.234568)
, ( 2, 9.876543, 1.234567) 
, ( 3, 9.876542, 1.234568)
, ( 4, 9.876542, 1.234567)
, ( 5, 9.876541, 1.234569)
, ( 6, 9.876540, 1.234560)
, ( 7, 9.876500, 1.234500)
, ( 8, 9.876000, 1.234000)
, ( 9, 9.870000, 1.230000)
, (10, 9.800000, 1.200000)
, (11, 9.876543, 1.234567)
, (12, 9.900000, 1.300000)
;

CREATE INDEX tbl_location_spgist_idx ON tbl USING SPGIST (point(longitude, latitude));

TABLE tbl;

CREATE TABLE



INSERT 0 12



CREATE INDEX




id
longitude
latitude


1
9.876543
1.234568

2
9.876543
1.234567

3
9.876542
1.234568

4
9.876542
1.234567

5
9.876541
1.234569

6
9.876540
1.234560

7
9.876500
1.234500

8
9.876000
1.234000

9
9.870000
1.230000

10
9.800000
1.200000

11
9.876543
1.234567

12
9.900000
1.300000



SELECT 12


-- nearest 5
SELECT *
FROM   tbl
ORDER  BY point (longitude, latitude)
      <-> point '(9.876543210987654321,1.234567890123456789)'
LIMIT  10;


id
longitude
latitude


1
9.876543
1.234568

2
9.876543
1.234567

11
9.876543
1.234567

3
9.876542
1.234568

4
9.876542
1.234567

5
9.876541
1.234569

6
9.876540
1.234560

7
9.876500
1.234500

8
9.876000
1.234000

9
9.870000
1.230000



SELECT 10


-- max nearest 5 that are within bound
SELECT *, ((longitude - 9.876543210987654321) ^ 2
        + (latitude   - 1.234567890123456789) ^ 2) AS squared_dist
FROM   tbl
WHERE  ((longitude - 9.876543210987654321) ^ 2
      + (latitude  - 1.234567890123456789) ^ 2) < 0.000000001  -- or whatever
ORDER  BY point (longitude, latitude)
      <-> point '(9.876543210987654321,1.234567890123456789)'
LIMIT  10;


id
longitude
latitude
squared_dist


1
9.876543
1.234568
0.000000000000056589

11
9.876543
1.234567
0.000000000000836836

2
9.876543
1.234567
0.000000000000836836

3
9.876542
1.234568
0.000000000001478564

4
9.876542
1.234567
0.000000000002258811

5
9.876541
1.234569
0.000000000006120292

6
9.876540
1.234560
0.000000000072564490



SELECT 7


SET enable_seqscan = off;  -- to force index with mini tbl
EXPLAIN
SELECT *, ((longitude - 9.876543210987654321) ^2
      + (latitude  - 1.234567890123456789) ^2) AS squared_dist
FROM   tbl
WHERE  ((longitude - 9.876543210987654321) ^2
      + (latitude  - 1.234567890123456789) ^2) < 0.000000001  -- or whatever
ORDER  BY point (longitude, latitude)
      <-> point '(9.876543210987654321,1.234567890123456789)'
LIMIT  10;

SET




QUERY PLAN


Limit  (cost=0.13..16.61 rows=4 width=72)

  ->  Index Scan using tbl_location_spgist_idx on tbl  (cost=0.13..16.61 rows=4 width=72)

        Order By: (point((longitude)::double precision, (latitude)::double precision) <-> '(9.876543210987654,1.2345678901234567)'::point)

        Filter: ((((longitude - 9.876543210987654321) ^ '2'::numeric) + ((latitude - 1.234567890123456789) ^ '2'::numeric)) < 0.000000001)



EXPLAIN

id	longitude	latitude
1	9.876543	1.234568
2	9.876543	1.234567
3	9.876542	1.234568
4	9.876542	1.234567
5	9.876541	1.234569
6	9.876540	1.234560
7	9.876500	1.234500
8	9.876000	1.234000
9	9.870000	1.230000
10	9.800000	1.200000
11	9.876543	1.234567
12	9.900000	1.300000

id	longitude	latitude	squared_dist
1	9.876543	1.234568	0.000000000000056589
11	9.876543	1.234567	0.000000000000836836
2	9.876543	1.234567	0.000000000000836836
3	9.876542	1.234568	0.000000000001478564
4	9.876542	1.234567	0.000000000002258811
5	9.876541	1.234569	0.000000000006120292
6	9.876540	1.234560	0.000000000072564490