homework/dsolovianov/greenplum/Добавлены индексы.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.4.26
-- Dumped by pg_dump version 17.5

-- Started on 2026-01-17 22:42:00

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_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;

--
-- TOC entry 10 (class 2615 OID 25610)
-- Name: core; Type: SCHEMA; Schema: -; Owner: dsolovianov
--

CREATE SCHEMA core;


ALTER SCHEMA core OWNER TO dsolovianov;

--
-- TOC entry 11 (class 2615 OID 25611)
-- Name: dm; Type: SCHEMA; Schema: -; Owner: dsolovianov
--

CREATE SCHEMA dm;


ALTER SCHEMA dm OWNER TO dsolovianov;

--
-- TOC entry 9 (class 2615 OID 25609)
-- Name: raw; Type: SCHEMA; Schema: -; Owner: dsolovianov
--

CREATE SCHEMA raw;


ALTER SCHEMA raw OWNER TO dsolovianov;

SET default_tablespace = '';

--
-- TOC entry 273 (class 1259 OID 25635)
-- Name: dim_course; Type: TABLE; Schema: core; Owner: dsolovianov
--

CREATE TABLE core.dim_course (
    course_id integer NOT NULL,
    title text,
    category text
);


ALTER TABLE core.dim_course OWNER TO dsolovianov;

--
-- TOC entry 275 (class 1259 OID 25656)
-- Name: dim_date; Type: TABLE; Schema: core; Owner: dsolovianov
--

CREATE TABLE core.dim_date (
    date date NOT NULL,
    year integer,
    month integer,
    day integer,
    weekday text
);


ALTER TABLE core.dim_date OWNER TO dsolovianov;

--
-- TOC entry 274 (class 1259 OID 25643)
-- Name: dim_lesson; Type: TABLE; Schema: core; Owner: dsolovianov
--

CREATE TABLE core.dim_lesson (
    lesson_id integer NOT NULL,
    title text,
    duration_min integer,
    course_id integer
);


ALTER TABLE core.dim_lesson OWNER TO dsolovianov;

--
-- TOC entry 272 (class 1259 OID 25627)
-- Name: dim_user; Type: TABLE; Schema: core; Owner: dsolovianov
--

CREATE TABLE core.dim_user (
    user_id integer NOT NULL,
    name text,
    age integer,
    email text,
    registration_date date
);


ALTER TABLE core.dim_user OWNER TO dsolovianov;

--
-- TOC entry 276 (class 1259 OID 25872)
-- Name: enrollments; Type: TABLE; Schema: core; Owner: dsolovianov
--

CREATE TABLE core.enrollments (
    user_id integer,
    course_id integer,
    enrolled_at timestamp without time zone
);


ALTER TABLE core.enrollments OWNER TO dsolovianov;

--
-- TOC entry 277 (class 1259 OID 25885)
-- Name: fact_lesson_views; Type: TABLE; Schema: core; Owner: dsolovianov
--

CREATE TABLE core.fact_lesson_views (
    user_id integer,
    lesson_id integer,
    course_id integer,
    viewed_at timestamp without time zone
);


ALTER TABLE core.fact_lesson_views OWNER TO dsolovianov;

--
-- TOC entry 268 (class 1259 OID 25615)
-- Name: courses; Type: TABLE; Schema: raw; Owner: dsolovianov
--

CREATE TABLE raw.courses (
    id integer,
    title character varying(50),
    category character varying(50),
    created_at character varying(50)
);


ALTER TABLE raw.courses OWNER TO dsolovianov;

--
-- TOC entry 270 (class 1259 OID 25621)
-- Name: enrollments; Type: TABLE; Schema: raw; Owner: dsolovianov
--

CREATE TABLE raw.enrollments (
    id integer,
    user_id integer,
    course_id integer,
    enrolled_at character varying(50)
);


ALTER TABLE raw.enrollments OWNER TO dsolovianov;

