with recursive cat as (
select id, id top from landcategory where parent_id is null
union
select lc.id, top from landcategory lc join cat on (cat.id = lc.parent_id)
)
select lc.*, childs from landcategory lc join (
select top, array_agg(id) childs from cat
group by 1
) t on (t.top = lc.id)Блокирует ли процесс построения индексов таблицу?
Перед UPDATE я индекс по нему сделал
Мне потребовалось добавить одно поледобавление поля без DEFAULT, допускающего NULL значение, пройдет быстро. Если его надо заполнять (есть DEFAULT, или как вы руками), это уже апдейт на 100 млн строк.
1. Правильно ли я понимаю что hash индексы не могут использовать сразу все ядра
1.1 а btree могут?
3. Почему UPDATE не связанный с этим полем так тормозит?
2. Как быть в текущей ситуации. Я понимаю что индекс нужно было после UPDATE делать, но как понять через сколько процесс закончится? Может подождать стоит?
where field и LIKE '%pattern%' - выборка все же малого числа строк (сколько примерно?)?users
__________
id
name
...
categories
__________
id
name
...
user_categories
__________
user_id
category_idКогда выполняю этот запрос в SQL то 10 раз он может выполняться по 90 секунд, а на 11 может выполниться за 5 и потом продолжить выполняться за 5.
...
Там какие-то оптимизаторы есть в БД или другая хитрая логика? Просто по прошествии часа история повторяется и запрос опять начинает медленно выполняться.
SELECT * FROM (
SELECT row_number() OVER (PARTITION BY id ORDER BY mydate DESC) as rn,
t1.* FROM xml_files t1
AND t1.insert_status IS NULL
AND t1.section_name='users' AND t1.region IN ('Moscow') AND t1.xml_date >= to_date('2016', 'YYYY') AND t1.parsing_status IS NULL
) t
WHERE rn = 1select
region_code, ...
sum(case when status_id = 2 then 1 else 0 end) - sum(case when consul_need_id = 3 then 1 else 0 end) as diff, ...select t.*,
"Всего детей",
"Всего детей" - "18.1",
"18.1" * "До года жизни" / ("7.1" + "8.1")
...
from (
select
region_code, sum(case when age_group_id < 13 then 1 else 0 end) as "До года жизни",
....
) t(sum(case when ... end),0) - зачем оно вам? Утилита pg_restore предназначена для восстановления базы данных Postgres Pro из архива, созданного командой pg_dump в любом из не текстовых форматов
<. Примерно:C:\Program Files\...\psql.exe --host ... --dbname "demo" < "C:\\Users\\...\\....SQL"SELECT Company, SUM(CASE WHEN IsDiscounted THEN Price ELSE -Price END) ...create table products (
id serial primary key,
name text not null
);
create table products_attributes (
id serial primary key,
attribute_id int not null,
product_id int not null references products (id),
value double precision
);
-- 10 млн продуктов
insert into products (name)
select 'p' || n from generate_series(1, 10000000) n;
-- в среднем по 10 аттрибутов на продукт
-- всего 20 разных аттрибутов
-- значения - случайные из 1-1000
insert into products_attributes(attribute_id, product_id, value)
select a,
p.id,
trunc(random() * 1000)
from products p, generate_series(1, 20) a
where random() < 0.5;
create index ix_attr_attribute_id on products_attributes(attribute_id);
create index ix_attr_product_id on products_attributes(product_id);
create unique index uk_attr_attr_product on products_attributes(product_id, attribute_id);
create index ix_attr_value on products_attributes(value);
-- немного статистики
select attribute_id, count(*) from products_attributes group by 1 order by 1;
attribute_id | count
--------------+---------
1 | 5001345
2 | 5001937
3 | 4998754
4 | 4998706
5 | 4999357
6 | 5004465
7 | 4999215
...
select product_id, count(*) from products_attributes group by 1 order by 2 desc limit 20;
product_id | count
------------+-------
4769292 | 20
5366802 | 20
7241348 | 20
3019891 | 20
7789046 | 20
1688646 | 19
1585970 | 19
...
SELECT count(*) FROM products_attributes WHERE attribute_id = 1 AND value BETWEEN 1 AND 400;
count
---------
1999212
(1 row)
SELECT count(*) FROM products_attributes WHERE attribute_id = 2 AND value BETWEEN 1 AND 400;
count
---------
1999385
(1 row)
SELECT count(*) FROM products_attributes WHERE attribute_id = 3 AND value BETWEEN 20 AND 30;
count
-------
55318
(1 row)explain analyze
select * from products
where id in (
select product_id from products_attributes
where attribute_id = 1 AND value BETWEEN 1 AND 400
)
and id in (
select product_id from products_attributes
where attribute_id = 2 AND value BETWEEN 1 AND 400
)
and id in (
select product_id from products_attributes
where attribute_id = 3 AND value BETWEEN 20 AND 30
);QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=164603.86..347381.12 rows=2047 width=12) (actual time=680.688..3753.927 rows=2189 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop (cost=163603.86..346176.42 rows=853 width=12) (actual time=652.714..3713.617 rows=730 loops=3)
-> Nested Loop (cost=163603.43..341334.66 rows=762 width=12) (actual time=652.439..3294.369 rows=730 loops=3)
-> Parallel Hash Join (cost=163602.86..313081.39 rows=4167 width=8) (actual time=649.437..1742.210 rows=3689 loops=3)
Hash Cond: (products_attributes_1.product_id = products_attributes_2.product_id)
-> Parallel Index Scan using ix_attr_attribute_id on products_attributes products_attributes_1 (cost=0.57..147444.27 rows=775173 width=4) (actual time=0.577..880.079 rows=666462 loops=3)
Index Cond: (attribute_id = 2)
Filter: ((value >= '1'::double precision) AND (value <= '400'::double precision))
Rows Removed by Filter: 1000851
-> Parallel Hash (cost=163306.17..163306.17 rows=23690 width=4) (actual time=647.483..647.483 rows=18439 loops=3)
Buckets: 65536 Batches: 1 Memory Usage: 2752kB
-> Parallel Index Scan using ix_attr_attribute_id on products_attributes products_attributes_2 (cost=0.57..163306.17 rows=23690 width=4) (actual time=18.296..639.541 rows=18439 loops=3)
Index Cond: (attribute_id = 3)
Filter: ((value >= '20'::double precision) AND (value <= '30'::double precision))
Rows Removed by Filter: 1647812
-> Index Scan using uk_attr_attr_product on products_attributes (cost=0.57..6.78 rows=1 width=4) (actual time=0.420..0.420 rows=0 loops=11066)
Index Cond: ((product_id = products_attributes_1.product_id) AND (attribute_id = 1))
Filter: ((value >= '1'::double precision) AND (value <= '400'::double precision))
Rows Removed by Filter: 0
-> Index Scan using products_pkey on products (cost=0.43..6.35 rows=1 width=12) (actual time=0.572..0.572 rows=1 loops=2189)
Index Cond: (id = products_attributes.product_id)
Planning Time: 4.481 ms
JIT:
Functions: 93
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 14.220 ms, Inlining 0.000 ms, Optimization 2.852 ms, Emission 50.889 ms, Total 67.961 ms
Execution Time: 3762.035 ms
(29 rows)explain analyze
select * from products
where id in (
select product_id from products_attributes
where attribute_id = 1 AND value BETWEEN 1 AND 400
)
and id in (
select product_id from products_attributes
where attribute_id = 2 AND value BETWEEN 1 AND 400
)
and id in (
select product_id from products_attributes
where attribute_id = 3 AND value BETWEEN 20 AND 30
) limit 20;QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2.14..7292.14 rows=20 width=12) (actual time=3.743..47.724 rows=20 loops=1)
-> Nested Loop (cost=2.14..746133.39 rows=2047 width=12) (actual time=3.741..47.714 rows=20 loops=1)
-> Nested Loop (cost=1.70..734511.89 rows=1829 width=12) (actual time=3.313..44.710 rows=20 loops=1)
-> Nested Loop (cost=1.14..666709.45 rows=10000 width=8) (actual time=2.377..42.276 rows=95 loops=1)
-> Index Scan using ix_attr_attribute_id on products_attributes products_attributes_2 (cost=0.57..209246.27 rows=56856 width=4) (actual time=1.250..14.969 rows=511 loops=1)
Index Cond: (attribute_id = 3)
Filter: ((value >= '20'::double precision) AND (value <= '30'::double precision))
Rows Removed by Filter: 46859
-> Index Scan using uk_attr_attr_product on products_attributes products_attributes_1 (cost=0.57..8.05 rows=1 width=4) (actual time=0.053..0.053 rows=0 loops=511)
Index Cond: ((product_id = products_attributes_2.product_id) AND (attribute_id = 2))
Filter: ((value >= '1'::double precision) AND (value <= '400'::double precision))
Rows Removed by Filter: 0
-> Index Scan using uk_attr_attr_product on products_attributes (cost=0.57..6.78 rows=1 width=4) (actual time=0.025..0.025 rows=0 loops=95)
Index Cond: ((product_id = products_attributes_1.product_id) AND (attribute_id = 1))
Filter: ((value >= '1'::double precision) AND (value <= '400'::double precision))
Rows Removed by Filter: 0
-> Index Scan using products_pkey on products (cost=0.43..6.35 rows=1 width=12) (actual time=0.149..0.149 rows=1 loops=20)
Index Cond: (id = products_attributes.product_id)
Planning Time: 9.959 ms
Execution Time: 47.796 ms
(20 rows)create table t (f jsonb);
insert into t values ('{"t_in":[0],"t_out":[1]}');
# table t;
f
-----------------------------
{"t_in": [0], "t_out": [1]}
# select f->'t_in' from t;
?column?
----------
[0]
(1 row)select '{"t_in":[0],"t_out":[1]}'::jsonb->'t_in'; len из символов алфавита chars:create or replace function rand_str(len int, chars text default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') returns text language sql as $$
SELECT string_agg (substr(chars, ceil (random() * length(chars))::integer, 1), '')
FROM generate_series(1, len)
$$;postgres=# select rand_str(10);
rand_str
------------
wmIn59AeiS
(1 row)
postgres=# select rand_str(50, 'абвгдежзиклмнопрстуфхцчшщъыьэюя');
rand_str
----------------------------------------------------
дломчяочэатьщсдмллянблнцммюгььжзетшбзвшлежлэщдечют
(1 row)v = {'_id': 10, 'param_name': None}
c.mogrify("SELECT * FROM table_name WHERE %(param_name)s IS NULL OR id = ANY(%(param_name)s)", v)
# b'SELECT * FROM table_name WHERE NULL IS NULL OR id = ANY(NULL)'
v = {"_id": 10, "param_name": [1, 2, 3]}
# b'SELECT * FROM table_name WHERE ARRAY[1,2,3] IS NULL OR id = ANY(ARRAY[1,2,3])'v = {"_id": 10, "param_name": []}
c.mogrify("SELECT * FROM table_name WHERE cardinality(%(param_name)s::int[]) = 0 OR id = ANY(%(param_name)s)", v)
# b"SELECT * FROM table_name WHERE cardinality('{}'::int[]) = 0 OR id = ANY('{}')"do $$
declare
list int[] := array[2619,1247,4159];
r text;
begin
select string_agg(relname, ',') into r from pg_class where oid = any(list);
raise notice '%', r;
end;
$$;
# NOTICE: pg_statistic,pg_type,pg_toast_2600> \set list 2619,1247,4159
> select * from pg_class where oid in (:list);