Postgres 11

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();


version


PostgreSQL 11.5 (Debian 11.5-3.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit


SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name = 'postgis';


name
default_version
installed_version


postgis
2.5.2
2.5.2


DROP TABLE IF EXISTS tbl_table;

CREATE TABLE IF NOT EXISTS tbl_table (
  random_point GEOMETRY
);
INSERT INTO tbl_table
VALUES
  (ST_GeomFromText('POINT(-30 -31)', 4326)),
  (ST_GeomFromText('POINT(-32 -33)', 4326)),
  (ST_GeomFromText('POINT(-60 -61)', 4326)),
  (ST_GeomFromText('POINT(-62 -63)', 4326));
4 rows affected

SELECT
  ST_AsGeoJSON(random_point) geojson,
  ST_X(random_point) x,
  ST_Y(random_point) y
FROM tbl_table;


geojson
x
y


{"type":"Point","coordinates":[-30,-31]}
-30
-31

{"type":"Point","coordinates":[-32,-33]}
-32
-33

{"type":"Point","coordinates":[-60,-61]}
-60
-61

{"type":"Point","coordinates":[-62,-63]}
-62
-63

geojson	x	y
{"type":"Point","coordinates":[-30,-31]}	-30	-31
{"type":"Point","coordinates":[-32,-33]}	-32	-33
{"type":"Point","coordinates":[-60,-61]}	-60	-61
{"type":"Point","coordinates":[-62,-63]}	-62	-63