search

postgrespro / pg_pathman

Partitioning tool for PostgreSQL
Other
580 stars 67 forks source link

Долгий одиночный insert (online_analyze) #113

Open sgrinko opened 7 years ago

sgrinko commented 7 years ago

Работаю на следующих версиях:

select get_pathman_lib_version()
10402

select pgpro_version()
pgpro_version
PostgresPro 9.6.3.3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit

использую RANGE секционирование 1 месяц. Выполняю такой код: на таблице base.fact_events сейчас 68 секций структура таблицы

CREATE TABLE base.fact_events
(
  id bigserial NOT NULL,
  rgu_service_id integer,
  rgu_authority_id integer,
  status integer,
  review_status integer,
  created_at timestamp without time zone NOT NULL DEFAULT now(),
  updated_at timestamp without time zone NOT NULL,
  deleted_at timestamp without time zone,
  event_date date,
  token character varying(50),
  mobile character varying(25),
  email character varying(128),
  CONSTRAINT fact_events_pkey PRIMARY KEY (id)
);

CREATE INDEX index_fact_events_on_created_at
  ON base.fact_events
  USING btree
  (created_at);
...
другие индексы

индекс по полю created_at есть на таблицах наследования

CREATE TABLE base.fact_events_11
(
-- Inherited from table base.fact_events:  id bigint NOT NULL DEFAULT nextval('base.fact_events_id_seq'::regclass),
-- Inherited from table base.fact_events:  rgu_service_id integer,
-- Inherited from table base.fact_events:  rgu_authority_id integer,
-- Inherited from table base.fact_events:  status integer,
-- Inherited from table base.fact_events:  review_status integer,
-- Inherited from table base.fact_events:  created_at timestamp without time zone NOT NULL DEFAULT now(),
-- Inherited from table base.fact_events:  updated_at timestamp without time zone NOT NULL,
-- Inherited from table base.fact_events:  deleted_at timestamp without time zone,
-- Inherited from table base.fact_events:  event_date date,
-- Inherited from table base.fact_events:  token character varying(50),
-- Inherited from table base.fact_events:  mobile character varying(25),
-- Inherited from table base.fact_events:  email character varying(128),
  CONSTRAINT fact_events_11_pkey PRIMARY KEY (id)
  CONSTRAINT pathman_fact_events_11_check CHECK (created_at >= '2012-11-01 00:00:00'::timestamp without time zone AND created_at < '2012-12-01 00:00:00'::timestamp without time zone)
)
INHERITS (base.fact_events)
WITH (
  OIDS=FALSE
);

CREATE INDEX fact_events_11_created_at_idx
  ON base.fact_events_11
  USING btree
  (created_at);
...
другие индексы

данные в конфиге

select * from public.pathman_config
partrel         base.fact_events
expr            created_at
parttype        2
range_interval  1 mon
cooked_expr     {VAR :varno 1 :varattno 6 :vartype 1114 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 6 :location 8}

select * from public.pathman_config_params
partrel               enable_parent   auto   init_callback     spawn_using_bgw
base.fact_events       t                  t       <NULL>               f

до этого пакетно в таблицу вставилось 

select count(*) from base.fact_events
count
22801606

и потом конкуретно выполнено партиционирование. после этого был выполнен VACUUM FULL FREEZE ANALYZE base.fact_events; главная таблицы после этого имеет размер 48кб и 0 строк. Все данные распределены по секциям.

выполняю вставку 1 строки сначала смотрим план:

explain(COSTS, BUFFERS, TIMING, ANALYZE, VERBOSE)
INSERT INTO base.fact_events (rgu_service_id, rgu_authority_id, created_at, updated_at) 
VALUES (165708, 91643, '2017-08-14 08:52:23.798822', '2017-08-14 08:52:23.798822') 
RETURNING id;

