Мы тут должны гадать о причинах? Телепатов в Гражданскую бледнорожие с краснопузыми на пару шашками порубали.

+100500 - аккумуляторы хрустального шара подсели - укажите текст ошибки

Добрый день.

Есть таблица, запросы на которую выполняются слишком долго.

Судя по плану проблема в том, что все данные читаются с диска всегда

"Total Cost": 461705.93,
 "Plan Rows": 166893,
"Shared Hit Blocks": 6286,
"Shared Read Blocks": 453751,

Есть другие таблицы, больше в несколько раз, но с ними подобных проблем нет:

"Total Cost": 38801.21,
"Plan Rows": 742621,
"Shared Hit Blocks": 31375,
"Shared Read Blocks": 0,

Никто не сталкивался с подобным? Спасибо

"Total Cost": 461705.93,
 "Plan Rows": 166893,
"Shared Hit Blocks": 6286,
"Shared Read Blocks": 453751,

"Total Cost": 38801.21,
"Plan Rows": 742621,
"Shared Hit Blocks": 31375,
"Shared Read Blocks": 0,

Покажите результат

\d+ таблица

и EXPLAIN (ANALYZE, BUFFERS) какого-то из запросов. И какая это версия PostgreSQL?

Добрый день. Чем может грозить предупреждение : tables declared with oids are not supported, ignored

\d+ таблица

9.6 версия

                                                   Table "public.table"
      Column      |   Type    |                        Modifiers                        | Storage  | Stats target | Description
------------------+-----------+---------------------------------------------------------+----------+--------------+-------------
 id               | bigint    | not null default nextval('table_id_seq'::regclass)      | plain    |              |
 c1               | integer[] |                                                         | extended |              |
 c2               | integer   |                                                         | plain    |              |
 c3               | bigint    |                                                         | plain    |              |
 c4               | bigint    |                                                         | plain    |              |
 c5               | bigint    |                                                         | plain    |              |
 c6               | bigint    |                                                         | plain    |              |
 c7               | integer   |                                                         | plain    |              |
 c8               | integer   |                                                         | plain    |              |
 c9               | boolean   |                                                         | plain    |              |
 c10              | boolean   |                                                         | plain    |              |
 c11_id           | integer   |                                                         | plain    |              |
 c12_id           | bigint    |                                                         | plain    |              |
 c13_id           | bigint    |                                                         | plain    |              |
 c14              | boolean   |                                                         | plain    |              |
Indexes:
    "table_pkey" PRIMARY KEY, btree (id)
    "c3c4_idx" btree (c3, c4)

[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Parallel Aware": false,
      "Relation Name": "table",
      "Schema": "public",
      "Alias": "table",
      "Startup Cost": 0,
      "Total Cost": 461705.93,
      "Plan Rows": 166893,
      "Plan Width": 100,
      "Actual Startup Time": 40306.758,
      "Actual Total Time": 43905.994,
      "Actual Rows": 168103,
      "Actual Loops": 1,
      "Output": [
        "id",
        "c1",
        "c2",
        "c3",
        "c4",
        "c5",
        "c6",
        "c7",
        "c8",
        "c9",
        "c10",
        "c11_id",
        "c12_id",
        "c13_id",
        "c14"
      ],
      "Shared Hit Blocks": 5566,
      "Shared Read Blocks": 454471,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning Time": 0.055,
    "Triggers": [],
    "Execution Time": 43914.751
  }
]

                                                   Table "public.table"
      Column      |   Type    |                        Modifiers                        | Storage  | Stats target | Description
------------------+-----------+---------------------------------------------------------+----------+--------------+-------------
 id               | bigint    | not null default nextval('table_id_seq'::regclass)      | plain    |              |
 c1               | integer[] |                                                         | extended |              |
 c2               | integer   |                                                         | plain    |              |
 c3               | bigint    |                                                         | plain    |              |
 c4               | bigint    |                                                         | plain    |              |
 c5               | bigint    |                                                         | plain    |              |
 c6               | bigint    |                                                         | plain    |              |
 c7               | integer   |                                                         | plain    |              |
 c8               | integer   |                                                         | plain    |              |
 c9               | boolean   |                                                         | plain    |              |
 c10              | boolean   |                                                         | plain    |              |
 c11_id           | integer   |                                                         | plain    |              |
 c12_id           | bigint    |                                                         | plain    |              |
 c13_id           | bigint    |                                                         | plain    |              |
 c14              | boolean   |                                                         | plain    |              |
