Postgres 16

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 docs01 (id SERIAL, doc TEXT, PRIMARY KEY(id));

CREATE TABLE invert01 (
  keyword TEXT,
  doc_id INTEGER REFERENCES docs01(id) ON DELETE CASCADE
);

INSERT INTO docs01 (doc) VALUES
('The building blocks of programs'),
('In the next few chapters we will learn more about the vocabulary'),
('sentence structure paragraph structure and story structure of Python'),
('We will learn about the powerful capabilities of Python and how to'),
('compose those capabilities together to create useful programs'),
('There are some lowlevel conceptual patterns that we use to construct'),
('programs These constructs are not just for Python programs they are'),
('part of every programming language from machine language up to the'),
('file or even some kind of sensor like a microphone or GPS In our'),
('initial programs our input will come from the user typing data on');

CREATE TABLE

CREATE TABLE

INSERT 0 10

begin transaction;

INSERT INTO invert01 (keyword, doc_id)
SELECT
    lower(keyword) AS keyword,
    doc_id
FROM (
    SELECT
        id AS doc_id,
        unnest(string_to_array(doc, ' ')) AS keyword
    FROM docs01
) AS words;

SELECT keyword, doc_id FROM invert01 WHERE keyword='are' 
ORDER BY keyword, doc_id LIMIT 10;

rollback;

BEGIN

INSERT 0 105

keyword
doc_id

are
6

are
7

are
7

SELECT 3

ROLLBACK

INSERT INTO invert01 (keyword, doc_id)
SELECT DISTINCT
    lower(string_to_table(doc, ' ')) AS keyword,
    id AS doc_id
FROM docs01;

SELECT keyword, doc_id FROM invert01 WHERE keyword='are' 
ORDER BY keyword, doc_id LIMIT 10;

INSERT 0 98

keyword
doc_id

are
6

are
7

SELECT 2