QUERY PLAN
Insert on base.fact_events  (cost=0.00..0.01 rows=1 width=148) (actual time=1.313..1.314 rows=1 loops=1)
  Output: id
  Buffers: shared hit=22 dirtied=9
  ->  Custom Scan (PartitionFilter)  (cost=0.00..0.01 rows=1 width=148) (actual time=0.051..0.051 rows=1 loops=1)
        Output: (nextval('base.fact_events_id_seq'::regclass)), (165708), (91643), (NULL::integer), (NULL::integer), ('2017-08-14 08:52:23.798822'::timestamp without time zone), ('2017-08-14 08:52:23.798822'::timestamp without time zone), (NULL::timestamp without time zone), (NULL::date), (NULL::character varying), (NULL::character varying), (NULL::character varying)
        Buffers: shared hit=2 dirtied=1
        ->  Result  (cost=0.00..0.01 rows=1 width=148) (actual time=0.024..0.024 rows=1 loops=1)
              Output: nextval('base.fact_events_id_seq'::regclass), 165708, 91643, NULL::integer, NULL::integer, '2017-08-14 08:52:23.798822'::timestamp without time zone, '2017-08-14 08:52:23.798822'::timestamp without time zone, NULL::timestamp without time zone, NULL::date, NULL::character varying, NULL::character varying, NULL::character varying
              Buffers: shared hit=2 dirtied=1
Planning time: 0.042 ms
Trigger RI_ConstraintTrigger_c_193188 for constraint fact_events_68_rgu_service_id_fkey on fact_events_68: time=0.323 calls=1
Trigger RI_ConstraintTrigger_c_193193 for constraint fact_events_68_rgu_authority_id_fkey on fact_events_68: time=0.270 calls=1
Execution time: 886.790 ms

теперь выполняем код в транзакции

begin;

INSERT INTO base.fact_events (rgu_service_id, rgu_authority_id, created_at, updated_at) 
VALUES (165708, 91643, '2017-08-14 08:52:23.798822', '2017-08-14 08:52:23.798822') 
RETURNING id;

rollback;

результат:

