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?.

--
-- PostgreSQL database dump
--

-- Dumped from database version 12.1
-- Dumped by pg_dump version 12.3

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: brandlift; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.brandlift (
    campaign_id uuid NOT NULL,
    respondent_goal integer NOT NULL
);

ALTER TABLE public.brandlift OWNER TO postgres;

--
-- Name: COLUMN brandlift.campaign_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.brandlift.campaign_id IS '(DC2Type:uuid)';

--
-- Name: brandlift_answer; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.brandlift_answer (
    id integer NOT NULL,
    question_id uuid NOT NULL,
    respondent_id integer NOT NULL
);

ALTER TABLE public.brandlift_answer OWNER TO postgres;

--
-- Name: COLUMN brandlift_answer.question_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.brandlift_answer.question_id IS '(DC2Type:uuid)';

--
-- Name: brandlift_answer_content; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.brandlift_answer_content (
    id integer NOT NULL,
    answer_id integer NOT NULL,
    brand_id integer
);

ALTER TABLE public.brandlift_answer_content OWNER TO postgres;

--
-- Name: brandlift_answer_content_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.brandlift_answer_content_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.brandlift_answer_content_id_seq OWNER TO postgres;

--
-- Name: brandlift_answer_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.brandlift_answer_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.brandlift_answer_id_seq OWNER TO postgres;

--
-- Name: brandlift_brand; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.brandlift_brand (
    id integer NOT NULL,
    campaign_id uuid NOT NULL,
    name character varying(255) NOT NULL
);

ALTER TABLE public.brandlift_brand OWNER TO postgres;

--
-- Name: COLUMN brandlift_brand.campaign_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.brandlift_brand.campaign_id IS '(DC2Type:uuid)';

--
-- Name: brandlift_brand_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.brandlift_brand_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.brandlift_brand_id_seq OWNER TO postgres;

--
-- Name: brandlift_question; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.brandlift_question (
    id uuid NOT NULL,
    campaign_id uuid NOT NULL,
    title character varying(255) NOT NULL
);

ALTER TABLE public.brandlift_question OWNER TO postgres;

--
-- Name: COLUMN brandlift_question.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.brandlift_question.id IS '(DC2Type:uuid)';

--
-- Name: COLUMN brandlift_question.campaign_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.brandlift_question.campaign_id IS '(DC2Type:uuid)';

--
-- Name: brandlift_respondent; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.brandlift_respondent (
    id integer NOT NULL,
    campaign_id uuid NOT NULL,
    segment_id integer NOT NULL,
    aam_uuid character varying(255) DEFAULT NULL::character varying,
    "timestamp" timestamp(0) without time zone NOT NULL
);

ALTER TABLE public.brandlift_respondent OWNER TO postgres;

--
-- Name: COLUMN brandlift_respondent.campaign_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.brandlift_respondent.campaign_id IS '(DC2Type:uuid)';

--
-- Name: brandlift_respondent_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.brandlift_respondent_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.brandlift_respondent_id_seq OWNER TO postgres;

--
-- Name: brandlift_segment; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.brandlift_segment (
    id integer NOT NULL,
    name character varying(255) NOT NULL
);

ALTER TABLE public.brandlift_segment OWNER TO postgres;

--
-- Name: brandlift_segment_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.brandlift_segment_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.brandlift_segment_id_seq OWNER TO postgres;

--
-- Name: campaign; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.campaign (
    id uuid NOT NULL,
    format_id integer NOT NULL,
    advertiser character varying(255) NOT NULL,
    name character varying(255) NOT NULL,
    date_start timestamp(0) without time zone NOT NULL,
    date_end timestamp(0) without time zone NOT NULL
);

ALTER TABLE public.campaign OWNER TO postgres;

--
-- Name: COLUMN campaign.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.campaign.id IS '(DC2Type:uuid)';

--
-- Name: doctrine_migration_versions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.doctrine_migration_versions (
    version character varying(191) NOT NULL,
    executed_at timestamp(0) without time zone DEFAULT NULL::timestamp without time zone,
    execution_time integer
);

