Postgres 12

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 "Room" (
 gid int,
 "polygon" text
);

INSERT INTO "Room" VALUES 
(1,'LINESTRING (30 10, 10 30, 40 40)'), -- RING OPEN!
(2,'POINT (30 10)'), -- NOT A LINESTRING!
(3,'LINESTRING (30 10, 10 30, 40 40, 30 10)');

3 rows affected

ALTER TABLE "Room" 
  ADD COLUMN geolocation geography(Polygon, 4326) 
  GENERATED ALWAYS AS (
    CASE 
      WHEN ST_IsValid("polygon") AND 
	   GeometryType("polygon")='LINESTRING' AND
	   ST_IsClosed("polygon"::geometry) THEN   
        ST_MakePolygon("polygon"::geometry)
      ELSE 
        'POLYGON EMPTY' 
      END) STORED;
SELECT * FROM "Room";


gid
polygon
geolocation


1
LINESTRING (30 10, 10 30, 40 40)
0103000020E610000000000000

2
POINT (30 10)
0103000020E610000000000000

3
LINESTRING (30 10, 10 30, 40 40, 30 10)
0103000020E610000001000000040000000000000000003E40000000000000244000000000000024400000000000003E40000000000000444000000000000044400000000000003E400000000000002440

gid	polygon	geolocation
1	LINESTRING (30 10, 10 30, 40 40)	0103000020E610000000000000
2	POINT (30 10)	0103000020E610000000000000
3	LINESTRING (30 10, 10 30, 40 40, 30 10)	0103000020E610000001000000040000000000000000003E40000000000000244000000000000024400000000000003E40000000000000444000000000000044400000000000003E400000000000002440