INFO:  analyzing "base.fact_events"
INFO:  "fact_events": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "base.fact_events" inheritance tree
INFO:  "fact_events_11": scanned 1 of 2 pages, containing 97 live rows and 0 dead rows; 1 rows in sample, 166 estimated total rows
INFO:  "fact_events_12": scanned 1 of 2 pages, containing 97 live rows and 0 dead rows; 1 rows in sample, 183 estimated total rows
INFO:  "fact_events_16": scanned 579 of 1161 pages, containing 45154 live rows and 0 dead rows; 579 rows in sample, 90592 estimated total rows
INFO:  "fact_events_17": scanned 998 of 2002 pages, containing 77850 live rows and 0 dead rows; 998 rows in sample, 156178 estimated total rows
INFO:  "fact_events_18": scanned 458 of 918 pages, containing 35875 live rows and 0 dead rows; 458 rows in sample, 71894 estimated total rows
INFO:  "fact_events_19": scanned 786 of 1576 pages, containing 61423 live rows and 0 dead rows; 786 rows in sample, 123166 estimated total rows
INFO:  "fact_events_20": scanned 719 of 1442 pages, containing 56102 live rows and 0 dead rows; 719 rows in sample, 112527 estimated total rows
INFO:  "fact_events_21": scanned 865 of 1735 pages, containing 67537 live rows and 0 dead rows; 865 rows in sample, 135495 estimated total rows
INFO:  "fact_events_22": scanned 815 of 1636 pages, containing 63498 live rows and 0 dead rows; 815 rows in sample, 127520 estimated total rows
INFO:  "fact_events_23": scanned 824 of 1654 pages, containing 64561 live rows and 0 dead rows; 824 rows in sample, 129672 estimated total rows
INFO:  "fact_events_24": scanned 459 of 921 pages, containing 35820 live rows and 0 dead rows; 459 rows in sample, 71905 estimated total rows
INFO:  "fact_events_25": scanned 487 of 978 pages, containing 38024 live rows and 0 dead rows; 487 rows in sample, 76353 estimated total rows
INFO:  "fact_events_26": scanned 750 of 1505 pages, containing 58605 live rows and 0 dead rows; 750 rows in sample, 117600 estimated total rows
INFO:  "fact_events_27": scanned 590 of 1183 pages, containing 46119 live rows and 0 dead rows; 590 rows in sample, 92462 estimated total rows
INFO:  "fact_events_28": scanned 555 of 1113 pages, containing 43373 live rows and 0 dead rows; 555 rows in sample, 87041 estimated total rows
INFO:  "fact_events_29": scanned 3000 of 6019 pages, containing 284326 live rows and 0 dead rows; 3000 rows in sample, 570828 estimated total rows
INFO:  "fact_events_30": scanned 1980 of 3973 pages, containing 187429 live rows and 0 dead rows; 1980 rows in sample, 376179 estimated total rows
INFO:  "fact_events_31": scanned 2399 of 4813 pages, containing 227117 live rows and 0 dead rows; 2399 rows in sample, 455462 estimated total rows
INFO:  "fact_events_32": scanned 2586 of 5187 pages, containing 239678 live rows and 0 dead rows; 2586 rows in sample, 480657 estimated total rows
INFO:  "fact_events_33": scanned 3179 of 6377 pages, containing 299436 live rows and 0 dead rows; 3179 rows in sample, 600662 estimated total rows
INFO:  "fact_events_34": scanned 2642 of 5301 pages, containing 254842 live rows and 0 dead rows; 2642 rows in sample, 511321 estimated total rows
INFO:  "fact_events_35": scanned 3216 of 6452 pages, containing 310877 live rows and 0 dead rows; 3216 rows in sample, 623762 estimated total rows
INFO:  "fact_events_36": scanned 4207 of 8440 pages, containing 399773 live rows and 0 dead rows; 4207 rows in sample, 802087 estimated total rows
INFO:  "fact_events_37": scanned 1908 of 3827 pages, containing 183399 live rows and 0 dead rows; 1908 rows in sample, 367822 estimated total rows
INFO:  "fact_events_38": scanned 2138 of 4290 pages, containing 205421 live rows and 0 dead rows; 2138 rows in sample, 412173 estimated total rows
INFO:  "fact_events_39": scanned 2786 of 5589 pages, containing 266143 live rows and 0 dead rows; 2786 rows in sample, 534013 estimated total rows
INFO:  "fact_events_40": scanned 2786 of 5589 pages, containing 264590 live rows and 0 dead rows; 2786 rows in sample, 531007 estimated total rows
INFO:  "fact_events_41": scanned 2769 of 5555 pages, containing 262066 live rows and 0 dead rows; 2769 rows in sample, 525909 estimated total rows
INFO:  "fact_events_42": scanned 2615 of 5247 pages, containing 248462 live rows and 0 dead rows; 2615 rows in sample, 498418 estimated total rows
INFO:  "fact_events_43": scanned 1183 of 2374 pages, containing 113432 live rows and 0 dead rows; 1183 rows in sample, 227606 estimated total rows
INFO:  "fact_events_44": scanned 434 of 871 pages, containing 42072 live rows and 0 dead rows; 434 rows in sample, 84404 estimated total rows
INFO:  "fact_events_45": scanned 539 of 1081 pages, containing 51964 live rows and 0 dead rows; 539 rows in sample, 104190 estimated total rows
INFO:  "fact_events_46": scanned 1108 of 2223 pages, containing 102339 live rows and 0 dead rows; 1108 rows in sample, 205433 estimated total rows
INFO:  "fact_events_47": scanned 1213 of 2434 pages, containing 113776 live rows and 0 dead rows; 1213 rows in sample, 228022 estimated total rows
INFO:  "fact_events_48": scanned 1297 of 2603 pages, containing 121951 live rows and 0 dead rows; 1297 rows in sample, 244601 estimated total rows
INFO:  "fact_events_49": scanned 1233 of 2474 pages, containing 113970 live rows and 0 dead rows; 1233 rows in sample, 228811 estimated total rows
INFO:  "fact_events_50": scanned 1609 of 3228 pages, containing 155339 live rows and 0 dead rows; 1609 rows in sample, 311778 estimated total rows
INFO:  "fact_events_51": scanned 3711 of 7445 pages, containing 359550 live rows and 0 dead rows; 3711 rows in sample, 721346 estimated total rows
INFO:  "fact_events_52": scanned 5686 of 11407 pages, containing 551053 live rows and 0 dead rows; 5686 rows in sample, 1105516 estimated total rows
INFO:  "fact_events_53": scanned 3921 of 7866 pages, containing 379556 live rows and 0 dead rows; 3921 rows in sample, 761444 estimated total rows
INFO:  "fact_events_54": scanned 4266 of 8559 pages, containing 413235 live rows and 0 dead rows; 4266 rows in sample, 829050 estimated total rows
INFO:  "fact_events_55": scanned 4237 of 8500 pages, containing 410090 live rows and 0 dead rows; 4237 rows in sample, 822742 estimated total rows
INFO:  "fact_events_56": scanned 4667 of 9362 pages, containing 451870 live rows and 0 dead rows; 4667 rows in sample, 906483 estimated total rows
INFO:  "fact_events_57": scanned 3906 of 7836 pages, containing 378153 live rows and 0 dead rows; 3906 rows in sample, 758625 estimated total rows
INFO:  "fact_events_58": scanned 4176 of 8377 pages, containing 404405 live rows and 0 dead rows; 4176 rows in sample, 811217 estimated total rows
INFO:  "fact_events_59": scanned 4077 of 8180 pages, containing 394670 live rows and 0 dead rows; 4077 rows in sample, 791919 estimated total rows
INFO:  "fact_events_60": scanned 5207 of 10447 pages, containing 504102 live rows and 0 dead rows; 5207 rows in sample, 1011430 estimated total rows
INFO:  "fact_events_61": scanned 3090 of 6200 pages, containing 299062 live rows and 0 dead rows; 3090 rows in sample, 600125 estimated total rows
INFO:  "fact_events_62": scanned 2929 of 5876 pages, containing 283054 live rows and 0 dead rows; 2929 rows in sample, 567871 estimated total rows
INFO:  "fact_events_63": scanned 3385 of 6790 pages, containing 327382 live rows and 0 dead rows; 3385 rows in sample, 656647 estimated total rows
INFO:  "fact_events_64": scanned 3478 of 6977 pages, containing 336402 live rows and 0 dead rows; 3478 rows in sample, 674748 estimated total rows
INFO:  "fact_events_65": scanned 2176 of 4366 pages, containing 210441 live rows and 0 dead rows; 2176 rows in sample, 422277 estimated total rows
INFO:  "fact_events_66": scanned 2789 of 5595 pages, containing 269767 live rows and 0 dead rows; 2789 rows in sample, 541143 estimated total rows
INFO:  "fact_events_67": scanned 2298 of 4610 pages, containing 222094 live rows and 0 dead rows; 2298 rows in sample, 445542 estimated total rows
INFO:  "fact_events_68": scanned 282 of 566 pages, containing 27277 live rows and 1 dead rows; 282 rows in sample, 54694 estimated total rows
INFO:  analyze "fact_events" took 1.55 seconds