--
-- TOC entry 271 (class 1259 OID 25624)
-- Name: lesson_views; Type: TABLE; Schema: raw; Owner: dsolovianov
--

CREATE TABLE raw.lesson_views (
    id integer,
    user_id integer,
    lesson_id integer,
    viewed_at character varying(50)
);


ALTER TABLE raw.lesson_views OWNER TO dsolovianov;

--
-- TOC entry 269 (class 1259 OID 25618)
-- Name: lessons; Type: TABLE; Schema: raw; Owner: dsolovianov
--

CREATE TABLE raw.lessons (
    id integer,
    course_id integer,
    title character varying(50),
    duration_min integer
);


ALTER TABLE raw.lessons OWNER TO dsolovianov;

--
-- TOC entry 267 (class 1259 OID 25612)
-- Name: users; Type: TABLE; Schema: raw; Owner: dsolovianov
--

CREATE TABLE raw.users (
    id integer,
    name character varying(50),
    age integer,
    email character varying(50),
    registration_date character varying(50)
);


ALTER TABLE raw.users OWNER TO dsolovianov;

--
-- TOC entry 2506 (class 0 OID 25635)
-- Dependencies: 273
-- Data for Name: dim_course; Type: TABLE DATA; Schema: core; Owner: dsolovianov
--

COPY core.dim_course (course_id, title, category) FROM stdin;
2	Python для анализа данных	programming
1	SQL для начинающих	data
3	BI с нуля	business
\.


--
-- TOC entry 2508 (class 0 OID 25656)
-- Dependencies: 275
-- Data for Name: dim_date; Type: TABLE DATA; Schema: core; Owner: dsolovianov
--

COPY core.dim_date (date, year, month, day, weekday) FROM stdin;
2023-01-15	2023	1	15	Sunday   
2023-01-16	2023	1	16	Monday   
2023-01-21	2023	1	21	Saturday 
2023-01-20	2023	1	20	Friday   
2023-03-05	2023	3	5	Sunday   
2023-02-01	2023	2	1	Wednesday
2023-04-02	2023	4	2	Sunday   
2023-03-06	2023	3	6	Monday   
2023-04-01	2023	4	1	Saturday 
2023-04-03	2023	4	3	Monday   
\.


--
-- TOC entry 2507 (class 0 OID 25643)
-- Dependencies: 274
-- Data for Name: dim_lesson; Type: TABLE DATA; Schema: core; Owner: dsolovianov
--

COPY core.dim_lesson (lesson_id, title, duration_min, course_id) FROM stdin;
4	Основы BI	12	3
5	Метрики и дашборды	18	3
2	JOIN	15	1
1	SELECT и FROM	10	1
3	Pandas	20	2
\.


--
-- TOC entry 2505 (class 0 OID 25627)
-- Dependencies: 272
-- Data for Name: dim_user; Type: TABLE DATA; Schema: core; Owner: dsolovianov
--

COPY core.dim_user (user_id, name, age, email, registration_date) FROM stdin;
3	Charlie	22	charlie@mail.com	2023-02-20
1	Alice	25	alice@mail.com	2023-01-10
2	Bob	30	bob@gmail.com	2023-02-05
5	Ethan	35	ethan@gmail.com	2023-03-10
4	Diana	28	diana@mail.com	2023-03-01
\.


--
-- TOC entry 2509 (class 0 OID 25872)
-- Dependencies: 276
-- Data for Name: enrollments; Type: TABLE DATA; Schema: core; Owner: dsolovianov
--

COPY core.enrollments (user_id, course_id, enrolled_at) FROM stdin;
4	3	2023-04-01 00:00:00
1	2	2023-02-01 00:00:00
2	1	2023-01-20 00:00:00
1	1	2023-01-15 00:00:00
3	2	2023-03-05 00:00:00
\.


--
-- TOC entry 2510 (class 0 OID 25885)
-- Dependencies: 277
-- Data for Name: fact_lesson_views; Type: TABLE DATA; Schema: core; Owner: dsolovianov
--