ALTER TABLE public.doctrine_migration_versions OWNER TO postgres;

--
-- Name: format; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.format (
    id integer NOT NULL,
    name character varying(255) NOT NULL
);

ALTER TABLE public.format OWNER TO postgres;

--
-- Name: format_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.format_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.format_id_seq OWNER TO postgres;

--
-- Data for Name: brandlift; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.brandlift (campaign_id, respondent_goal) FROM stdin;
8d8c79ce-188c-4f5e-bd72-edb854faf34c	500
\.

--
-- Data for Name: brandlift_answer; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.brandlift_answer (id, question_id, respondent_id) FROM stdin;
28	6d6596f4-9418-4f76-9234-c3c943ca56cf	18
29	c84760de-be6f-4f8e-be32-9195846307cf	18
30	6d6596f4-9418-4f76-9234-c3c943ca56cf	19
31	c84760de-be6f-4f8e-be32-9195846307cf	19
32	6d6596f4-9418-4f76-9234-c3c943ca56cf	20
33	c84760de-be6f-4f8e-be32-9195846307cf	20
34	6d6596f4-9418-4f76-9234-c3c943ca56cf	21
35	c84760de-be6f-4f8e-be32-9195846307cf	21
36	6d6596f4-9418-4f76-9234-c3c943ca56cf	22
37	c84760de-be6f-4f8e-be32-9195846307cf	22
\.

--
-- Data for Name: brandlift_answer_content; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.brandlift_answer_content (id, answer_id, brand_id) FROM stdin;
54	28	5
55	28	7
56	29	5
57	29	8
58	30	5
59	30	7
60	31	7
61	31	5
62	32	5
63	32	7
64	33	6
65	33	7
66	33	8
67	34	7
68	34	5
69	35	6
70	35	7
71	35	8
72	36	7
73	36	5
74	37	6
75	37	7
\.

--
-- Data for Name: brandlift_brand; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.brandlift_brand (id, campaign_id, name) FROM stdin;
5	8d8c79ce-188c-4f5e-bd72-edb854faf34c	Nike
6	8d8c79ce-188c-4f5e-bd72-edb854faf34c	Adidas
7	8d8c79ce-188c-4f5e-bd72-edb854faf34c	Lacoste
8	8d8c79ce-188c-4f5e-bd72-edb854faf34c	Puma
\.

--
-- Data for Name: brandlift_question; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.brandlift_question (id, campaign_id, title) FROM stdin;
6d6596f4-9418-4f76-9234-c3c943ca56cf	8d8c79ce-188c-4f5e-bd72-edb854faf34c	Parmi les propositions suivantes, pour lesquelles avez-vous vu de la publicité vidéo en ligne au cours du dernier mois ?
c84760de-be6f-4f8e-be32-9195846307cf	8d8c79ce-188c-4f5e-bd72-edb854faf34c	Si vous deviez prochainement choisir une marque de streetwear la ou lesquelles choisiriez-vous ?
\.

--
-- Data for Name: brandlift_respondent; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.brandlift_respondent (id, campaign_id, segment_id, aam_uuid, "timestamp") FROM stdin;
10	8d8c79ce-188c-4f5e-bd72-edb854faf34c	3	912407327014	2021-02-01 18:04:43
11	8d8c79ce-188c-4f5e-bd72-edb854faf34c	3	2692416912404	2021-02-01 18:06:01
12	8d8c79ce-188c-4f5e-bd72-edb854faf34c	4	9741094120421	2021-02-01 18:06:56
13	8d8c79ce-188c-4f5e-bd72-edb854faf34c	4	8973093247093	2021-02-01 18:07:59
14	8d8c79ce-188c-4f5e-bd72-edb854faf34c	4	2147124472178421	2021-02-01 18:08:34
15	8d8c79ce-188c-4f5e-bd72-edb854faf34c	4	2147124472178421	2021-02-01 18:09:20
16	8d8c79ce-188c-4f5e-bd72-edb854faf34c	4	23253532532352	2021-02-01 18:09:53
17	8d8c79ce-188c-4f5e-bd72-edb854faf34c	3	357325732577352	2021-02-01 18:20:01
18	8d8c79ce-188c-4f5e-bd72-edb854faf34c	3	357325732577352	2021-02-01 18:21:12
19	8d8c79ce-188c-4f5e-bd72-edb854faf34c	3	4354646464223	2021-02-01 18:22:14
20	8d8c79ce-188c-4f5e-bd72-edb854faf34c	4	6855985895808	2021-02-01 18:25:41
21	8d8c79ce-188c-4f5e-bd72-edb854faf34c	4	6855985895808	2021-02-01 18:26:03
22	8d8c79ce-188c-4f5e-bd72-edb854faf34c	4	6855985895808	2021-02-01 18:26:33
\.