Total query runtime: 1.5 secs
1 row retrieved.

1.5 секунды + идут сканирования всех секций... что я делаю не так?

sgrinko commented 7 years ago

попробовал убрать родителя из анализа.... select set_enable_parent('base.fact_events'::REGCLASS, false)

сначала смотрим план:

explain(COSTS, BUFFERS, TIMING, ANALYZE, VERBOSE)
INSERT INTO base.fact_events (rgu_service_id, rgu_authority_id, created_at, updated_at) 
VALUES (165708, 91643, '2017-08-14 08:52:23.798822', '2017-08-14 08:52:23.798822') 
RETURNING id;

QUERY PLAN
Insert on base.fact_events  (cost=0.00..0.01 rows=1 width=148) (actual time=0.354..0.355 rows=1 loops=1)
  Output: id
  Buffers: shared hit=22 dirtied=1
  ->  Custom Scan (PartitionFilter)  (cost=0.00..0.01 rows=1 width=148) (actual time=0.050..0.051 rows=1 loops=1)
        Output: (nextval('base.fact_events_id_seq'::regclass)), (165708), (91643), (NULL::integer), (NULL::integer), ('2017-08-14 08:52:23.798822'::timestamp without time zone), ('2017-08-14 08:52:23.798822'::timestamp without time zone), (NULL::timestamp without time zone), (NULL::date), (NULL::character varying), (NULL::character varying), (NULL::character varying)
        Buffers: shared hit=2
        ->  Result  (cost=0.00..0.01 rows=1 width=148) (actual time=0.021..0.021 rows=1 loops=1)
              Output: nextval('base.fact_events_id_seq'::regclass), 165708, 91643, NULL::integer, NULL::integer, '2017-08-14 08:52:23.798822'::timestamp without time zone, '2017-08-14 08:52:23.798822'::timestamp without time zone, NULL::timestamp without time zone, NULL::date, NULL::character varying, NULL::character varying, NULL::character varying
              Buffers: shared hit=2
