Postgres 9.6

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 9.6.24 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit



SELECT 1


SELECT NOW();


now


2023-12-06 08:57:52.195895+00



SELECT 1


CREATE TABLE source_table (
    id              INT,
    prompttype      VARCHAR(20),
    corpuscode      VARCHAR(2000),
    text            VARCHAR(2000),
    attributes      VARCHAR(2000),
    inputs          VARCHAR(2000),
    comment         VARCHAR(2000),
    created         TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    projectid       BIGINT,
    promptnum       INT,
    scriptid        INT,
    modified        TIMESTAMP WITHOUT TIME ZONE


);

INSERT INTO source_table VALUES (209687,'recording','24-Z-02-669-0001-012','<html><p>Please read the following prompt: </p><p><span style="color: #ff0000;"><strong><span style="font-size: 14pt;">के म जुलाई पन्ध्रमा बस्ने होटल जाँच गर्नुभएको छ ?</span></strong></span></p></html>','{"prompt": "के म जुलाई पन्ध्रमा बस्ने होटल जाँच गर्नुभएको छ ?", "skippable": false, "sampleRate": 16000, "audioMaxDuration": 60, "audioMinDuration": 4, "locationRequired": true, "maxLeadingSilence": 5, "minLeadingSilence": 0.6, "maxTrailingSilence": 5, "minTrailingSilence": 0.6}',null,'','2021-09-01 05:42:15.807260',224,73,10217,'2021-09-03 13:15:15.807260');

SELECT * FROM source_table WHERE prompttype ='input' ORDER BY created DESC LIMIT 10;

CREATE TABLE target_table (
    location_required       BOOLEAN,
    access_medialibrary     BOOLEAN,
    skippable               BOOLEAN,
    flashmode               BOOLEAN,
    other_attributes        VARCHAR(2000),
    custom_framerate        VARCHAR(2000),
    video_quality           VARCHAR(2000),
    default_camerafacing    VARCHAR(2000),
    prompt_values           VARCHAR(2000),
    subtitle                VARCHAR(2000),
    use_case                VARCHAR(2000),
    topic                   VARCHAR(2000),
    prompt_desc             VARCHAR(2000),
    prompt_name             VARCHAR(2000),
    prompt_source           VARCHAR(2000),
    prompt_type             VARCHAR(2000),
    max_recordings          BIGINT,
    min_recordings          BIGINT,
    script_id               BIGINT,
    with_transcription      BIGINT,
    video_maxduration       BIGINT,
    prompt_attribute_id     BIGSERIAL PRIMARY KEY,
    collect_project_id      BIGINT,
    edw_date_updated        TIMESTAMP WITHOUT TIME ZONE,
    edw_date_created        TIMESTAMP WITHOUT TIME ZONE,
    date_updated            TIMESTAMP WITHOUT TIME ZONE,
    date_created            TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),
    CONSTRAINT unique_dim_collect_prompt_attr_configs UNIQUE (collect_project_id, prompt_values)

);

CREATE TABLE


SELECT * FROM source_table;


id
prompttype
corpuscode
text
attributes
inputs
comment
created
projectid
promptnum
scriptid
modified


209687
recording
24-Z-02-669-0001-012
<html><p>Please read the following prompt: </p><p><span style="color: #ff0000;"><strong><span style="font-size: 14pt;">के म जुलाई पन्ध्रमा बस्ने होटल जाँच गर्नुभएको छ ?</span></strong></span></p></html>
{"prompt": "के म जुलाई पन्ध्रमा बस्ने होटल जाँच गर्नुभएको छ ?", "skippable": false, "sampleRate": 16000, "audioMaxDuration": 60, "audioMinDuration": 4, "locationRequired": true, "maxLeadingSilence": 5, "minLeadingSilence": 0.6, "maxTrailingSilence": 5, "minTrailingSilence": 0.6}
null

2021-09-01 05:42:15.80726
224
73
10217
2021-09-03 13:15:15.80726



SELECT 1


WITH all_values AS (
    SELECT s.projectid AS projectid,
           s.prompttype AS prompttype,
           el.attributes->>'prompt' AS prompt,
           el.attributes->>'description' AS description,
           el.attributes->>'topic' AS topic,
           el.attributes->>'context' AS context,
           el.attributes->>'use_case' AS use_case,
           el.attributes->>'subtitle' AS subtitle,
           el.attributes->>'txValues' AS txValues,
           el.attributes->>'flashmode' AS flashmode,
           el.attributes->>'Isskippable' AS Isskippable,
           el.attributes->>'videoMaxDuration' AS videoMaxDuration,
           el.attributes->>'defaultCameraFacing' AS defaultCameraFacing,
           s.corpuscode AS corpuscode,
           s.scriptid AS scriptid,
           s.promptnum AS promptnum
    FROM source_table AS s
       CROSS JOIN LATERAL jsonb_array_elements(s.attributes::jsonb) AS el(attributes)
    WHERE
        s.prompttype != 'input' AND (s.created > now() - interval '30 minutes' OR s.modified > now() - interval '30 minutes')
    GROUP BY s.projectid, s.prompttype, prompt, description, topic, context, use_case, subtitle, txValues, flashmode, Isskippable, videoMaxDuration, defaultCameraFacing, corpuscode, scriptid, promptnum
   )
SELECT * FROM all_values;


projectid
prompttype
prompt
description
topic
context
use_case
subtitle
txvalues
flashmode
isskippable
videomaxduration
defaultcamerafacing
corpuscode
scriptid
promptnum



SELECT 0