--
-- Data for Name: brandlift_segment; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.brandlift_segment (id, name) FROM stdin;
3	expo
4	no expo
\.

--
-- Data for Name: campaign; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.campaign (id, format_id, advertiser, name, date_start, date_end) FROM stdin;
8d8c79ce-188c-4f5e-bd72-edb854faf34c	2	Nike	Air Max 270	2021-01-25 00:00:00	2021-02-28 00:00:00
\.

--
-- Data for Name: doctrine_migration_versions; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.doctrine_migration_versions (version, executed_at, execution_time) FROM stdin;
DoctrineMigrations\\Version20210131122327	2021-01-31 13:23:34	199
\.

--
-- Data for Name: format; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.format (id, name) FROM stdin;
2	Brandlift
\.

--
-- Name: brandlift_answer_content_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.brandlift_answer_content_id_seq', 75, true);

--
-- Name: brandlift_answer_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.brandlift_answer_id_seq', 37, true);

--
-- Name: brandlift_brand_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.brandlift_brand_id_seq', 8, true);

--
-- Name: brandlift_respondent_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.brandlift_respondent_id_seq', 22, true);

--
-- Name: brandlift_segment_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.brandlift_segment_id_seq', 4, true);

--
-- Name: format_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.format_id_seq', 2, true);

--
-- Name: brandlift_answer_content brandlift_answer_content_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.brandlift_answer_content
    ADD CONSTRAINT brandlift_answer_content_pkey PRIMARY KEY (id);

--
-- Name: brandlift_answer brandlift_answer_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.brandlift_answer
    ADD CONSTRAINT brandlift_answer_pkey PRIMARY KEY (id);

--
-- Name: brandlift_brand brandlift_brand_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.brandlift_brand
    ADD CONSTRAINT brandlift_brand_pkey PRIMARY KEY (id);

--
-- Name: brandlift brandlift_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.brandlift
    ADD CONSTRAINT brandlift_pkey PRIMARY KEY (campaign_id);

--
-- Name: brandlift_question brandlift_question_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.brandlift_question
    ADD CONSTRAINT brandlift_question_pkey PRIMARY KEY (id);

--
-- Name: brandlift_respondent brandlift_respondent_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.brandlift_respondent
    ADD CONSTRAINT brandlift_respondent_pkey PRIMARY KEY (id);

--
-- Name: brandlift_segment brandlift_segment_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.brandlift_segment
    ADD CONSTRAINT brandlift_segment_pkey PRIMARY KEY (id);

--
-- Name: campaign campaign_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.campaign
    ADD CONSTRAINT campaign_pkey PRIMARY KEY (id);

--
-- Name: doctrine_migration_versions doctrine_migration_versions_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.doctrine_migration_versions
    ADD CONSTRAINT doctrine_migration_versions_pkey PRIMARY KEY (version);

--
-- Name: format format_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.format
    ADD CONSTRAINT format_pkey PRIMARY KEY (id);

--
-- Name: answer_content_unique; Type: INDEX; Schema: public; Owner: postgres
--

CREATE UNIQUE INDEX answer_content_unique ON public.brandlift_answer_content USING btree (answer_id, brand_id);

--
-- Name: answer_unique; Type: INDEX; Schema: public; Owner: postgres
--