Indexes:
    "table_pkey" PRIMARY KEY, btree (id)
    "c3c4_idx" btree (c3, c4)

[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Parallel Aware": false,
      "Relation Name": "table",
      "Schema": "public",
      "Alias": "table",
      "Startup Cost": 0,
      "Total Cost": 461705.93,
      "Plan Rows": 166893,
      "Plan Width": 100,
      "Actual Startup Time": 40306.758,
      "Actual Total Time": 43905.994,
      "Actual Rows": 168103,
      "Actual Loops": 1,
      "Output": [
        "id",
        "c1",
        "c2",
        "c3",
        "c4",
        "c5",
        "c6",
        "c7",
        "c8",
        "c9",
        "c10",
        "c11_id",
        "c12_id",
        "c13_id",
        "c14"
      ],
      "Shared Hit Blocks": 5566,
      "Shared Read Blocks": 454471,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning Time": 0.055,
    "Triggers": [],
    "Execution Time": 43914.751
  }
]

Это не "EXPLAIN (ANALYZE, BUFFERS)" — зачем тут этот JSON? ;)
И что это за запрос? "SELECT * FROM table"?

И что касается "другие таблицы, больше в несколько раз, но с ними подобных проблем нет" — можете показать то, что я просил раньше, для какой-то из них?

"Seq Scan on table  (cost=0.00..461705.93 rows=166893 width=100) (actual time=40245.945..43832.027 rows=168103 loops=1)"
"  Buffers: shared hit=5662 read=454375"
"Planning time: 0.086 ms"
"Execution time: 43840.624 ms"

Да, SELECT * FROM table

Это для таблицы, с которой такого нет

"Seq Scan on table_fast  (cost=0.00..38801.21 rows=742621 width=1711) (actual time=0.037..393.563 rows=846456 loops=1)"
"  Buffers: shared hit=31375"
"Planning time: 0.063 ms"
"Execution time: 437.492 ms"

                                                       Table "public.table_fast"
   Column    |          Type          |                     Modifiers                      | Storage  | Stats target | Description
-------------+------------------------+----------------------------------------------------+----------+--------------+-------------
 id          | bigint                 | not null default nextval('table_fast_id_seq'::regclass) | plain    |              |
 t1          | text                   |                                                    | extended |              |
 t2          | boolean                |                                                    | plain    |              |
 t3          | boolean                |                                                    | plain    |              |
 t4          | integer                |                                                    | plain    |              |
 t5          | character varying(255) |                                                    | extended |              |
 t6          | bigint                 |                                                    | plain    |              |
 t7          | bigint                 |                                                    | plain    |              |
 t8          | character varying(255) |                                                    | extended |              |
 t9          | character varying(255) |                                                    | extended |              |
 t10         | character varying(255) |                                                    | extended |              |
 t11         | date                   |                                                    | plain    |              |
Indexes:
    "table_fast_pkey" PRIMARY KEY, btree (id)
    "table_fast_t7" btree (t7, t2)
    "table_fast_index" btree (t6, t3)
Foreign-key constraints:
    "table_fast_t7_fkey" FOREIGN KEY (t7) REFERENCES b(id) ON UPDATE CASCADE ON DELETE SET NULL
    "table_fast_t6_fkey" FOREIGN KEY (t6) REFERENCES l(id) ON UPDATE CASCADE ON DELETE SET NULL

"Seq Scan on table  (cost=0.00..461705.93 rows=166893 width=100) (actual time=40245.945..43832.027 rows=168103 loops=1)"
"  Buffers: shared hit=5662 read=454375"
"Planning time: 0.086 ms"
"Execution time: 43840.624 ms"