COPY core.fact_lesson_views (user_id, lesson_id, course_id, viewed_at) FROM stdin;
2	1	1	2023-01-21 09:00:00
1	2	1	2023-01-16 10:15:00
3	3	2	2023-03-06 12:00:00
1	1	1	2023-01-16 10:00:00
4	4	3	2023-04-02 10:00:00
4	5	3	2023-04-03 10:00:00
\.


--
-- TOC entry 2501 (class 0 OID 25615)
-- Dependencies: 268
-- Data for Name: courses; Type: TABLE DATA; Schema: raw; Owner: dsolovianov
--

COPY raw.courses (id, title, category, created_at) FROM stdin;
3	BI с нуля	business	2023-03-01
1	SQL для начинающих	data	2023-01-01
2	Python для анализа данных	programming	2023-01-15
\.


--
-- TOC entry 2503 (class 0 OID 25621)
-- Dependencies: 270
-- Data for Name: enrollments; Type: TABLE DATA; Schema: raw; Owner: dsolovianov
--

COPY raw.enrollments (id, user_id, course_id, enrolled_at) FROM stdin;
1	1	1	2023-01-15
2	1	2	2023-02-01
3	2	1	2023-01-20
4	3	2	2023-03-05
5	4	3	2023-04-01
\.


--
-- TOC entry 2504 (class 0 OID 25624)
-- Dependencies: 271
-- Data for Name: lesson_views; Type: TABLE DATA; Schema: raw; Owner: dsolovianov
--

COPY raw.lesson_views (id, user_id, lesson_id, viewed_at) FROM stdin;
2	1	2	2023-01-16 10:15:00.000
1	1	1	2023-01-16 10:00:00.000
3	2	1	2023-01-21 09:00:00.000
5	4	4	2023-04-02 10:00:00.000
4	3	3	2023-03-06 12:00:00.000
6	4	5	2023-04-03 10:00:00.000
\.


--
-- TOC entry 2502 (class 0 OID 25618)
-- Dependencies: 269
-- Data for Name: lessons; Type: TABLE DATA; Schema: raw; Owner: dsolovianov
--

COPY raw.lessons (id, course_id, title, duration_min) FROM stdin;
1	1	SELECT и FROM	10
2	1	JOIN	15
3	2	Pandas	20
4	3	Основы BI	12
5	3	Метрики и дашборды	18
\.


--
-- TOC entry 2500 (class 0 OID 25612)
-- Dependencies: 267
-- Data for Name: users; Type: TABLE DATA; Schema: raw; Owner: dsolovianov
--

COPY raw.users (id, name, age, email, registration_date) FROM stdin;
1	Alice	25	alice@mail.com	2023-01-10
2	Bob	30	bob@gmail.com	2023-02-05
3	Charlie	22	charlie@mail.com	2023-02-20
5	Ethan	35	ethan@gmail.com	2023-03-10
4	Diana	28	diana@mail.com	2023-03-01
\.


--
-- TOC entry 2314 (class 2606 OID 25642)
-- Name: dim_course dim_course_pkey; Type: CONSTRAINT; Schema: core; Owner: dsolovianov
--

ALTER TABLE ONLY core.dim_course
    ADD CONSTRAINT dim_course_pkey PRIMARY KEY (course_id);


--
-- TOC entry 2319 (class 2606 OID 25663)
-- Name: dim_date dim_date_pkey; Type: CONSTRAINT; Schema: core; Owner: dsolovianov
--

ALTER TABLE ONLY core.dim_date
    ADD CONSTRAINT dim_date_pkey PRIMARY KEY (date);


--
-- TOC entry 2316 (class 2606 OID 25650)
-- Name: dim_lesson dim_lesson_pkey; Type: CONSTRAINT; Schema: core; Owner: dsolovianov
--