CREATE UNIQUE INDEX answer_unique ON public.brandlift_answer USING btree (question_id, respondent_id);

--
-- Name: idx_1f1512ddd629f605; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_1f1512ddd629f605 ON public.campaign USING btree (format_id);

--
-- Name: idx_3be90c3a44f5d008; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_3be90c3a44f5d008 ON public.brandlift_answer_content USING btree (brand_id);

--
-- Name: idx_3be90c3aaa334807; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_3be90c3aaa334807 ON public.brandlift_answer_content USING btree (answer_id);

--
-- Name: idx_80d627bdf639f774; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_80d627bdf639f774 ON public.brandlift_question USING btree (campaign_id);

--
-- Name: idx_d5009950db296aad; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_d5009950db296aad ON public.brandlift_respondent USING btree (segment_id);

--
-- Name: idx_d5009950f639f774; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_d5009950f639f774 ON public.brandlift_respondent USING btree (campaign_id);

--
-- Name: idx_d671e368f639f774; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_d671e368f639f774 ON public.brandlift_brand USING btree (campaign_id);

--
-- Name: idx_fcce59931e27f6bf; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_fcce59931e27f6bf ON public.brandlift_answer USING btree (question_id);

--
-- Name: idx_fcce5993ce80cd19; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_fcce5993ce80cd19 ON public.brandlift_answer USING btree (respondent_id);

--
-- Name: campaign fk_1f1512ddd629f605; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.campaign
    ADD CONSTRAINT fk_1f1512ddd629f605 FOREIGN KEY (format_id) REFERENCES public.format(id);

--
-- Name: brandlift_answer_content fk_3be90c3a44f5d008; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.brandlift_answer_content
    ADD CONSTRAINT fk_3be90c3a44f5d008 FOREIGN KEY (brand_id) REFERENCES public.brandlift_brand(id);

--
-- Name: brandlift_answer_content fk_3be90c3aaa334807; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.brandlift_answer_content
    ADD CONSTRAINT fk_3be90c3aaa334807 FOREIGN KEY (answer_id) REFERENCES public.brandlift_answer(id);

--
-- Name: brandlift fk_5b5d1287f639f774; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.brandlift
    ADD CONSTRAINT fk_5b5d1287f639f774 FOREIGN KEY (campaign_id) REFERENCES public.campaign(id);

--
-- Name: brandlift_question fk_80d627bdf639f774; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.brandlift_question
    ADD CONSTRAINT fk_80d627bdf639f774 FOREIGN KEY (campaign_id) REFERENCES public.brandlift(campaign_id);

--
-- Name: brandlift_respondent fk_d5009950db296aad; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.brandlift_respondent
    ADD CONSTRAINT fk_d5009950db296aad FOREIGN KEY (segment_id) REFERENCES public.brandlift_segment(id);

--
-- Name: brandlift_respondent fk_d5009950f639f774; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.brandlift_respondent
    ADD CONSTRAINT fk_d5009950f639f774 FOREIGN KEY (campaign_id) REFERENCES public.brandlift(campaign_id);

--
-- Name: brandlift_brand fk_d671e368f639f774; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.brandlift_brand
    ADD CONSTRAINT fk_d671e368f639f774 FOREIGN KEY (campaign_id) REFERENCES public.brandlift(campaign_id);

--
-- Name: brandlift_answer fk_fcce59931e27f6bf; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.brandlift_answer
    ADD CONSTRAINT fk_fcce59931e27f6bf FOREIGN KEY (question_id) REFERENCES public.brandlift_question(id);

--
-- Name: brandlift_answer fk_fcce5993ce80cd19; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.brandlift_answer
    ADD CONSTRAINT fk_fcce5993ce80cd19 FOREIGN KEY (respondent_id) REFERENCES public.brandlift_respondent(id);

--
-- PostgreSQL database dump complete
--

ERROR:  syntax error at or near "8"
LINE 293: 8d8c79ce-188c-4f5e-bd72-edb854faf34c 500
          ^