Size: a a a
I
-k это копирование с использование жестких ссылок - это существенно сокращает время апгрейдаR
insert into "post" ("created_at", "title", "updated_at") values ('2021-03-01T16:48:40.845Z', 'My first post', '2021-03-01T16:48:40.845Z') returning "id";R
SQL Error [22P02]: ERROR: invalid input syntax for type json
Detail: Token "My" is invalid.
Position: 94
Where: JSON data, line 1: My...
R
SB
SQL Error [22P02]: ERROR: invalid input syntax for type json
Detail: Token "My" is invalid.
Position: 94
Where: JSON data, line 1: My...
SB
R
R
AL
YS
explain SELECT DISTINCT on (t.uuid)
t.uuid as task_uuid,
ct. "name" city,
c.plate_number,
c.id car_id,
pa.created_at start_time,
(now() - pa.created_at) active_time,
--r.long_service_alert_interval allowed_time,
u.phone user_phone,
r.caption AS ROLE,
CONCAT_WS(' '::text, u.first_name, u.patronymic_name, u.last_name) AS user_name
FROM
performed_actions pa
JOIN tasks t ON t.uuid = pa.task_uuid
JOIN cars c ON t.car_id = c.id
JOIN cities ct ON ct.external_id = c.region_id
JOIN roles r ON r.uuid = t.role_uuid -- AND r.long_service_alert_interval IS NOT NULL
JOIN users u ON u.uuid = t.user_uuid
WHERE
t.finished_at IS NULL
AND(now() - pa.created_at) >= interval '20 minutes' --r.long_service_alert_interval
ORDER BY
t.uuid,
active_time DESC;
Unique (cost=698438.31..699336.05 rows=179549 width=517)
-> Sort (cost=698438.31..698887.18 rows=179549 width=517)
Sort Key: t.uuid, ((now() - pa.created_at)) DESC
-> Hash Join (cost=296229.54..682769.05 rows=179549 width=517)
Hash Cond: (t.user_uuid = u.uuid)
-> Hash Join (cost=295933.68..680654.89 rows=179549 width=473)
Hash Cond: (t.role_uuid = r.uuid)
-> Hash Join (cost=295931.96..680119.43 rows=179549 width=215)
Hash Cond: (t.car_id = c.id)
-> Hash Join (cost=220800.77..602514.80 rows=180787 width=60)
Hash Cond: (pa.task_uuid = t.uuid)
-> Seq Scan on performed_actions pa (cost=0.00..376286.51 rows=2067629 width=24)
Filter: ((now() - created_at) >= '00:20:00'::interval)
-> Hash (cost=215722.12..215722.12 rows=406292 width=52)
-> Bitmap Heap Scan on tasks t (cost=12957.20..215722.12 rows=406292 width=52)
Recheck Cond: (finished_at IS NULL)
-> Bitmap Index Scan on ix__tasks__finished_at (cost=0.00..12855.62 rows=406292 width=0)
Index Cond: (finished_at IS NULL)
-> Hash (cost=74920.86..74920.86 rows=16827 width=159)
-> Hash Join (cost=1.68..74920.86 rows=16827 width=159)
Hash Cond: (c.region_id = ct.external_id)
-> Index Scan using cars_pkey on cars c (cost=0.41..74687.78 rows=16943 width=17)
-> Hash (cost=1.12..1.12 rows=12 width=150)
-> Seq Scan on cities ct (cost=0.00..1.12 rows=12 width=150)
-> Hash (cost=1.32..1.32 rows=32 width=290)
-> Seq Scan on roles r (cost=0.00..1.32 rows=32 width=290)
-> Hash (cost=228.16..228.16 rows=5416 width=71)
-> Seq Scan on users u (cost=0.00..228.16 rows=5416 width=71)
(now() - pa.created_at) >= interval '20 minutes'
YS
V
(now() - pa.created_at) >= interval '20 minutes'
YS
could not resize shared memory segment "/PostgreSQL.1284959199" to 2147483648 bytes: No space left on device
CONTEXT: parallel worker
AL
YS
Поле оператор_сравнения выражениев общем случае. А в этом так, вроде:
pa.created_at < now() - interval '20 minutes'
V
Поле оператор_сравнения выражениев общем случае. А в этом так, вроде:
pa.created_at < now() - interval '20 minutes'
YS
I
V
PostgreSQL 11.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
Unique (cost=754759.68..757452.87 rows=406292 width=517)
-> Sort (cost=754759.68..756106.28 rows=538639 width=517)
Sort Key: t.uuid, ((now() - pa.created_at)) DESC
-> Hash Join (cost=296438.90..703484.05 rows=538639 width=517)
Hash Cond: (t.user_uuid = u.uuid)
-> Hash Join (cost=296143.04..697733.37 rows=538639 width=473)
Hash Cond: (t.role_uuid = r.uuid)
-> Hash Join (cost=296141.32..696130.46 rows=538639 width=215)
Hash Cond: (t.car_id = c.id)
-> Hash Join (cost=220800.77..613369.69 rows=542354 width=60)
Hash Cond: (pa.task_uuid = t.uuid)
-> Seq Scan on performed_actions pa (cost=0.00..376286.51 rows=6202824 width=24)
Filter: (created_at <= (now() - '00:20:00'::interval))
-> Hash (cost=215722.12..215722.12 rows=406292 width=52)
-> Bitmap Heap Scan on tasks t (cost=12957.20..215722.12 rows=406292 width=52)
Recheck Cond: (finished_at IS NULL)
-> Bitmap Index Scan on ix__tasks__finished_at (cost=0.00..12855.62 rows=406292 width=0)
Index Cond: (finished_at IS NULL)
-> Hash (cost=75130.29..75130.29 rows=16821 width=159)
-> Hash Join (cost=1.68..75130.29 rows=16821 width=159)
Hash Cond: (c.region_id = ct.external_id)
-> Index Scan using cars_pkey on cars c (cost=0.41..74897.30 rows=16937 width=17)
-> Hash (cost=1.12..1.12 rows=12 width=150)
-> Seq Scan on cities ct (cost=0.00..1.12 rows=12 width=150)
-> Hash (cost=1.32..1.32 rows=32 width=290)
-> Seq Scan on roles r (cost=0.00..1.32 rows=32 width=290)
-> Hash (cost=228.16..228.16 rows=5416 width=71)
-> Seq Scan on users u (cost=0.00..228.16 rows=5416 width=71)
V
guido_backend=> \d performed_actions
Table "public.performed_actions"
Column | Type | Collation | Nullable | Default
----------------------+--------------------------+-----------+----------+--------------------
uuid | uuid | | not null | uuid_generate_v4()
task_uuid | uuid | | |
action_uuid | uuid | | |
created_at | timestamp with time zone | | | CURRENT_TIMESTAMP
updated_at | timestamp with time zone | | | CURRENT_TIMESTAMP
started_at | timestamp with time zone | | | CURRENT_TIMESTAMP
finished_at | timestamp with time zone | | |
car_point_on_start | geometry | | |
car_point_on_finish | geometry | | |
extra | jsonb | | |
user_point_on_finish | geometry | | |
Indexes:
"performed_actions_pkey" PRIMARY KEY, btree (uuid)
"ix__performed_actions__extra" gin (extra)
"ix__performed_actions__task_uuid" btree (task_uuid)
"performed_actions_finished_at_index" btree (finished_at DESC)
"uniqu_action_per_task" UNIQUE CONSTRAINT, btree (task_uuid, action_uuid)
Foreign-key constraints:
"performed_actions_action_uuid_fkey" FOREIGN KEY (action_uuid) REFERENCES actions(uuid)
"performed_actions_task_uuid_fkey" FOREIGN KEY (task_uuid) REFERENCES tasks(uuid)
Referenced by:
TABLE "refuel_check_schedule" CONSTRAINT "refuel_check_schedule_performed_action_uuid_fkey" FOREIGN KEY (performed_action_uuid) REFERENCES performed_actions(uuid) ON DELETE CASCADE
TABLE "task_reports" CONSTRAINT "task_reports_performed_action_uuid_fkey" FOREIGN KEY (performed_action_uuid) REFERENCES performed_actions(uuid) ON DELETE CASCADE
Publications:
"dbz_publication"