ALTER TABLE ONLY core.dim_lesson
    ADD CONSTRAINT dim_lesson_pkey PRIMARY KEY (lesson_id);


--
-- TOC entry 2312 (class 2606 OID 25634)
-- Name: dim_user dim_user_pkey; Type: CONSTRAINT; Schema: core; Owner: dsolovianov
--

ALTER TABLE ONLY core.dim_user
    ADD CONSTRAINT dim_user_pkey PRIMARY KEY (user_id);


--
-- TOC entry 2317 (class 1259 OID 25906)
-- Name: idx_dim_lesson_course_id; Type: INDEX; Schema: core; Owner: dsolovianov
--
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



/*
1) Создание INDEX на таблицы в CORE-слое
*/

CREATE INDEX idx_dim_lesson_course_id ON core.dim_lesson USING btree (course_id);

CREATE INDEX idx_enrollments_course_id ON core.enrollments USING btree (course_id);

CREATE INDEX idx_fact_views_course_id ON core.fact_lesson_views USING btree (course_id);

CREATE INDEX idx_fact_views_course_viewed_at ON core.fact_lesson_views USING btree (course_id, viewed_at);

CREATE INDEX idx_fact_views_lesson_id ON core.fact_lesson_views USING btree (lesson_id);

CREATE INDEX idx_fact_views_user_id ON core.fact_lesson_views USING btree (user_id);

CREATE INDEX idx_fact_views_user_viewed_at ON core.fact_lesson_views USING btree (user_id, viewed_at);
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------




ALTER TABLE ONLY core.dim_lesson
    ADD CONSTRAINT dim_lesson_course_id_fkey FOREIGN KEY (course_id) REFERENCES core.dim_course(course_id);


--
-- TOC entry 2327 (class 2606 OID 25880)
-- Name: enrollments enrollments_course_id_fkey; Type: FK CONSTRAINT; Schema: core; Owner: dsolovianov
--

ALTER TABLE ONLY core.enrollments
    ADD CONSTRAINT enrollments_course_id_fkey FOREIGN KEY (course_id) REFERENCES core.dim_course(course_id);


--
-- TOC entry 2328 (class 2606 OID 25875)
-- Name: enrollments enrollments_user_id_fkey; Type: FK CONSTRAINT; Schema: core; Owner: dsolovianov
--

ALTER TABLE ONLY core.enrollments
    ADD CONSTRAINT enrollments_user_id_fkey FOREIGN KEY (user_id) REFERENCES core.dim_user(user_id);


--
-- TOC entry 2329 (class 2606 OID 25898)
-- Name: fact_lesson_views fact_lesson_views_course_id_fkey; Type: FK CONSTRAINT; Schema: core; Owner: dsolovianov
--

ALTER TABLE ONLY core.fact_lesson_views
    ADD CONSTRAINT fact_lesson_views_course_id_fkey FOREIGN KEY (course_id) REFERENCES core.dim_course(course_id);


--
-- TOC entry 2330 (class 2606 OID 25893)
-- Name: fact_lesson_views fact_lesson_views_lesson_id_fkey; Type: FK CONSTRAINT; Schema: core; Owner: dsolovianov
--

ALTER TABLE ONLY core.fact_lesson_views
    ADD CONSTRAINT fact_lesson_views_lesson_id_fkey FOREIGN KEY (lesson_id) REFERENCES core.dim_lesson(lesson_id);


--
-- TOC entry 2331 (class 2606 OID 25888)
-- Name: fact_lesson_views fact_lesson_views_user_id_fkey; Type: FK CONSTRAINT; Schema: core; Owner: dsolovianov
--

ALTER TABLE ONLY core.fact_lesson_views
    ADD CONSTRAINT fact_lesson_views_user_id_fkey FOREIGN KEY (user_id) REFERENCES core.dim_user(user_id);


-- Completed on 2026-01-17 22:42:02

--
-- PostgreSQL database dump complete
--