Planning time: 0.054 ms
Trigger RI_ConstraintTrigger_c_193188 for constraint fact_events_68_rgu_service_id_fkey on fact_events_68: time=0.179 calls=1
Trigger RI_ConstraintTrigger_c_193193 for constraint fact_events_68_rgu_authority_id_fkey on fact_events_68: time=0.085 calls=1
Execution time: 829.602 ms

теперь выполняем код в транзакции

begin;

INSERT INTO base.fact_events (rgu_service_id, rgu_authority_id, created_at, updated_at) 
VALUES (165708, 91643, '2017-08-14 08:52:23.798822', '2017-08-14 08:52:23.798822') 
RETURNING id;

rollback;

результат:

INFO:  analyzing "base.fact_events"
INFO:  "fact_events": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "base.fact_events" inheritance tree
INFO:  "fact_events_11": scanned 1 of 2 pages, containing 40 live rows and 0 dead rows; 1 rows in sample, 109 estimated total rows
INFO:  "fact_events_12": scanned 1 of 2 pages, containing 74 live rows and 0 dead rows; 1 rows in sample, 160 estimated total rows
INFO:  "fact_events_16": scanned 579 of 1161 pages, containing 45156 live rows and 0 dead rows; 579 rows in sample, 90594 estimated total rows
INFO:  "fact_events_17": scanned 998 of 2002 pages, containing 77874 live rows and 0 dead rows; 998 rows in sample, 156202 estimated total rows
INFO:  "fact_events_18": scanned 458 of 918 pages, containing 35859 live rows and 0 dead rows; 458 rows in sample, 71878 estimated total rows
INFO:  "fact_events_19": scanned 786 of 1576 pages, containing 61454 live rows and 0 dead rows; 786 rows in sample, 123197 estimated total rows
INFO:  "fact_events_20": scanned 719 of 1442 pages, containing 56117 live rows and 0 dead rows; 719 rows in sample, 112542 estimated total rows
INFO:  "fact_events_21": scanned 865 of 1735 pages, containing 67565 live rows and 0 dead rows; 865 rows in sample, 135523 estimated total rows
INFO:  "fact_events_22": scanned 815 of 1636 pages, containing 63530 live rows and 0 dead rows; 815 rows in sample, 127552 estimated total rows
INFO:  "fact_events_23": scanned 824 of 1654 pages, containing 64640 live rows and 0 dead rows; 824 rows in sample, 129751 estimated total rows
INFO:  "fact_events_24": scanned 459 of 921 pages, containing 35832 live rows and 0 dead rows; 459 rows in sample, 71917 estimated total rows
INFO:  "fact_events_25": scanned 487 of 978 pages, containing 37996 live rows and 0 dead rows; 487 rows in sample, 76325 estimated total rows
INFO:  "fact_events_26": scanned 750 of 1505 pages, containing 58597 live rows and 0 dead rows; 750 rows in sample, 117592 estimated total rows
INFO:  "fact_events_27": scanned 590 of 1183 pages, containing 46080 live rows and 0 dead rows; 590 rows in sample, 92423 estimated total rows
INFO:  "fact_events_28": scanned 555 of 1113 pages, containing 43458 live rows and 0 dead rows; 555 rows in sample, 87126 estimated total rows
INFO:  "fact_events_29": scanned 3000 of 6019 pages, containing 285002 live rows and 0 dead rows; 3000 rows in sample, 571504 estimated total rows
INFO:  "fact_events_30": scanned 1980 of 3973 pages, containing 187504 live rows and 0 dead rows; 1980 rows in sample, 376254 estimated total rows
INFO:  "fact_events_31": scanned 2399 of 4813 pages, containing 226731 live rows and 0 dead rows; 2399 rows in sample, 455076 estimated total rows
INFO:  "fact_events_32": scanned 2586 of 5187 pages, containing 239826 live rows and 0 dead rows; 2586 rows in sample, 480805 estimated total rows
INFO:  "fact_events_33": scanned 3179 of 6377 pages, containing 299072 live rows and 0 dead rows; 3179 rows in sample, 600298 estimated total rows
INFO:  "fact_events_34": scanned 2642 of 5301 pages, containing 254689 live rows and 0 dead rows; 2642 rows in sample, 511168 estimated total rows
INFO:  "fact_events_35": scanned 3216 of 6452 pages, containing 310780 live rows and 0 dead rows; 3216 rows in sample, 623665 estimated total rows
INFO:  "fact_events_36": scanned 4207 of 8440 pages, containing 400185 live rows and 0 dead rows; 4207 rows in sample, 802499 estimated total rows
INFO:  "fact_events_37": scanned 1908 of 3827 pages, containing 183396 live rows and 0 dead rows; 1908 rows in sample, 367819 estimated total rows
INFO:  "fact_events_38": scanned 2138 of 4290 pages, containing 205361 live rows and 0 dead rows; 2138 rows in sample, 412113 estimated total rows
INFO:  "fact_events_39": scanned 2786 of 5589 pages, containing 265858 live rows and 0 dead rows; 2786 rows in sample, 533728 estimated total rows
INFO:  "fact_events_40": scanned 2786 of 5589 pages, containing 264817 live rows and 0 dead rows; 2786 rows in sample, 531234 estimated total rows
INFO:  "fact_events_41": scanned 2769 of 5555 pages, containing 262277 live rows and 0 dead rows; 2769 rows in sample, 526120 estimated total rows
INFO:  "fact_events_42": scanned 2615 of 5247 pages, containing 248475 live rows and 0 dead rows; 2615 rows in sample, 498431 estimated total rows
INFO:  "fact_events_43": scanned 1183 of 2374 pages, containing 113512 live rows and 0 dead rows; 1183 rows in sample, 227686 estimated total rows
INFO:  "fact_events_44": scanned 434 of 871 pages, containing 42082 live rows and 0 dead rows; 434 rows in sample, 84414 estimated total rows
INFO:  "fact_events_45": scanned 539 of 1081 pages, containing 51935 live rows and 0 dead rows; 539 rows in sample, 104161 estimated total rows
INFO:  "fact_events_46": scanned 1108 of 2223 pages, containing 102478 live rows and 0 dead rows; 1108 rows in sample, 205572 estimated total rows
INFO:  "fact_events_47": scanned 1213 of 2434 pages, containing 113504 live rows and 0 dead rows; 1213 rows in sample, 227750 estimated total rows
INFO:  "fact_events_48": scanned 1297 of 2603 pages, containing 121730 live rows and 0 dead rows; 1297 rows in sample, 244380 estimated total rows
INFO:  "fact_events_49": scanned 1233 of 2474 pages, containing 113994 live rows and 0 dead rows; 1233 rows in sample, 228835 estimated total rows
INFO:  "fact_events_50": scanned 1609 of 3228 pages, containing 155489 live rows and 0 dead rows; 1609 rows in sample, 311928 estimated total rows
INFO:  "fact_events_51": scanned 3711 of 7445 pages, containing 359519 live rows and 0 dead rows; 3711 rows in sample, 721315 estimated total rows
INFO:  "fact_events_52": scanned 5686 of 11407 pages, containing 551058 live rows and 0 dead rows; 5686 rows in sample, 1105521 estimated total rows
INFO:  "fact_events_53": scanned 3921 of 7866 pages, containing 379544 live rows and 0 dead rows; 3921 rows in sample, 761432 estimated total rows
INFO:  "fact_events_54": scanned 4266 of 8559 pages, containing 413129 live rows and 0 dead rows; 4266 rows in sample, 828944 estimated total rows
INFO:  "fact_events_55": scanned 4237 of 8500 pages, containing 410146 live rows and 0 dead rows; 4237 rows in sample, 822798 estimated total rows
INFO:  "fact_events_56": scanned 4667 of 9362 pages, containing 451866 live rows and 0 dead rows; 4667 rows in sample, 906479 estimated total rows
INFO:  "fact_events_57": scanned 3906 of 7836 pages, containing 378137 live rows and 0 dead rows; 3906 rows in sample, 758609 estimated total rows
INFO:  "fact_events_58": scanned 4176 of 8377 pages, containing 404407 live rows and 1 dead rows; 4176 rows in sample, 811219 estimated total rows
INFO:  "fact_events_59": scanned 4077 of 8180 pages, containing 394743 live rows and 0 dead rows; 4077 rows in sample, 791992 estimated total rows
INFO:  "fact_events_60": scanned 5207 of 10447 pages, containing 504111 live rows and 0 dead rows; 5207 rows in sample, 1011439 estimated total rows
INFO:  "fact_events_61": scanned 3090 of 6200 pages, containing 299143 live rows and 0 dead rows; 3090 rows in sample, 600206 estimated total rows
INFO:  "fact_events_62": scanned 2929 of 5876 pages, containing 283073 live rows and 0 dead rows; 2929 rows in sample, 567890 estimated total rows
INFO:  "fact_events_63": scanned 3385 of 6790 pages, containing 327427 live rows and 0 dead rows; 3385 rows in sample, 656692 estimated total rows
INFO:  "fact_events_64": scanned 3478 of 6977 pages, containing 336319 live rows and 0 dead rows; 3478 rows in sample, 674665 estimated total rows
INFO:  "fact_events_65": scanned 2176 of 4366 pages, containing 210492 live rows and 0 dead rows; 2176 rows in sample, 422328 estimated total rows
INFO:  "fact_events_66": scanned 2789 of 5595 pages, containing 269718 live rows and 0 dead rows; 2789 rows in sample, 541094 estimated total rows
INFO:  "fact_events_67": scanned 2298 of 4610 pages, containing 222103 live rows and 0 dead rows; 2298 rows in sample, 445551 estimated total rows
INFO:  "fact_events_68": scanned 282 of 566 pages, containing 27271 live rows and 1 dead rows; 282 rows in sample, 54688 estimated total rows
INFO:  analyze "fact_events" took 0.81 seconds

