Hello!
I have got huge planning time for a query in quite small database in PortgreSQL
17
Planning Time: 452.796 ms
Execution Time: 0.350 ms
Tried several version from 17.3 to 17.7 (cpu 2.2GHz) - it almost does not
matter. If I run query many times in row planning time may reduce down to 430ms
but never less.
Tried in PortgreSQL 11 (in a little bit different hardware with cpu 2.60GHz) -
planning time almost ten times less.
Changing parameters: from_collapse_limit, join_collapse_limit, geqo, jit, work_mem and many
others does not help at all. I attach 1. additional setting in the
postgresql.status.conf. 2. querry itself in query.sql. 3. zql plan in
query.sqlplan 4. additioanal information about os, tables etc. would you
please help me -Alexander Kulikov
max_connections = 500
autovacuum_naptime = 20s
bgwriter_delay = 20ms
bgwriter_lru_multiplier = 4.0
bgwriter_lru_maxpages = 400
commit_delay = 1000
shared_buffers = 6GB
effective_cache_size = 16GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500
random_page_cost = 1.1
effective_io_concurrency = 2
from_collapse_limit = 20
join_collapse_limit = 20
max_files_per_process = 8000
max_locks_per_transaction = 512
min_wal_size = 4GB
max_wal_size = 16GB
max_worker_processes = 6
max_parallel_workers_per_gather = 3
max_parallel_workers = 6
max_parallel_maintenance_workers = 3
shared_preload_libraries = 'online_analyze, plantuner, pg_stat_statements'
online_analyze.table_type = none
#------------------------------------------------------------------------------
#Параметры для платформы 1С:Предприятия
#------------------------------------------------------------------------------
standard_conforming_strings = off
escape_string_warning = off
min_wal_size = 1GB
max_wal_size = 2GB
row_security = off
ssl = off
synchronous_commit = off
temp_buffers = 256MB
work_mem = 32MB
#------------------------------------------------------------------------------
# REPORTING AND LOGGING
#------------------------------------------------------------------------------
logging_collector = on # Enable capturing of stderr, jsonlog,
# and csvlog into log files. Required
# to be on for csvlogs and jsonlogs.
# (change requires restart)
# These are only used if logging_collector is on:
log_directory = 'pg_log' # directory where log files are
written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log' # log file name pattern,
# can include strftime() escapes
log_rotation_size = 0 # Automatic rotation of logfiles will
# happen after that much log output.
# 0 disables.
log_file_mode = 0644 # creation mode for log files,
# begin with 0 to use octal notation
log_truncate_on_rotation = on # If on, an existing log file with the
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
log_checkpoints = off
SET client_min_messages=error;
SET enable_mergejoin = off;
SET escape_string_warning = off;
SET cpu_operator_cost = 0.001;
set client_encoding = 'utf8';
SET lock_timeout = 20000;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT
CASE WHEN (EXISTS(SELECT
TRUE AS Q_001_F_000_
FROM _InfoRg8199 T23
LEFT OUTER JOIN _InfoRg10621 T24
ON ((T24._Fld10622RRef = T23._Fld8201RRef) AND (T24._Fld15131RRef =
'\\210@\\000PV\\276\\307<\\021\\360\\244\\243?\\321i\\276'::bytea)) AND
(T24._Fld809 = CAST(0 AS NUMERIC))
WHERE ((T23._Fld809 = CAST(0 AS NUMERIC))) AND ((T23._Fld8200_TYPE =
'\\010'::bytea AND T23._Fld8200_RTRef = '\\000\\000\\000\\177'::bytea AND
T23._Fld8200_RRRef = T1._IDRRef) AND (NOT (((T24._Fld10622RRef IS NULL)))))))
AND COALESCE(T2.SDBL_RLS_SIGNAL_,TRUE) = TRUE AND
COALESCE(T6.SDBL_RLS_SIGNAL_,TRUE) = TRUE AND
COALESCE(T18.SDBL_RLS_SIGNAL_,TRUE) = TRUE THEN TRUE ELSE FALSE END,
T1._IDRRef
FROM _Reference127 T1
LEFT OUTER JOIN (SELECT
CASE WHEN (EXISTS(SELECT
TRUE AS Q_001_F_000_
FROM _InfoRg8199 T4
LEFT OUTER JOIN _InfoRg10621 T5
ON ((T5._Fld10622RRef = T4._Fld8201RRef) AND (T5._Fld15131RRef =
'\\210@\\000PV\\276\\307<\\021\\360\\244\\243?\\321i\\276'::bytea)) AND
(T5._Fld809 = CAST(0 AS NUMERIC))
WHERE ((T4._Fld809 = CAST(0 AS NUMERIC))) AND ((T4._Fld8200_TYPE =
'\\010'::bytea AND T4._Fld8200_RTRef = '\\000\\000\\000\\177'::bytea AND
T4._Fld8200_RRRef = T3._Fld12031RRef) AND (NOT (((T5._Fld10622RRef IS
NULL))))))) THEN TRUE ELSE FALSE END AS SDBL_RLS_SIGNAL_,
T3._Fld809 AS Fld809_,
T3._Fld12031RRef AS Fld12031RRef
FROM _InfoRg12030 T3
WHERE (T3._Fld809 = CAST(0 AS NUMERIC))) T2
ON (T1._IDRRef = T2.Fld12031RRef)
LEFT OUTER JOIN (SELECT
CASE WHEN (EXISTS(SELECT
1
FROM (SELECT 1 AS SDBL_DUMMY) SDBL_DUAL
LEFT OUTER JOIN _Reference312 T8
ON (T7._Fld11507_TYPE = '\\010'::bytea AND T7._Fld11507_RTRef =
'\\000\\000\\0018'::bytea AND T7._Fld11507_RRRef = T8._IDRRef) AND (T8._Fld809
= CAST(0 AS NUMERIC))
LEFT OUTER JOIN _Reference312 T9
ON (T7._Fld11509_TYPE = '\\010'::bytea AND T7._Fld11509_RTRef =
'\\000\\000\\0018'::bytea AND T7._Fld11509_RRRef = T9._IDRRef) AND (T9._Fld809
= CAST(0 AS NUMERIC))
WHERE ((T7._Fld11507_TYPE = '\\010'::bytea AND T7._Fld11507_RTRef =
'\\000\\000\\0018'::bytea) AND EXISTS(SELECT
TRUE AS Q_001_F_000_
FROM _InfoRg8199 T10
LEFT OUTER JOIN _InfoRg10621 T11
ON ((T10._Fld8201RRef = T11._Fld10622RRef)) AND (T11._Fld809 = CAST(0 AS
NUMERIC))
WHERE ((T10._Fld809 = CAST(0 AS NUMERIC))) AND ((T10._Fld8200_TYPE =
(T8._Fld4781_TYPE) AND T10._Fld8200_RTRef = (T8._Fld4781_RTRef) AND
T10._Fld8200_RRRef = (T8._Fld4781_RRRef)) AND (T11._Fld15131RRef =
'\\210@\\000PV\\276\\307<\\021\\360\\244\\243?\\321i\\276'::bytea))) OR (NOT
(((T7._Fld11507_TYPE = '\\010'::bytea AND T7._Fld11507_RTRef =
'\\000\\000\\0018'::bytea)))) AND EXISTS(SELECT
TRUE AS Q_002_F_000_
FROM _InfoRg8199 T12
LEFT OUTER JOIN _InfoRg10621 T13
ON ((T12._Fld8201RRef = T13._Fld10622RRef)) AND (T13._Fld809 = CAST(0 AS
NUMERIC))
WHERE ((T12._Fld809 = CAST(0 AS NUMERIC))) AND ((T12._Fld8200_TYPE =
T7._Fld11507_TYPE AND T12._Fld8200_RTRef = T7._Fld11507_RTRef AND
T12._Fld8200_RRRef = T7._Fld11507_RRRef) AND (T13._Fld15131RRef =
'\\210@\\000PV\\276\\307<\\021\\360\\244\\243?\\321i\\276'::bytea)))) AND
((T7._Fld11509_TYPE = '\\001'::bytea AND T7._Fld11509_RTRef =
'\\000\\000\\000\\000'::bytea AND T7._Fld11509_RRRef =
'\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea)
OR (T7._Fld11509_TYPE = '\\010'::bytea AND T7._Fld11509_RTRef =
'\\000\\000\\0018'::bytea) AND EXISTS(SELECT
TRUE AS Q_003_F_000_
FROM _InfoRg8199 T14
LEFT OUTER JOIN _InfoRg10621 T15
ON ((T14._Fld8201RRef = T15._Fld10622RRef)) AND (T15._Fld809 = CAST(0 AS
NUMERIC))
WHERE ((T14._Fld809 = CAST(0 AS NUMERIC))) AND ((T14._Fld8200_TYPE =
(T9._Fld4781_TYPE) AND T14._Fld8200_RTRef = (T9._Fld4781_RTRef) AND
T14._Fld8200_RRRef = (T9._Fld4781_RRRef)) AND (T15._Fld15131RRef =
'\\210@\\000PV\\276\\307<\\021\\360\\244\\243?\\321i\\276'::bytea))) OR (NOT
(((T7._Fld11509_TYPE = '\\010'::bytea AND T7._Fld11509_RTRef =
'\\000\\000\\0018'::bytea)))) AND EXISTS(SELECT
TRUE AS Q_004_F_000_
FROM _InfoRg8199 T16
LEFT OUTER JOIN _InfoRg10621 T17
ON ((T16._Fld8201RRef = T17._Fld10622RRef)) AND (T17._Fld809 = CAST(0 AS
NUMERIC))
WHERE ((T16._Fld809 = CAST(0 AS NUMERIC))) AND ((T16._Fld8200_TYPE =
T7._Fld11509_TYPE AND T16._Fld8200_RTRef = T7._Fld11509_RTRef AND
T16._Fld8200_RRRef = T7._Fld11509_RRRef) AND (T17._Fld15131RRef =
'\\210@\\000PV\\276\\307<\\021\\360\\244\\243?\\321i\\276'::bytea)))))) THEN
TRUE ELSE FALSE END AS SDBL_RLS_SIGNAL_,
T7._Fld809 AS Fld809_,
T7._Fld11509_TYPE AS Fld11509_TYPE,
T7._Fld11509_RTRef AS Fld11509_RTRef,
T7._Fld11509_RRRef AS Fld11509_RRRef,
T7._Fld11508RRef AS Fld11508RRef,
T7._Fld11507_TYPE AS Fld11507_TYPE,
T7._Fld11507_RTRef AS Fld11507_RTRef,
T7._Fld11507_RRRef AS Fld11507_RRRef
FROM _InfoRg11506 T7
WHERE (T7._Fld809 = CAST(0 AS NUMERIC))) T6
ON ('\\010'::bytea = T6.Fld11507_TYPE AND '\\000\\000\\000\\177'::bytea =
T6.Fld11507_RTRef AND T1._IDRRef = T6.Fld11507_RRRef) AND (T6.Fld11508RRef =
'\\237X\\000PV\\276\\306\\177\\021\\360\\211l\\367\\233\\277\\234'::bytea)
LEFT OUTER JOIN (SELECT
CASE WHEN (EXISTS(SELECT
1
FROM (SELECT 1 AS SDBL_DUMMY) SDBL_DUAL
LEFT OUTER JOIN _Reference147 T20
ON ((T19._Fld8928RRef = T20._IDRRef)) AND (T20._Fld809 = CAST(0 AS NUMERIC))
WHERE (T20._Fld2132_TYPE = '\\010'::bytea AND T20._Fld2132_RTRef =
'\\000\\000\\001\\020'::bytea AND T20._Fld2132_RRRef IN
('\\210@\\000PV\\276\\307<\\021\\360\\244\\2439#x\\310'::bytea)) OR
(T20._Fld2135 = FALSE) AND EXISTS(SELECT
TRUE AS Q_001_F_000_
FROM _InfoRg8199 T21
WHERE ((T21._Fld809 = CAST(0 AS NUMERIC))) AND (('\\010'::bytea =
T21._Fld8200_TYPE AND '\\000\\000\\000\\223'::bytea = T21._Fld8200_RTRef AND
T20._IDRRef = T21._Fld8200_RRRef) AND EXISTS(SELECT
TRUE AS Q_002_F_000_
FROM _InfoRg10621 T22
WHERE ((T22._Fld809 = CAST(0 AS NUMERIC))) AND ((T21._Fld8201RRef =
T22._Fld10622RRef) AND (T22._Fld15131RRef =
'\\210@\\000PV\\276\\307<\\021\\360\\244\\243?\\321i\\276'::bytea))))))) THEN
TRUE ELSE FALSE END AS SDBL_RLS_SIGNAL_,
T19._Fld8928RRef AS Fld8928RRef,
T19._Fld8927_TYPE AS Fld8927_TYPE,
T19._Fld8927_RTRef AS Fld8927_RTRef,
T19._Fld8927_RRRef AS Fld8927_RRRef,
T19._Fld809 AS Fld809_
FROM _InfoRg8926 T19
WHERE (T19._Fld809 = CAST(0 AS NUMERIC))) T18
ON ('\\010'::bytea = T18.Fld8927_TYPE AND '\\000\\000\\000\\177'::bytea =
T18.Fld8927_RTRef AND T1._IDRRef = T18.Fld8927_RRRef)
WHERE ((T1._Fld809 = CAST(0 AS NUMERIC))) AND ((T1._IDRRef =
'\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea)
AND (T1._Fld1806RRef =
'\\203\\356\\000PV\\276+c\\021\\360\\215L\\342i\\233\\304'::bytea))
Huge planning time
==
PostgreSQL 17.7 (Ubuntu 17.7-1.1C) on x86_64-pc-linux-gnu, compiled by gcc
(Ubuntu 11.4.0-1ubuntu1~22.04.2) 11.4.0, 64-bit
OS - UBUNTU VERSION="22.04.2 LTS (Jammy Jellyfish)"
\d _InfoRg8199
Таблица "public._inforg8199"
Столбец | Тип | Правило сортировки | Допустимость NULL | По
умолчанию
----------------+--------------+--------------------+-------------------+--------------
_fld8200_type | bytea | | not null |
_fld8200_rtref | bytea | | not null |
_fld8200_rrref | bytea | | not null |
_fld8201rref | bytea | | not null |
_fld8202rref | bytea | | not null |
_fld8203 | numeric(1,0) | | not null |
_fld8204_type | bytea | | not null |
_fld8204_rtref | bytea | | not null |
_fld8204_rrref | bytea | | not null |
_fld809 | numeric(7,0) | | not null |
_fld7827 | bytea
| | not null |
Индексы:
"_inforg8199_1" UNIQUE, btree (_fld809, _fld8200_type, _fld8200_rtref,
_fld8200_rrref, _fld8201rref, _fld8202rref, _fld8203) CLUSTER
"_inforg8199_2" UNIQUE, btree (_fld809, _fld8201rref, _fld8200_type,
_fld8200_rtref, _fld8200_rrref, _fld8202rref, _fld8203)
"_inforg8199_3" UNIQUE, btree (_fld809, _fld8202rref, _fld8200_type,
_fld8200_rtref, _fld8200_rrref, _fld8201rref, _fld8203)
"_inforg8199_4" UNIQUE, btree (_fld809, _fld8204_type, _fld8204_rtref,
_fld8204_rrref, _fld8200_type, _fld8200_rtref, _fld8200_rrref, _fld8201rref,
_fld8202rref, _fld8203)
relname | relpages | reltuples | relallvisible | relkind | relnatts |
relhassubclass | reloptions | pg_table_size
-------------+----------+-----------+---------------+---------+----------+----------------+---------------------------+---------------
_inforg8199 | 298 | 13993 | 0 | r | 11 | f
| {autovacuum_enabled=true} | 2449408
\d _InfoRg10621
Таблица "public._inforg10621"
Столбец | Тип | Правило сортировки | Допустимость NULL | По
умолчанию
-----------------+--------------+--------------------+-------------------+--------------
_fld10622rref | bytea | | not null |
_fld15131rref | bytea | | not null |
_fld10623rref | bytea | | not null |
_fld10624_type | bytea | | not null |
_fld10624_rtref | bytea | | not null |
_fld10624_rrref | bytea | | not null |
_fld10625 | boolean | | not null |
_fld10626 | boolean | | not null |
_fld10627 | boolean | | not null |
_fld10628 | boolean | | not null |
_fld10629 | boolean | | not null |
_fld7826 | bytea | | not null |
_fld809 | numeric(7,0) | | not null |
_fld7827 | bytea | | not null |
Индексы:
"_inforg10621_1" UNIQUE, btree (_fld809, _fld10622rref, _fld15131rref,
_fld10623rref, _fld10624_type, _fld10624_rtref, _fld10624_rrref) CLUSTER
"_inforg10621_2" UNIQUE, btree (_fld809, _fld15131rref, _fld10622rref,
_fld10623rref, _fld10624_type, _fld10624_rtref, _fld10624_rrref)
"_inforg10621_3" UNIQUE, btree (_fld809, _fld10623rref, _fld10622rref,
_fld15131rref, _fld10624_type, _fld10624_rtref, _fld10624_rrref)
"_inforg10621_4" UNIQUE, btree (_fld809, _fld10624_type, _fld10624_rtref,
_fld10624_rrref, _fld10622rref, _fld15131rref, _fld10623rref)
relname | relpages | reltuples | relallvisible | relkind | relnatts |
relhassubclass | reloptions | pg_table_size
--------------+----------+-----------+---------------+---------+----------+----------------+---------------------------+---------------
_inforg10621 | 1099 | 57135 | 0 | r | 14 | f
| {autovacuum_enabled=true} | 9011200
\d _Reference127
_fld1818rref | bytea | | not null
|
_fld1819 | mvarchar(40) | | not null
|
_fld1820rref | bytea | | not null
|
_fld1821 | mvarchar | | not null
|
_fld1822_type | bytea | | not null
|
_fld1822_rtref | bytea | | not null
|
_fld1822_rrref | bytea | | not null
|
_fld1823 | timestamp without time zone | | not null
|
_fld1824 | numeric(15,2) | | not null
|
_fld1825 | numeric(15,2) | | not null
|
_fld1826 | mvarchar | | not null
|
_fld1827 | mvarchar | | not null
|
_fld1828rref | bytea | | not null
|
_fld1829 | boolean | | not null
|
_fld1830_type | bytea | | not null
|
_fld1830_rtref | bytea | | not null
|
_fld1830_rrref | bytea | | not null
|
_fld1831_type | bytea | | not null
|
_fld1831_rtref | bytea | | not null
|
_fld1831_rrref | bytea | | not null
|
_fld1832rref | bytea | | not null
|
_fld1833 | numeric(10,0) | | not null
|
_fld1834rref | bytea | | not null
|
_fld1269rref | bytea | | not null
|
_fld809 | numeric(7,0) | | not null
|
_fld1459 | bytea | | not null
|
Индексы:
"_reference127_1" btree (_fld809, _predefinedid)
"_reference127_10" UNIQUE, btree (_fld809, _fld1809rref, _idrref)
"_reference127_11" UNIQUE, btree (_fld809, _fld1818rref, _idrref)
"_reference127_12" UNIQUE, btree (_fld809, _fld1819, _idrref)
"_reference127_13" UNIQUE, btree (_fld809, _fld1828rref, _idrref)
"_reference127_14" UNIQUE, btree (_fld809, _fld1459, _idrref)
"_reference127_2" UNIQUE, btree (_fld809, _code, _idrref)
"_reference127_3" UNIQUE, btree (_fld809, _description, _idrref)
"_reference127_4" UNIQUE, btree (_fld809, _fld1780rref, _idrref)
"_reference127_5" UNIQUE, btree (_fld809, _fld1781rref, _idrref)
"_reference127_6" UNIQUE, btree (_fld809, _fld1782, _idrref)
"_reference127_7" UNIQUE, btree (_fld809, _fld1787, _idrref)
"_reference127_8" UNIQUE, btree (_fld809, _fld1790rref, _idrref)
"_reference127_9" UNIQUE, btree (_fld809, _fld1791, _idrref)
"_reference127_s_hpk" UNIQUE, btree (_fld809, _idrref) CLUSTER
relname | relpages | reltuples | relallvisible | relkind | relnatts |
relhassubclass | reloptions | pg_table_size
---------------+----------+-----------+---------------+---------+----------+----------------+---------------------------+---------------
_reference127 | 128 | 759 | 0 | r | 89 | f
| {autovacuum_enabled=true} | 1056768
\d _InfoRg12030
Таблица "public._inforg12030"
Столбец | Тип | Правило сортировки | Допустимость NULL | По
умолчанию
---------------+---------------+--------------------+-------------------+--------------
_fld12031rref | bytea | | not null |
_fld12032rref | bytea | | not null |
_fld12033 | numeric(15,2) | | not null |
_fld12034 | numeric(15,2) | | not null |
_fld7826 | bytea | | not null |
_fld809 | numeric(7,0) | | not null |
_fld7827 | bytea | | not null |
Индексы:
"_inforg12030_1" UNIQUE, btree (_fld809, _fld12031rref, _fld12032rref)
CLUSTER
"_inforg12030_2" UNIQUE, btree (_fld809, _fld12032rref, _fld12031rref)
relname | relpages | reltuples | relallvisible | relkind | relnatts |
relhassubclass | reloptions | pg_table_size
--------------+----------+-----------+---------------+---------+----------+----------------+---------------------------+---------------
_inforg12030 | 0 | 0 | 0 | r | 7 | f
| {autovacuum_enabled=true} | 8192
\d _Reference312
_fld4789 | mvarchar(256) | | not null
|
_fld4790rref | bytea | | not null
|
_fld4791_type | bytea | | not null
|
_fld4791_rtref | bytea | | not null
|
_fld4791_rrref | bytea | | not null
|
_fld4792rref | bytea | | not null
|
_fld4793rref | bytea | | not null
|
_fld4794_type | bytea | | not null
|
_fld4794_rtref | bytea | | not null
|
_fld4794_rrref | bytea | | not null
|
_fld4795 | timestamp without time zone | | not null
|
_fld4796 | timestamp without time zone | | not null
|
_fld4797 | mvarchar(50) | | not null
|
_fld4798 | numeric(5,0) | | not null
|
_fld4799 | mvarchar | | not null
|
_fld4800 | numeric(10,0) | | not null
|
_fld4801 | mvarchar(10) | | not null
|
_fld4802rref | bytea | | not null
|
_fld4803 | bytea | | not null
|
_fld4804 | boolean | | not null
|
_fld4805rref | bytea | | not null
|
_fld4806 | mvarchar(255) | | not null
|
_fld1269rref | bytea | | not null
|
_fld809 | numeric(7,0) | | not null
|
Индексы:
"_reference312_1" btree (_fld809, _predefinedid)
"_reference312_10" UNIQUE, btree (_fld809, _fld4790rref, _idrref)
"_reference312_11" UNIQUE, btree (_fld809, _fld4791_type, _fld4791_rtref,
_fld4791_rrref, _idrref)
"_reference312_12" UNIQUE, btree (_fld809, _fld4793rref, _idrref)
"_reference312_13" UNIQUE, btree (_fld809, _fld4796, _idrref)
"_reference312_14" UNIQUE, btree (_fld809, _fld4800, _idrref)
"_reference312_15" UNIQUE, btree (_fld809, _fld4801, _idrref)
"_reference312_16" UNIQUE, btree (_fld809, _fld4802rref, _idrref)
"_reference312_2" UNIQUE, btree (_fld809, _code, _idrref)
"_reference312_3" UNIQUE, btree (_fld809, _description, _idrref)
"_reference312_4" UNIQUE, btree (_fld809, _fld4780_type, _fld4780_rtref,
_fld4780_rrref, _idrref)
"_reference312_5" UNIQUE, btree (_fld809, _fld4781_type, _fld4781_rtref,
_fld4781_rrref, _description, _idrref, _marked)
"_reference312_6" UNIQUE, btree (_fld809, _fld4783, _idrref)
"_reference312_7" UNIQUE, btree (_fld809, _fld4784, _idrref)
"_reference312_8" UNIQUE, btree (_fld809, _fld4788, _idrref)
"_reference312_9" UNIQUE, btree (_fld809, _fld4789, _idrref)
"_reference312_s_hpk" UNIQUE, btree (_fld809, _idrref) CLUSTER
relname | relpages | reltuples | relallvisible | relkind | relnatts |
relhassubclass | reloptions | pg_table_size
---------------+----------+-----------+---------------+---------+----------+----------------+---------------------------+---------------
_reference312 | 53 | 574 | 0 | r | 43 | f
| {autovacuum_enabled=true} | 442368
\d _InfoRg11506
Таблица "public._inforg11506"
Столбец | Тип | Правило сортировки |
Допустимость NULL | По умолчанию
-----------------+-----------------------------+--------------------+-------------------+--------------
_fld11507_type | bytea | | not null
|
_fld11507_rtref | bytea | | not null
|
_fld11507_rrref | bytea | | not null
|
_fld11508rref | bytea | | not null
|
_fld11509_type | bytea | | not null
|
_fld11509_rtref | bytea | | not null
|
_fld11509_rrref | bytea | | not null
|
_fld11510 | mvarchar(250) | | not null
|
_fld11511rref | bytea | | not null
|
_fld11512 | boolean | | not null
|
_fld11513 | timestamp without time zone | | not null
|
_fld11514 | mvarchar | | not null
|
_fld11515 | numeric(6,0) | | not null
|
_fld11516_type | bytea | | not null
|
_fld11516_rtref | bytea | | not null
|
_fld11516_rrref | bytea | | not null
|
_fld7826 | bytea | | not null
|
_fld3920 | mvarchar | | not null
|
_fld3921 | mvarchar | | not null
|
_fld809 | numeric(7,0) | | not null
|
Индексы:
"_inforg11506_1" UNIQUE, btree (_fld809, _fld11507_type, _fld11507_rtref,
_fld11507_rrref, _fld11508rref, _fld11509_type, _fld11509_rtref,
_fld11509_rrref, _fld11510, _fld11511rref) CLUSTER
"_inforg11506_2" UNIQUE, btree (_fld809, _fld11509_type, _fld11509_rtref,
_fld11509_rrref, _fld11507_type, _fld11507_rtref, _fld11507_rrref,
_fld11508rref, _fld11510, _fld11511rref)
relname | relpages | reltuples | relallvisible | relkind | relnatts |
relhassubclass | reloptions | pg_table_size
--------------+----------+-----------+---------------+---------+----------+----------------+---------------------------+---------------
_inforg11506 | 12 | 528 | 0 | r | 20 | f
| {autovacuum_enabled=true} | 106496
\d _InfoRg8926
Таблица "public._inforg8926"
Столбец | Тип | Правило сортировки |
Допустимость NULL | По умолчанию
----------------+-----------------------------+--------------------+-------------------+--------------
_fld8927_type | bytea | | not null
|
_fld8927_rtref | bytea | | not null
|
_fld8927_rrref | bytea | | not null
|
_fld8928rref | bytea | | not null
|
_fld8929_type | bytea | | not null
|
_fld8929_rtref | bytea | | not null
|
_fld8929_rrref | bytea | | not null
|
_fld8930 | timestamp without time zone | | not null
|
_fld8931 | boolean | | not null
|
_fld7826 | bytea | | not null
|
_fld809 | numeric(7,0) | | not null
|
_fld7827 | bytea | | not null
|
Индексы:
"_inforg8926_1" UNIQUE, btree (_fld809, _fld8927_type, _fld8927_rtref,
_fld8927_rrref, _fld8928rref) CLUSTER
"_inforg8926_2" UNIQUE, btree (_fld809, _fld8928rref, _fld8927_type,
_fld8927_rtref, _fld8927_rrref)
"_inforg8926_3" UNIQUE, btree (_fld809, _fld8929_type, _fld8929_rtref,
_fld8929_rrref, _fld8927_type, _fld8927_rtref, _fld8927_rrref, _fld8928rref)
relname | relpages | reltuples | relallvisible | relkind | relnatts |
relhassubclass | reloptions | pg_table_size
-------------+----------+-----------+---------------+---------+----------+----------------+---------------------------+---------------
_inforg8926 | 0 | 0 | 0 | r | 12 | f
| {autovacuum_enabled=true} | 8192
\d _Reference147
Таблица "public._reference147"
Столбец | Тип | Правило сортировки |
Допустимость NULL | По умолчанию
----------------+-----------------------------+--------------------+-------------------+--------------
_idrref | bytea | | not null
|
_version | integer | | not null
| 0
_marked | boolean | | not null
|
_predefinedid | bytea | | not null
|
_parentidrref | bytea | | not null
|
_code | mvarchar(9) | | not null
|
_description | mvarchar(150) | | not null
|
_fld2132_type | bytea | | not null
|
_fld2132_rtref | bytea | | not null
|
_fld2132_rrref | bytea | | not null
|
_fld2133 | timestamp without time zone | | not null
|
_fld2134 | mvarchar | | not null
|
_fld2135 | boolean | | not null
|
_fld809 | numeric(7,0) | | not null
|
Индексы:
"_reference147_1" btree (_fld809, _predefinedid)
"_reference147_2" UNIQUE, btree (_fld809, _parentidrref, _code, _idrref)
"_reference147_3" UNIQUE, btree (_fld809, _parentidrref, _description,
_idrref)
"_reference147_4" UNIQUE, btree (_fld809, _code, _idrref)
"_reference147_5" UNIQUE, btree (_fld809, _description, _idrref)
"_reference147_6" UNIQUE, btree (_fld809, _parentidrref, _fld2135, _idrref)
"_reference147_7" UNIQUE, btree (_fld809, _fld2135, _idrref)
"_reference147_s_hpk" UNIQUE, btree (_fld809, _idrref) CLUSTER
relname | relpages | reltuples | relallvisible | relkind | relnatts |
relhassubclass | reloptions | pg_table_size
---------------+----------+-----------+---------------+---------+----------+----------------+---------------------------+---------------
_reference147 | 1 | 1 | 0 | r | 14 | f
| {autovacuum_enabled=true} | 16384
cpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Address sizes: 43 bits physical, 48 bits virtual
Byte Order: Little Endian
CPU(s): 6
On-line CPU(s) list: 0-5
Vendor ID: GenuineIntel
Model name: Intel(R) Xeon(R) Gold 5220R CPU @ 2.20GHz
CPU family: 6
Model: 85
Thread(s) per core: 1
Core(s) per socket: 3
Socket(s): 2
Stepping: 7
mem
total used free shared buff/cache available
Mem: 24605532 513776 18305948 1987800 5785808 21694868
Swap: 8388604 0 8388604
shared_buffers 6GB
effective_cache_size 16GB
effective_io_concurrency 2
bonnie++ -f -n0 -x4 -d /var/lib/postgresql
format_version,bonnie_version,name,concurrency,seed,file_size,io_chunk_size,putc,putc_cpu,put_block,put_block_cpu,rewrite,rewrite_cpu,getc,getc_cpu,get_block,get_block_cpu,seeks,seeks_cpu,num_files,max_size,min_size,num_dirs,file_chunk_size,seq_create,seq_create_cpu,seq_stat,seq_stat_cpu,seq_del,seq_del_cpu,ran_create,ran_create_cpu,ran_stat,ran_stat_cpu,ran_del,ran_del_cpu,putc_latency,put_block_latency,rewrite_latency,getc_latency,get_block_latency,seeks_latency,seq_create_latency,seq_stat_latency,seq_del_latency,ran_create_latency,ran_stat_latency,ran_del_latency
Writing intelligently...done
Rewriting...done
Reading intelligently...done
start 'em...done...done...done...done...done...
1.98,2.00,ta-func3-sql1-test,1,1768285869,47G,,8192,5,,,452086,44,122549,12,,,137416,8,15624,172,,,,,,,,,,,,,,,,,,,50659us,66581us,,21821us,2422us,,,,,,
Writing intelligently...done
Rewriting...done
Reading intelligently...done
start 'em...done...done...done...done...done...
1.98,2.00,ta-func3-sql1-test,1,1768285869,47G,,8192,5,,,462041,44,118181,11,,,146596,8,3790,116,,,,,,,,,,,,,,,,,,,45478us,138ms,,19240us,2089us,,,,,,
Writing intelligently...done
Rewriting...done
Reading intelligently...done
start 'em...done...done...done...done...done...
1.98,2.00,ta-func3-sql1-test,1,1768285869,47G,,8192,5,,,628882,53,135431,12,,,143343,8,3648,119,,,,,,,,,,,,,,,,,,,26509us,55469us,,23591us,1823us,,,,,,
Writing intelligently...done
Rewriting...done
Reading intelligently...done
start 'em...done...done...done...done...done...
1.98,2.00,ta-func3-sql1-test,1,1768285869,47G,,8192,5,,,1030856,83,134357,12,,,143619,8,3653,113,,,,,,,,,,,,,,,,,,,30936us,60986us,,22422us,15658us,,,,,,
p
SET
SET
SET
SET
SET
SET
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=1.33..15.31 rows=1 width=18) (actual
time=0.030..0.032 rows=0 loops=1)
Buffers: shared hit=2
-> Nested Loop Left Join (cost=1.33..4.72 rows=1 width=19) (actual
time=0.030..0.031 rows=0 loops=1)
Buffers: shared hit=2
-> Nested Loop Left Join (cost=0.11..2.35 rows=1 width=18) (actual
time=0.030..0.030 rows=0 loops=1)
Buffers: shared hit=2
-> Index Scan using _reference127_s_hpk on _reference127 t1
(cost=0.11..2.33 rows=1 width=17) (actual time=0.030..0.030 rows=0 loops=1)
Index Cond: ((_fld809 = '0'::numeric) AND (_idrref =
'\\x00000000000000000000000000000000'::bytea))
Filter: (_fld1806rref =
'\\x83ee005056be2b6311f08d4ce2699bc4'::bytea)
Buffers: shared hit=2
-> Seq Scan on _inforg12030 t3 (cost=0.00..0.01 rows=1
width=33) (never executed)
Filter: ((_fld809 = '0'::numeric) AND (_fld12031rref =
'\\x00000000000000000000000000000000'::bytea))
SubPlan 3
-> Nested Loop (cost=0.33..10.57 rows=1 width=0)
(never executed)
-> Index Only Scan using _inforg8199_1 on
_inforg8199 t4 (cost=0.16..3.38 rows=3 width=20) (never executed)
Index Cond: ((_fld809 = '0'::numeric) AND
(_fld8200_type = '\\x08'::bytea) AND (_fld8200_rtref = '\\x0000007f'::bytea)
AND (_fld8200_rrref = t3._fld12031rref))
Heap Fetches: 0
-> Index Only Scan using _inforg10621_2 on
_inforg10621 t5 (cost=0.17..2.38 rows=1 width=17) (never executed)
Index Cond: ((_fld809 = '0'::numeric) AND
(_fld15131rref = '\\x8840005056bec73c11f0a4a33fd169be'::bytea) AND
(_fld10622rref = t4._fld8201rref))
Heap Fetches: 0
-> Bitmap Heap Scan on _inforg11506 t7 (cost=1.22..2.36 rows=1
width=18) (never executed)
Recheck Cond: ((_fld809 = '0'::numeric) AND ('\\x08'::bytea =
_fld11507_type) AND ('\\x0000007f'::bytea = _fld11507_rtref) AND
(_fld11507_rrref = '\\x00000000000000000000000000000000'::bytea) AND
(_fld11508rref = '\\x9f58005056bec67f11f0896cf79bbf9c'::bytea))
-> Bitmap Index Scan on _inforg11506_1 (cost=0.00..1.22 rows=1
width=0) (never executed)
Index Cond: ((_fld809 = '0'::numeric) AND (_fld11507_type
= '\\x08'::bytea) AND (_fld11507_rtref = '\\x0000007f'::bytea) AND
(_fld11507_rrref = '\\x00000000000000000000000000000000'::bytea) AND
(_fld11508rref = '\\x9f58005056bec67f11f0896cf79bbf9c'::bytea))
SubPlan 11
-> Nested Loop Left Join (cost=9.77..23.81 rows=1 width=0)
(never executed)
Join Filter: ((t7._fld11509_type = '\\x08'::bytea) AND
(t7._fld11509_rtref = '\\x00000138'::bytea))
Filter: (((t7._fld11509_type = '\\x01'::bytea) AND
(t7._fld11509_rtref = '\\x00000000'::bytea) AND (t7._fld11509_rrref =
'\\x00000000000000000000000000000000'::bytea)) OR ((t7._fld11509_type =
'\\x08'::bytea) AND (t7._fld11509_rtref = '\\x00000138'::bytea) AND
EXISTS(SubPlan 8)) OR (((t7._fld11509_type <> '\\x08'::bytea) OR
(t7._fld11509_rtref <> '\\x00000138'::bytea)) AND (InitPlan 10).col1))
InitPlan 7
-> Nested Loop (cost=0.33..4.78 rows=1 width=0)
(never executed)
-> Index Only Scan using _inforg8199_1 on
_inforg8199 t12 (cost=0.16..2.38 rows=1 width=20) (never executed)
Index Cond: ((_fld809 = '0'::numeric) AND
(_fld8200_type = t7._fld11507_type) AND (_fld8200_rtref = t7._fld11507_rtref)
AND (_fld8200_rrref = t7._fld11507_rrref))
Heap Fetches: 0
-> Index Only Scan using _inforg10621_2 on
_inforg10621 t13 (cost=0.17..2.38 rows=1 width=17) (never executed)
Index Cond: ((_fld809 = '0'::numeric) AND
(_fld15131rref = '\\x8840005056bec73c11f0a4a33fd169be'::bytea) AND
(_fld10622rref = t12._fld8201rref))
Heap Fetches: 0
InitPlan 10
-> Nested Loop (cost=0.33..4.78 rows=1 width=0)
(never executed)
-> Index Only Scan using _inforg8199_1 on
_inforg8199 t16 (cost=0.16..2.38 rows=1 width=20) (never executed)
Index Cond: ((_fld809 = '0'::numeric) AND
(_fld8200_type = t7._fld11509_type) AND (_fld8200_rtref = t7._fld11509_rtref)
AND (_fld8200_rrref = t7._fld11509_rrref))
Heap Fetches: 0
-> Index Only Scan using _inforg10621_2 on
_inforg10621 t17 (cost=0.17..2.38 rows=1 width=17) (never executed)
Index Cond: ((_fld809 = '0'::numeric) AND
(_fld15131rref = '\\x8840005056bec73c11f0a4a33fd169be'::bytea) AND
(_fld10622rref = t16._fld8201rref))
Heap Fetches: 0
-> Nested Loop Left Join (cost=0.11..7.13 rows=1
width=0) (never executed)
Join Filter: ((t7._fld11507_type = '\\x08'::bytea)
AND (t7._fld11507_rtref = '\\x00000138'::bytea))
Filter: (((t7._fld11507_type = '\\x08'::bytea) AND
(t7._fld11507_rtref = '\\x00000138'::bytea) AND EXISTS(SubPlan 5)) OR
(((t7._fld11507_type <> '\\x08'::bytea) OR (t7._fld11507_rtref <>
'\\x00000138'::bytea)) AND (InitPlan 7).col1))
-> Result (cost=0.00..0.01 rows=1 width=0)
(never executed)
-> Index Scan using _reference312_s_hpk on
_reference312 t8 (cost=0.11..2.33 rows=1 width=24) (never executed)
Index Cond: ((_fld809 = '0'::numeric) AND
(_idrref = t7._fld11507_rrref))
SubPlan 5
-> Nested Loop (cost=0.33..4.78 rows=1
width=0) (never executed)
-> Index Only Scan using _inforg8199_1 on
_inforg8199 t10 (cost=0.16..2.38 rows=1 width=20) (never executed)
Index Cond: ((_fld809 =
'0'::numeric) AND (_fld8200_type = t8._fld4781_type) AND (_fld8200_rtref =
t8._fld4781_rtref) AND (_fld8200_rrref = t8._fld4781_rrref))
Heap Fetches: 0
-> Index Only Scan using _inforg10621_2
on _inforg10621 t11 (cost=0.17..2.38 rows=1 width=17) (never executed)
Index Cond: ((_fld809 =
'0'::numeric) AND (_fld15131rref =
'\\x8840005056bec73c11f0a4a33fd169be'::bytea) AND (_fld10622rref =
t10._fld8201rref))
Heap Fetches: 0
-> Index Scan using _reference312_s_hpk on
_reference312 t9 (cost=0.11..2.33 rows=1 width=24) (never executed)
Index Cond: ((_fld809 = '0'::numeric) AND (_idrref
= t7._fld11509_rrref))
SubPlan 8
-> Nested Loop (cost=0.33..4.78 rows=1 width=0)
(never executed)
-> Index Only Scan using _inforg8199_1 on
_inforg8199 t14 (cost=0.16..2.38 rows=1 width=20) (never executed)
Index Cond: ((_fld809 = '0'::numeric) AND
(_fld8200_type = t9._fld4781_type) AND (_fld8200_rtref = t9._fld4781_rtref) AND
(_fld8200_rrref = t9._fld4781_rrref))
Heap Fetches: 0
-> Index Only Scan using _inforg10621_2 on
_inforg10621 t15 (cost=0.17..2.38 rows=1 width=17) (never executed)
Index Cond: ((_fld809 = '0'::numeric) AND
(_fld15131rref = '\\x8840005056bec73c11f0a4a33fd169be'::bytea) AND
(_fld10622rref = t14._fld8201rref))
Heap Fetches: 0
-> Seq Scan on _inforg8926 t19 (cost=0.00..0.01 rows=1 width=33) (never
executed)
Filter: ((_fld809 = '0'::numeric) AND ('\\x08'::bytea = _fld8927_type)
AND ('\\x0000007f'::bytea = _fld8927_rtref) AND (_fld8927_rrref =
'\\x00000000000000000000000000000000'::bytea))
SubPlan 14
-> Seq Scan on _reference147 t20 (cost=0.00..8.00 rows=1 width=0)
(never executed)
Filter: ((t19._fld8928rref = _idrref) AND (_fld809 =
'0'::numeric) AND (((_fld2132_type = '\\x08'::bytea) AND (_fld2132_rtref =
'\\x00000110'::bytea) AND (_fld2132_rrref =
'\\x8840005056bec73c11f0a4a3392378c8'::bytea)) OR ((NOT _fld2135) AND (ANY
(_idrref = (hashed SubPlan 13).col1)))))
SubPlan 13
-> Nested Loop Semi Join (cost=0.33..10.98 rows=1
width=20) (never executed)
-> Index Only Scan using _inforg8199_1 on _inforg8199
t21 (cost=0.16..6.38 rows=1 width=40) (never executed)
Index Cond: ((_fld809 = '0'::numeric) AND
(_fld8200_type = '\\x08'::bytea) AND (_fld8200_rtref = '\\x00000093'::bytea))
Heap Fetches: 0
-> Index Only Scan using _inforg10621_2 on
_inforg10621 t22 (cost=0.17..2.38 rows=1 width=17) (never executed)
Index Cond: ((_fld809 = '0'::numeric) AND
(_fld15131rref = '\\x8840005056bec73c11f0a4a33fd169be'::bytea) AND
(_fld10622rref = t21._fld8201rref))
Heap Fetches: 0
SubPlan 1
-> Nested Loop (cost=0.33..10.57 rows=1 width=0) (never executed)
-> Index Only Scan using _inforg8199_1 on _inforg8199 t23
(cost=0.16..3.38 rows=3 width=20) (never executed)
Index Cond: ((_fld809 = '0'::numeric) AND (_fld8200_type =
'\\x08'::bytea) AND (_fld8200_rtref = '\\x0000007f'::bytea) AND (_fld8200_rrref
= t1._idrref))
Heap Fetches: 0
-> Index Only Scan using _inforg10621_2 on _inforg10621 t24
(cost=0.17..2.38 rows=1 width=17) (never executed)
Index Cond: ((_fld809 = '0'::numeric) AND (_fld15131rref =
'\\x8840005056bec73c11f0a4a33fd169be'::bytea) AND (_fld10622rref =
t23._fld8201rref))
Heap Fetches: 0
Settings: effective_cache_size = '16GB', random_page_cost = '1.1',
effective_io_concurrency = '2', from_collapse_limit = '20', join_collapse_limit
= '20', max_parallel_workers_per_gather = '3', max_parallel_workers = '6',
temp_buffers = '256MB', work_mem = '32MB', enable_mergejoin = 'off',
cpu_operator_cost = '0.001'
Planning:
Buffers: shared hit=2717
Planning Time: 452.796 ms
Execution Time: 0.350 ms
(94 строки)