"Seq Scan on table_fast  (cost=0.00..38801.21 rows=742621 width=1711) (actual time=0.037..393.563 rows=846456 loops=1)"
"  Buffers: shared hit=31375"
"Planning time: 0.063 ms"
"Execution time: 437.492 ms"

                                                       Table "public.table_fast"
   Column    |          Type          |                     Modifiers                      | Storage  | Stats target | Description
-------------+------------------------+----------------------------------------------------+----------+--------------+-------------
 id          | bigint                 | not null default nextval('table_fast_id_seq'::regclass) | plain    |              |
 t1          | text                   |                                                    | extended |              |
 t2          | boolean                |                                                    | plain    |              |
 t3          | boolean                |                                                    | plain    |              |
 t4          | integer                |                                                    | plain    |              |
 t5          | character varying(255) |                                                    | extended |              |
 t6          | bigint                 |                                                    | plain    |              |
 t7          | bigint                 |                                                    | plain    |              |
 t8          | character varying(255) |                                                    | extended |              |
 t9          | character varying(255) |                                                    | extended |              |
 t10         | character varying(255) |                                                    | extended |              |
 t11         | date                   |                                                    | plain    |              |
Indexes:
    "table_fast_pkey" PRIMARY KEY, btree (id)
    "table_fast_t7" btree (t7, t2)
    "table_fast_index" btree (t6, t3)
Foreign-key constraints:
    "table_fast_t7_fkey" FOREIGN KEY (t7) REFERENCES b(id) ON UPDATE CASCADE ON DELETE SET NULL
    "table_fast_t6_fkey" FOREIGN KEY (t6) REFERENCES l(id) ON UPDATE CASCADE ON DELETE SET NULL

Ну так эта вторая таблица, наоборот, намного (почти в 15 раз) меньше по размеру (можете посмотреть сами с помощью \dt+).
Т.е. то, что тут происходит — это использование bulk-read access strategy (т.е. перезаписываемого буфера небольшого размера) для того, чтобы страницы других таблиц не "вымывались" из shared buffers.

Прошу подсказать, необходимо выполнить копирование данных из одной таблицы в другую БД, это не проблема, делаю таким образом:

psql -h localhost -U postgres NAME_DB -c "copy a_product_volume_settings to stdout" | psql -h IP -p 5432 NAME_DB -U postgres -W -c "copy a_product_volume_settings from stdin"

Проблема в том, что необходимо, чтобы вторая таблица была неким архивом т.е. чтобы в неё попадали все изменения, если я повторно выполняю указанную команду, то данные просто добавляются без проверки на наличие уже существующих записей.

Всем привет!

А как отследить накат WALов при восстановлении? Интересует конкретно на какую точку во времени мы сейчас восстановились.

В логе обычно пишет

В 9.6 просто так не показывает. Вот хочется узнать, от какого времени из прошлого эти WAL'ы

Получается размер слишком большой, чтобы поместить в shared buffers, поэтому всегда читает с диска. Спасибо за помощь. Только непонятно как

70570092  {2}  1  1399472  1754582  67640  67640  21  26    true    60000349    true - такие данные могут занять 3.5 гига. Случайно нет каких-то особенностей с удалением из таблицы? Что данные продолжают занимать место

> Получается размер слишком большой, чтобы поместить в shared buffers

Не совсем так. Поместить, может, и можно было бы, но это было бы глупо, потому что этот "выигрыш" был бы достигнут за счёт других таблиц (и запросов, соответственно).

> Случайно нет каких-то особенностей с удалением из таблицы?

Да, есть, конечно — удаление ничего физически не сжимает и не удаляет, а для вставки новых записей на места тех, что помечены как удалённые, может быть нужен (auto)vacuum.
Найдите какой-нибудь запрос для вывода bloat (их есть в google / на PostgreSQL wiki), и посмотрите на эту таблицу.
А про autovacuum — проверьте по pg_stat_user_tables, когда она там в последний раз обрабатывалась и т.п.

столкнулся с аномально долгим чтением маааленькой таблицы.. 30 строк, а читается по explane analyze секунды. причём после vacuum full

pg 12

Ну так показали бы этот EXPLAIN (ANALYZE, BUFFERS)...

ну там seq scan 30 rows ~3000ms