Пытаюсь запустить на базе вот этот скрипт :
spoiler
/* Простые запросы
1) Фильмы с возрастным ограничением 18+ и продолжительностью менее 120 минут */
select * from film where age_limit >= 18 and duration <= 120;
--БД до 10к
<b><i>Seq</i> Scan on film (cost=0.00..23.00 rows=105 width=28)</b>
Filter: ((age_limit >= 18) AND (duration <= 120))
--БД до 10кк
Seq Scan on film (cost=0.00..31687.00 rows=132557 width=32)
Filter: ((age_limit >= 18) AND (duration <= 120))
create index film_duration_idx on film (duration);
Bitmap Heap Scan on film (cost=3014.83..16120.35 rows=132557 width=32)
Recheck Cond: (duration <= 120)
Filter: (age_limit >= 18)
-> Bitmap Index Scan on film_duration_idx (cost=0.00..2981.69 rows=161235 width=0)
Index Cond: (duration <= 120)
select * from ticket where price >= 500 and (purchase_timestamp - CURRENT_TIMESTAMP) < '24:00:00'::interval;
--БД до 10к
Seq Scan on ticket (cost=0.00..28.00 rows=172 width=24)
Filter: ((price >= '500'::numeric) AND ((purchase_timestamp - CURRENT_TIMESTAMP) < '24:00:00'::interval))
--БД до 10кк
Seq Scan on ticket (cost=0.00..38295.00 rows=230398 width=24)
Filter: ((price >= '500'::numeric) AND ((purchase_timestamp - CURRENT_TIMESTAMP) < '24:00:00'::interval))
select * from session where session_datetime >= timestamp '2021-12-01 00:00:00' and session_datetime < timestamp '2022-02-07 00:00:00'
--БД до 10кк
Gather (cost=1000.00..18854.40 rows=1864 width=20)
Workers Planned: 2
-> Parallel Seq Scan on session (cost=0.00..17668.00 rows=777 width=20)
Filter: ((session_datetime >= '2021-12-01 00:00:00'::timestamp without time zone) AND (session_datetime < '2022-02-07 00:00:00'::timestamp without time zone))
create index session_idx on session (session_datetime);
Bitmap Heap Scan on session (cost=45.49..4934.84 rows=2055 width=20)
Recheck Cond: ((session_datetime >= '2021-12-01 00:00:00'::timestamp without time zone) AND (session_datetime < '2022-02-07 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on session_idx (cost=0.00..44.98 rows=2055 width=0)
Index Cond: ((session_datetime >= '2021-12-01 00:00:00'::timestamp without time zone) AND (session_datetime < '2022-02-07 00:00:00'::timestamp without time zone))
select f.name as film_name, sum(t.price) as paid_tickets_price
from public.order as o inner join public.order_to_ticket as o_to_t on o_to_t.order_id = o.order_id
inner join public.ticket as t on o_to_t.ticket_id = t.ticket_id
inner join public.session as s on t.session_id = s.session_id
inner join public.film as f on s.film_id = f.film_id
where o.status = 'PAID'
group by f.name
order by paid_tickets_price desc
limit 1;
--БД до 10к
Limit (cost=38.22..38.23 rows=1 width=48)
-> Sort (cost=38.22..38.23 rows=1 width=48)
Sort Key: (sum(t.price)) DESC
-> GroupAggregate (cost=38.19..38.21 rows=1 width=48)
Group Key: f.name
-> Sort (cost=38.19..38.19 rows=1 width=20)
Sort Key: f.name
-> Nested Loop (cost=19.34..38.18 rows=1 width=20)
-> Nested Loop (cost=19.06..37.83 rows=1 width=8)
-> Nested Loop (cost=18.79..37.49 rows=1 width=8)
-> Hash Join (cost=18.51..37.15 rows=1 width=4)
Hash Cond: (o_to_t.order_id = o.order_id)
-> Seq Scan on order_to_ticket o_to_t (cost=0.00..16.00 rows=1000 width=8)
-> Hash (cost=18.50..18.50 rows=1 width=4)
-> Seq Scan on "order" o (cost=0.00..18.50 rows=1 width=4)
Filter: (status = 'PAID'::order_status)
-> Index Scan using ticket_pkey on ticket t (cost=0.28..0.34 rows=1 width=12)
Index Cond: (ticket_id = o_to_t.ticket_id)
-> Index Scan using session_id_pk on session s (cost=0.28..0.34 rows=1 width=8)
Index Cond: (session_id = t.session_id)
-> Index Scan using film_pkey on film f (cost=0.28..0.34 rows=1 width=20)
Index Cond: (film_id = s.film_id)
--БД до 10кк
Limit (cost=41002.33..41002.34 rows=1 width=52)
-> Sort (cost=41002.33..41002.34 rows=1 width=52)
Sort Key: (sum(t.price)) DESC
-> Finalize GroupAggregate (cost=41002.29..41002.32 rows=1 width=52)
Group Key: f.name
-> Sort (cost=41002.29..41002.30 rows=2 width=52)
Sort Key: f.name
-> Gather (cost=41002.06..41002.28 rows=2 width=52)
Workers Planned: 2
-> Partial GroupAggregate (cost=40002.06..40002.08 rows=1 width=52)
Group Key: f.name
-> Sort (cost=40002.06..40002.06 rows=1 width=24)
Sort Key: f.name
-> Nested Loop (cost=25069.30..40002.05 rows=1 width=24)
-> Nested Loop (cost=25068.87..40001.56 rows=1 width=8)
-> Nested Loop (cost=25068.44..40001.08 rows=1 width=8)
-> Hash Join (cost=25068.01..40000.59 rows=1 width=4)
Hash Cond: (o_to_t.order_id = o.order_id)
-> Parallel Seq Scan on order_to_ticket o_to_t (cost=0.00..13401.33 rows=583333 width=8)
-> Hash (cost=25068.00..25068.00 rows=1 width=4)
-> Seq Scan on "order" o (cost=0.00..25068.00 rows=1 width=4)
Filter: (status = 'PAID'::order_status)
-> Index Scan using ticket_pkey on ticket t (cost=0.43..0.49 rows=1 width=12)
Index Cond: (ticket_id = o_to_t.ticket_id)
-> Index Scan using session_id_pk on session s (cost=0.43..0.48 rows=1 width=8)
Index Cond: (session_id = t.session_id)
-> Index Scan using film_pkey on film f (cost=0.43..0.49 rows=1 width=24)
Index Cond: (film_id = s.film_id)
create index order_status_idx on "order" (status);
Limit (cost=15938.64..15938.64 rows=1 width=52)
-> Sort (cost=15938.64..15938.64 rows=1 width=52)
Sort Key: (sum(t.price)) DESC
-> GroupAggregate (cost=15938.60..15938.63 rows=1 width=52)
Group Key: f.name
-> Sort (cost=15938.60..15938.61 rows=1 width=24)
Sort Key: f.name
-> Nested Loop (cost=1005.74..15938.59 rows=1 width=24)
-> Nested Loop (cost=1005.31..15938.11 rows=1 width=8)
-> Nested Loop (cost=1004.88..15937.63 rows=1 width=8)
-> Gather (cost=1004.46..15937.14 rows=1 width=4)
Workers Planned: 2
-> Hash Join (cost=4.46..14937.04 rows=1 width=4)
Hash Cond: (o_to_t.order_id = o.order_id)
-> Parallel Seq Scan on order_to_ticket o_to_t (cost=0.00..13401.33 rows=583333 width=8)
-> Hash (cost=4.45..4.45 rows=1 width=4)
-> Index Scan using order_status_idx on "order" o (cost=0.43..4.45 rows=1 width=4)
Index Cond: (status = 'PAID'::order_status)
-> Index Scan using ticket_pkey on ticket t (cost=0.43..0.49 rows=1 width=12)
Index Cond: (ticket_id = o_to_t.ticket_id)
-> Index Scan using session_id_pk on session s (cost=0.43..0.48 rows=1 width=8)
Index Cond: (session_id = t.session_id)
-> Index Scan using film_pkey on film f (cost=0.43..0.49 rows=1 width=24)
Index Cond: (film_id = s.film_id)
select f.name, s.session_datetime, max(t.price) from film f
inner join "session" s on f.film_id = s.film_id
inner join ticket t on t.session_id = s.session_id
where session_datetime < '2022-02-01 00:00:00'
group by f.name, s.session_datetime;
При запуске получаю ошибку: ERROR: ОШИБКА: ошибка синтаксиса (примерное положение: "Seq")
LINE 9: Seq Scan on film (cost=0.00..23.00 rows=105 width=28)
SQL state: 42601
Character: 178
(проблемную строку на всякий случай выделил жирным в коде). Подскажите в чём может быть проблема?!??
Средний