Total query runtime: 843 msec
1 row retrieved.

конечно быстрее, НО причём здесь сканирование всех секций?

funbringer commented 7 years ago

конечно быстрее, НО причём здесь сканирование всех секций?

Это хороший вопрос! Я думаю, его нужно задавать автору расширения online analyze :) Я посмотрю, с чем это может быть связано.

sgrinko commented 7 years ago

Вот оно, что... действительно у меня включено данное расширение

name                                setting
online_analyze.capacity_threshold   100000
online_analyze.enable               on
online_analyze.exclude_tables   
online_analyze.include_tables   
online_analyze.local_tracking       off
online_analyze.lower_limit          0
online_analyze.min_interval         10000
online_analyze.scale_factor         0.1
online_analyze.table_type           all
online_analyze.threshold            50
online_analyze.verbose              on

я попробую его сейчас отключить...

sgrinko commented 7 years ago

я сделал следующее

alter system set online_analyze.enable = off; select pg_reload_conf()

и после этого всё залетало

funbringer commented 7 years ago

и после этого всё залетало

Отлично, но я все же попробую разобраться, может сделаю патч для online_analyze.

sgrinko commented 7 years ago

Да, это было бы очень хорошо. Может быть на пока посоветуете другие параметры для него ...? Чтобы полностью не отключать расширение.... Возможно, что и моя ошибка "bogus" (/issues/112) связана именно с этим :)

funbringer commented 7 years ago

Может быть на пока посоветуете другие параметры для него ...?

Можно указать партицированную таблицу в online_analyze.exclude_tables, чтобы не отключать для всех.

Возможно, что и моя ошибка "bogus" связана именно с этим :)

К сожалению, нет, я воспроизводил с online_analyze + plantuner.

sgrinko commented 7 years ago

Спасибо, будем ждать обновления. Пока отключил через exclude_tables