agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedANSI JOINS versus , like JOINS
3+ messages / 2 participants
[nested] [flat]
* ANSI JOINS versus , like JOINS
@ 2016-06-20 10:06 Herwig Goemans <herwig.goemans@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Herwig Goemans @ 2016-06-20 10:06 UTC (permalink / raw)
To: pgsql-sql
Hello,
I have two queries, identical except for the way the tables are joined. One
version is a mixture of ANSI joins and 'old' , like join syntax, this is
the 'mixed' query The other version is a completely ANSI JOIN syntax
query,I will call it the ANSI query from now on.
We are running postgres 9.5, PostgreSQL 9.5.3 on x86_64-pc-linux-gnu,
compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit on CentOS 7:
CentOS Linux release 7.2.1511 (Core)
It was installed from repository:
yum install postgresql95-server postgresql95-contrib --enablerepo=pgdg95
I prefer very much ANSI joins, simply because they are more readable and
less error prone (like forgetting a join condition). However, the queries
are generated via a JAVA application and the application, unfortunately,
sends the mixed query to the database.
Full details are give below but the interesting thing about these queries
is that the execution plan is not the same. The performance of the mixed
query is much worse. On a cold system, the ANSI query returns
results(approx 8 rows) in approx 2s and with caching in effect, in less
than 300ms. However the mixed query takes 45s, sometimes we get at 15s and
by rewriting a little bit we get at 3-4s.
The other interesting thing is that the execution plan of the mixed query
changes when xts_service_process_activity and tables below are added to the
join list. Without these tables, the execution plan of both queries is the
same.
In query below, id_helper is norrmally a temporary table. For debugging
purposes I have made it a permanent table with relevant data.
In attach create statements and other info.
Is there anyone who can help so that the mixed query has the same or
equivalent execution plan (performance) as the ansi query ?
Thank you.
Herwig
The server:Centos 7
Linux diana.ix.xplanation.com 3.10.0-327.18.2.el7.x86_64 #1 SMP Thu May 12
11:03:55 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
cat /proc/meminfo |grep MemTotal
MemTotal: 8011216 kB
This is the mixed query:
explain (analyze, buffers)
select sqlxtsjoba0_.ID as col_0_0_
from TSTREAM.XTS_JOB_ACTIVITY sqlxtsjoba0_
inner join TSTREAM.XTS_ACT_IN_JOB xtsactinjo1_ on
sqlxtsjoba0_.ID=xtsactinjo1_.XTJA_ID
, id_helper3 sqlidhelpe3_
, TSTREAM.XTS_JOB_PROCESS sqlxtsjobp4_
, TSTREAM.XTS_JOB sqlxtsjob5_
, TSTREAM.XTS_STATUS sqlxtsstat6_
, TSTREAM.XTS_STATUS sqlxtsstat7_
, TSTREAM.XTS_CUSTOMER_ACTIVITY sqlxtscust8_
, TSTREAM.XTS_SERVICE_PROCESS_ACTIVITY sqlxtsserv9_
, TSTREAM.XTS_PROCESS_ACTIVITY sqlxtsproc10_
, TSTREAM.XTS_ACTIVITY sqlxtsacti11_
where set_id = 0
and sqlxtsjoba0_.ID = sqlidhelpe3_.reference_ID
and sqlxtsstat6_.CODE='RUN'
and sqlxtsstat7_.CODE='RUN'
and xtsactinjo1_.XTJP_ID=sqlxtsjobp4_.ID
and sqlxtsjobp4_.XTJO_ID=sqlxtsjob5_.ID
and sqlxtsjob5_.XPRS_ID=sqlxtsstat6_.ID
and sqlxtsjoba0_.XPRS_ID=sqlxtsstat7_.ID
and sqlxtsjoba0_.XTCA_ID=sqlxtscust8_.ID
and sqlxtscust8_.XSPA_ID=sqlxtsserv9_.ID
and sqlxtsserv9_.XPRA_ID=sqlxtsproc10_.ID
and sqlxtsproc10_.XTAC_ID=sqlxtsacti11_.ID
and sqlxtsacti11_.HANDLER='FinalDeliveryCheckHandlerSession'
order by 1;
And ii's plan:
"QUERY PLAN"
"Nested Loop (cost=19764.93..1720710.10 rows=1 width=4) (actual
time=49834.078..55288.833 rows=7 loops=1)"
" Buffers: shared hit=520154 read=376691"
" I/O Timings: read=4084.273"
" -> Nested Loop (cost=19764.79..1720709.92 rows=1 width=12) (actual
time=42345.312..55288.671 rows=17 loops=1)"
" Buffers: shared hit=520120 read=376691"
" I/O Timings: read=4084.273"
" -> Nested Loop (cost=19764.36..1720709.36 rows=1 width=12)
(actual time=42336.880..55236.649 rows=17 loops=1)"
" Buffers: shared hit=520073 read=376670"
" I/O Timings: read=4032.574"
" -> Nested Loop (cost=19763.93..1720708.91 rows=1 width=12)
(actual time=42323.227..55164.778 rows=17 loops=1)"
" Join Filter: (sqlxtsserv9_.id = sqlxtscust8_.xspa_id)"
" Rows Removed by Join Filter: 823"
" Buffers: shared hit=520025 read=376650"
" I/O Timings: read=3961.004"
" -> Nested Loop (cost=19762.19..1720701.77 rows=18
width=20) (actual time=42323.133..55152.950 rows=21 loops=1)"
" Buffers: shared hit=520016 read=376649"
" I/O Timings: read=3950.217"
" -> Nested Loop (cost=19761.76..1720693.58
rows=18 width=20) (actual time=42292.470..55029.761 rows=21 loops=1)"
" Join Filter: (sqlxtsjoba0_.xprs_id =
sqlxtsstat7_.id)"
" Rows Removed by Join Filter: 1579"
" Buffers: shared hit=519951 read=376630"
" I/O Timings: read=3827.494"
" -> Merge Join (cost=19761.76..1720666.95
rows=392 width=28) (actual time=42292.368..55027.679 rows=400 loops=1)"
" Merge Cond: (sqlxtsjoba0_.id =
sqlidhelpe3_.reference_id)"
" Buffers: shared hit=519949
read=376630"
" I/O Timings: read=3827.494"
" -> Merge Join
(cost=9187.27..1671024.74 rows=19628192 width=36) (actual
time=220.994..47852.677 rows=18610377 loops=1)"
" Merge Cond: (sqlxtsjoba0_.id =
xtsactinjo1_.xtja_id)"
" Buffers: shared hit=519948
read=376624"
" I/O Timings: read=3827.471"
" -> Index Scan using
xts_job_activity_pkey on xts_job_activity sqlxtsjoba0_
(cost=0.44..744709.37 rows=17843875 width=20) (actual
time=0.010..10119.665 rows=17079648 loops=1)"
" Buffers: shared
hit=72557 read=250737"
" I/O Timings:
read=1234.731"
" -> Index Scan using xaij_xtja
on xts_act_in_job xtsactinjo1_ (cost=0.44..636358.07 rows=19628192
width=16) (actual time=0.008..12174.828 rows=18610377 loops=1)"
" Buffers: shared
hit=447391 read=125887"
" I/O Timings:
read=2592.740"
" -> Sort (cost=565.85..566.74
rows=356 width=4) (actual time=0.450..0.658 rows=400 loops=1)"
" Sort Key:
sqlidhelpe3_.reference_id"
" Sort Method: quicksort
Memory: 43kB"
" Buffers: shared hit=1 read=6"
" I/O Timings: read=0.023"
" -> Bitmap Heap Scan on
id_helper3 sqlidhelpe3_ (cost=7.18..550.76 rows=356 width=4) (actual
time=0.065..0.248 rows=400 loops=1)"
" Recheck Cond: (set_id =
0)"
" Heap Blocks: exact=4"
" Buffers: shared hit=1
read=6"
" I/O Timings: read=0.023"
" -> Bitmap Index Scan on
id_helper_set (cost=0.00..7.09 rows=356 width=0) (actual time=0.054..0.054
rows=400 loops=1)"
" Index Cond:
(set_id = 0)"
" Buffers: shared
read=3"
" I/O Timings:
read=0.013"
" -> Materialize (cost=0.00..3.12 rows=4
width=4) (actual time=0.001..0.002 rows=4 loops=400)"
" Buffers: shared hit=2"
" -> Seq Scan on xts_status
sqlxtsstat7_ (cost=0.00..3.10 rows=4 width=4) (actual time=0.020..0.037
rows=4 loops=1)"
" Filter: ((code)::text =
'RUN'::text)"
" Rows Removed by Filter: 84"
" Buffers: shared hit=2"
" -> Index Scan using xts_customer_activity_pkey
on xts_customer_activity sqlxtscust8_ (cost=0.42..0.44 rows=1 width=12)
(actual time=5.860..5.861 rows=1 loops=21)"
" Index Cond: (id = sqlxtsjoba0_.xtca_id)"
" Buffers: shared hit=65 read=19"
" I/O Timings: read=122.723"
" -> Materialize (cost=1.74..5.80 rows=5 width=4)
(actual time=0.005..0.544 rows=40 loops=21)"
" Buffers: shared hit=9 read=1"
" I/O Timings: read=10.787"
" -> Nested Loop (cost=1.74..5.78 rows=5
width=4) (actual time=0.085..11.064 rows=40 loops=1)"
" Buffers: shared hit=9 read=1"
" I/O Timings: read=10.787"
" -> Hash Join (cost=1.60..4.97 rows=2
width=4) (actual time=0.070..0.161 rows=3 loops=1)"
" Hash Cond: (sqlxtsproc10_.xtac_id =
sqlxtsacti11_.id)"
" Buffers: shared hit=3"
" -> Seq Scan on xts_process_activity
sqlxtsproc10_ (cost=0.00..2.98 rows=98 width=12) (actual time=0.007..0.065
rows=98 loops=1)"
" Buffers: shared hit=2"
" -> Hash (cost=1.59..1.59 rows=1
width=4) (actual time=0.022..0.022 rows=1 loops=1)"
" Buckets: 1024 Batches: 1
Memory Usage: 9kB"
" Buffers: shared hit=1"
" -> Seq Scan on xts_activity
sqlxtsacti11_ (cost=0.00..1.59 rows=1 width=4) (actual time=0.014..0.018
rows=1 loops=1)"
" Filter: ((handler)::text
= 'FinalDeliveryCheckHandlerSession'::text)"
" Rows Removed by Filter:
46"
" Buffers: shared hit=1"
" -> Index Scan using xspa_xpra on
xts_service_process_activity sqlxtsserv9_ (cost=0.14..0.38 rows=2
width=12) (actual time=0.009..3.617 rows=13 loops=3)"
" Index Cond: (xpra_id =
sqlxtsproc10_.id)"
" Buffers: shared hit=6 read=1"
" I/O Timings: read=10.787"
" -> Index Scan using xts_job_process_pkey on xts_job_process
sqlxtsjobp4_ (cost=0.43..0.45 rows=1 width=12) (actual time=4.223..4.224
rows=1 loops=17)"
" Index Cond: (id = xtsactinjo1_.xtjp_id)"
" Buffers: shared hit=48 read=20"
" I/O Timings: read=71.570"
" -> Index Scan using xts_job_pkey on xts_job sqlxtsjob5_
(cost=0.43..0.55 rows=1 width=12) (actual time=3.054..3.055 rows=1
loops=17)"
" Index Cond: (id = sqlxtsjobp4_.xtjo_id)"
" Buffers: shared hit=47 read=21"
" I/O Timings: read=51.699"
" -> Index Scan using xts_status_pkey on xts_status sqlxtsstat6_
(cost=0.14..0.16 rows=1 width=4) (actual time=0.006..0.007 rows=0
loops=17)"
" Index Cond: (id = sqlxtsjob5_.xprs_id)"
" Filter: ((code)::text = 'RUN'::text)"
" Rows Removed by Filter: 1"
" Buffers: shared hit=34"
"Planning time: 7.273 ms"
"Execution time: 55288.986 ms"
Then the ANSI Query:
explain (analyze, buffers) select sqlxtsjoba0_.ID as col_0_0_
from TSTREAM.XTS_JOB_ACTIVITY sqlxtsjoba0_
inner join TSTREAM.XTS_ACT_IN_JOB xtsactinjo1_ on
sqlxtsjoba0_.ID=xtsactinjo1_.XTJA_ID
join id_helper3 sqlidhelpe3_ on sqlxtsjoba0_.ID = sqlidhelpe3_.reference_ID
and set_id = 0
join TSTREAM.XTS_JOB_PROCESS sqlxtsjobp4_ on
xtsactinjo1_.XTJP_ID=sqlxtsjobp4_.ID
join TSTREAM.XTS_JOB sqlxtsjob5_ on sqlxtsjobp4_.XTJO_ID=sqlxtsjob5_.ID
join TSTREAM.XTS_STATUS sqlxtsstat6_ on sqlxtsjob5_.XPRS_ID=sqlxtsstat6_.ID
join TSTREAM.XTS_STATUS sqlxtsstat7_ on sqlxtsjoba0_.XPRS_ID=sqlxtsstat7_.ID
join TSTREAM.XTS_CUSTOMER_ACTIVITY sqlxtscust8_ on
sqlxtsjoba0_.XTCA_ID=sqlxtscust8_.ID
join TSTREAM.XTS_SERVICE_PROCESS_ACTIVITY sqlxtsserv9_ on
sqlxtscust8_.XSPA_ID=sqlxtsserv9_.ID
join TSTREAM.XTS_PROCESS_ACTIVITY sqlxtsproc10_ on
sqlxtsserv9_.XPRA_ID=sqlxtsproc10_.ID
join TSTREAM.XTS_ACTIVITY sqlxtsacti11_ on
sqlxtsproc10_.XTAC_ID=sqlxtsacti11_.ID
where sqlxtsstat6_.CODE='RUN'
and sqlxtsstat7_.CODE='RUN'
and sqlxtsacti11_.HANDLER='FinalDeliveryCheckHandlerSession'
order by 1;
"QUERY PLAN"
"Sort (cost=3630.62..3630.63 rows=1 width=4) (actual time=7.467..7.470
rows=7 loops=1)"
" Sort Key: sqlxtsjoba0_.id"
" Sort Method: quicksort Memory: 25kB"
" Buffers: shared hit=1965"
" -> Nested Loop (cost=9.33..3630.61 rows=1 width=4) (actual
time=1.418..7.451 rows=7 loops=1)"
" Join Filter: (sqlxtsproc10_.xtac_id = sqlxtsacti11_.id)"
" Rows Removed by Join Filter: 3"
" Buffers: shared hit=1965"
" -> Nested Loop (cost=9.33..3629.01 rows=1 width=12) (actual
time=1.408..7.353 rows=10 loops=1)"
" Join Filter: (sqlxtsserv9_.xpra_id = sqlxtsproc10_.id)"
" Rows Removed by Join Filter: 970"
" Buffers: shared hit=1955"
" -> Nested Loop (cost=9.33..3624.81 rows=1 width=12)
(actual time=1.360..6.556 rows=10 loops=1)"
" Join Filter: (sqlxtscust8_.xspa_id = sqlxtsserv9_.id)"
" Rows Removed by Join Filter: 2440"
" Buffers: shared hit=1935"
" -> Nested Loop (cost=9.33..3616.29 rows=1 width=12)
(actual time=1.224..4.589 rows=10 loops=1)"
" Buffers: shared hit=1905"
" -> Nested Loop (cost=8.91..3615.84 rows=1
width=12) (actual time=1.213..4.517 rows=10 loops=1)"
" Join Filter: (sqlxtsjob5_.xprs_id =
sqlxtsstat6_.id)"
" Rows Removed by Join Filter: 74"
" Buffers: shared hit=1865"
" -> Nested Loop (cost=8.91..3611.65
rows=18 width=20) (actual time=0.197..4.394 rows=21 loops=1)"
" Buffers: shared hit=1863"
" -> Nested Loop (cost=8.48..3601.60
rows=18 width=20) (actual time=0.187..4.219 rows=21 loops=1)"
" Buffers: shared hit=1779"
" -> Nested Loop
(cost=8.05..3593.34 rows=18 width=20) (actual time=0.172..4.037 rows=21
loops=1)"
" Buffers: shared hit=1695"
" -> Nested Loop
(cost=7.62..3584.77 rows=16 width=16) (actual time=0.154..3.841 rows=21
loops=1)"
" Join Filter:
(sqlxtsjoba0_.xprs_id = sqlxtsstat7_.id)"
" Rows Removed by
Join Filter: 1579"
" Buffers: shared
hit=1611"
" -> Nested Loop
(cost=7.62..3560.30 rows=356 width=24) (actual time=0.087..2.154 rows=400
loops=1)"
" Buffers:
shared hit=1609"
" -> Bitmap
Heap Scan on id_helper3 sqlidhelpe3_ (cost=7.18..550.76 rows=356 width=4)
(actual time=0.075..0.233 rows=400 loops=1)"
"
Recheck Cond: (set_id = 0)"
" Heap
Blocks: exact=4"
"
Buffers: shared hit=7"
" ->
Bitmap Index Scan on id_helper_set (cost=0.00..7.09 rows=356 width=0)
(actual time=0.065..0.065 rows=400 loops=1)"
"
Index Cond: (set_id = 0)"
"
Buffers: shared hit=3"
" -> Index
Scan using xts_job_activity_pkey on xts_job_activity sqlxtsjoba0_
(cost=0.44..8.44 rows=1 width=20) (actual time=0.003..0.003 rows=1
loops=400)"
" Index
Cond: (id = sqlidhelpe3_.reference_id)"
"
Buffers: shared hit=1602"
" -> Materialize
(cost=0.00..3.12 rows=4 width=4) (actual time=0.000..0.002 rows=4
loops=400)"
" Buffers:
shared hit=2"
" -> Seq Scan
on xts_status sqlxtsstat7_ (cost=0.00..3.10 rows=4 width=4) (actual
time=0.007..0.016 rows=4 loops=1)"
"
Filter: ((code)::text = 'RUN'::text)"
" Rows
Removed by Filter: 84"
"
Buffers: shared hit=2"
" -> Index Scan using
xaij_xtja on xts_act_in_job xtsactinjo1_ (cost=0.44..0.52 rows=2 width=16)
(actual time=0.007..0.008 rows=1 loops=21)"
" Index Cond:
(xtja_id = sqlxtsjoba0_.id)"
" Buffers: shared
hit=84"
" -> Index Scan using
xts_job_process_pkey on xts_job_process sqlxtsjobp4_ (cost=0.43..0.45
rows=1 width=12) (actual time=0.006..0.007 rows=1 loops=21)"
" Index Cond: (id =
xtsactinjo1_.xtjp_id)"
" Buffers: shared hit=84"
" -> Index Scan using xts_job_pkey on
xts_job sqlxtsjob5_ (cost=0.43..0.55 rows=1 width=12) (actual
time=0.006..0.006 rows=1 loops=21)"
" Index Cond: (id =
sqlxtsjobp4_.xtjo_id)"
" Buffers: shared hit=84"
" -> Materialize (cost=0.00..3.12 rows=4
width=4) (actual time=0.001..0.003 rows=4 loops=21)"
" Buffers: shared hit=2"
" -> Seq Scan on xts_status
sqlxtsstat6_ (cost=0.00..3.10 rows=4 width=4) (actual time=0.014..0.024
rows=4 loops=1)"
" Filter: ((code)::text =
'RUN'::text)"
" Rows Removed by Filter: 84"
" Buffers: shared hit=2"
" -> Index Scan using xts_customer_activity_pkey
on xts_customer_activity sqlxtscust8_ (cost=0.42..0.44 rows=1 width=12)
(actual time=0.005..0.005 rows=1 loops=10)"
" Index Cond: (id = sqlxtsjoba0_.xtca_id)"
" Buffers: shared hit=40"
" -> Seq Scan on xts_service_process_activity
sqlxtsserv9_ (cost=0.00..5.45 rows=245 width=12) (actual time=0.002..0.093
rows=245 loops=10)"
" Buffers: shared hit=30"
" -> Seq Scan on xts_process_activity sqlxtsproc10_
(cost=0.00..2.98 rows=98 width=12) (actual time=0.002..0.034 rows=98
loops=10)"
" Buffers: shared hit=20"
" -> Seq Scan on xts_activity sqlxtsacti11_ (cost=0.00..1.59
rows=1 width=4) (actual time=0.004..0.008 rows=1 loops=10)"
" Filter: ((handler)::text =
'FinalDeliveryCheckHandlerSession'::text)"
" Rows Removed by Filter: 46"
" Buffers: shared hit=10"
"Planning time: 8.361 ms"
"Execution time: 7.669 ms"
--Rows (estimated) 133429
CREATE UNLOGGED TABLE id_helper3
(
reference_id integer,
set_id integer
)
WITH (
OIDS=FALSE
);
CREATE INDEX id_helper_set
ON id_helper3
USING btree
(set_id)
WITH (FILLFACTOR=100);
--Rows (estimated) 17843700
CREATE TABLE xts_job_activity
(
id serial NOT NULL,
xtca_id bigint NOT NULL,
skip character(1) NOT NULL DEFAULT 'N'::bpchar,
stop character(1) NOT NULL DEFAULT 'N'::bpchar,
start_date timestamp without time zone,
end_date timestamp without time zone,
due_date timestamp without time zone,
act_result character varying(25),
original_document character(1) NOT NULL,
retry_flag character(1),
last_update_date timestamp without time zone DEFAULT clock_timestamp(),
last_update_by bigint NOT NULL,
xtja_comment character varying(128),
xtps_id bigint,
sequence_id bigint,
xprs_id bigint NOT NULL,
fixed_due_date character(1) NOT NULL,
wait_player_linked character(1) NOT NULL DEFAULT 'N'::bpchar,
wait_workspec_accept character(1) NOT NULL DEFAULT 'N'::bpchar,
sequence_group smallint DEFAULT 1,
player_mail_sent character(1) NOT NULL DEFAULT 'N'::bpchar,
skippable character(1) DEFAULT 'Y'::bpchar,
need_orig_source character(1),
rollbackable character(1) NOT NULL DEFAULT 'Y'::bpchar,
CONSTRAINT xts_job_activity_pkey PRIMARY KEY (id),
CONSTRAINT xtja_xprs_fk FOREIGN KEY (xprs_id)
REFERENCES xts_status (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtja_xtca_fk FOREIGN KEY (xtca_id)
REFERENCES xts_customer_activity (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtja_xtco_fk FOREIGN KEY (last_update_by)
REFERENCES xts_contact (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtja_xtps_fk FOREIGN KEY (xtps_id)
REFERENCES xts_param_set (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (
OIDS=FALSE
);
ALTER TABLE xts_job_activity
OWNER TO tstream;
GRANT ALL ON TABLE xts_job_activity TO tstream;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xts_job_activity TO app_tstream_role;
ALTER TABLE xts_job_activity ALTER COLUMN xtca_id SET STATISTICS 2500;
ALTER TABLE xts_job_activity ALTER COLUMN last_update_by SET STATISTICS 2500;
ALTER TABLE xts_job_activity ALTER COLUMN xtps_id SET STATISTICS 2500;
ALTER TABLE xts_job_activity ALTER COLUMN xprs_id SET STATISTICS 2500;
-- Index: xtja_act_result
-- DROP INDEX xtja_act_result;
CREATE INDEX xtja_act_result
ON xts_job_activity
USING brin
(act_result COLLATE pg_catalog."default");
-- Index: xtja_start_date
-- DROP INDEX xtja_start_date;
CREATE INDEX xtja_start_date
ON xts_job_activity
USING btree
((start_date::date));
-- Index: xtja_xprs
-- DROP INDEX xtja_xprs;
CREATE INDEX xtja_xprs
ON xts_job_activity
USING brin
(xprs_id);
-- Index: xtja_xtca
-- DROP INDEX xtja_xtca;
CREATE INDEX xtja_xtca
ON xts_job_activity
USING brin
(xtca_id);
-- Index: xtja_xtco
-- DROP INDEX xtja_xtco;
CREATE INDEX xtja_xtco
ON xts_job_activity
USING btree
(last_update_by);
-- Index: xtja_xtps
-- DROP INDEX xtja_xtps;
CREATE INDEX xtja_xtps
ON xts_job_activity
USING btree
(xtps_id);
--Rows (estimated) 19628100
CREATE TABLE xts_act_in_job
(
id serial NOT NULL,
xtjp_id bigint NOT NULL,
xtja_id bigint NOT NULL,
active character(1) NOT NULL DEFAULT 'Y'::bpchar,
xtja_id_merge bigint,
CONSTRAINT xts_act_in_job_pkey PRIMARY KEY (id),
CONSTRAINT xaip_xtja_fk FOREIGN KEY (xtja_id)
REFERENCES xts_job_activity (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xaip_xtjp_fk FOREIGN KEY (xtjp_id)
REFERENCES xts_job_process (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (
OIDS=FALSE
);
ALTER TABLE xts_act_in_job
OWNER TO tstream;
GRANT ALL ON TABLE xts_act_in_job TO tstream;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xts_act_in_job TO app_tstream_role;
ALTER TABLE xts_act_in_job ALTER COLUMN xtjp_id SET STATISTICS 2500;
ALTER TABLE xts_act_in_job ALTER COLUMN xtja_id SET STATISTICS 2500;
-- Index: xaij_active
-- DROP INDEX xaij_active;
CREATE INDEX xaij_active
ON xts_act_in_job
USING brin
(active COLLATE pg_catalog."default");
-- Index: xaij_xtja
-- DROP INDEX xaij_xtja;
CREATE INDEX xaij_xtja
ON xts_act_in_job
USING btree
(xtja_id)
WITH (FILLFACTOR=98);
-- Index: xaij_xtjp
-- DROP INDEX xaij_xtjp;
CREATE INDEX xaij_xtjp
ON xts_act_in_job
USING btree
(xtjp_id)
WITH (FILLFACTOR=98);
--Rows (estimated) 1434270
CREATE TABLE xts_job_process
(
id serial NOT NULL,
xtjo_id bigint NOT NULL,
xcsp_id bigint NOT NULL,
start_date timestamp without time zone,
due_date timestamp without time zone,
end_date timestamp without time zone,
active character(1) NOT NULL DEFAULT 'Y'::bpchar,
CONSTRAINT xts_job_process_pkey PRIMARY KEY (id),
CONSTRAINT xtjp_xcsp_fk FOREIGN KEY (xcsp_id)
REFERENCES xts_customer_service_process (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtjp_xtjo_fk FOREIGN KEY (xtjo_id)
REFERENCES xts_job (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (
OIDS=FALSE
);
ALTER TABLE xts_job_process
OWNER TO tstream;
GRANT ALL ON TABLE xts_job_process TO tstream;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xts_job_process TO app_tstream_role;
ALTER TABLE xts_job_process ALTER COLUMN xtjo_id SET STATISTICS 2500;
ALTER TABLE xts_job_process ALTER COLUMN xcsp_id SET STATISTICS 2500;
-- Index: xtjp_active
-- DROP INDEX xtjp_active;
CREATE INDEX xtjp_active
ON xts_job_process
USING brin
(active COLLATE pg_catalog."default");
-- Index: xtjp_xcsp
-- DROP INDEX xtjp_xcsp;
CREATE INDEX xtjp_xcsp
ON xts_job_process
USING btree
(xcsp_id)
WITH (FILLFACTOR=95);
-- Index: xtjp_xtjo
-- DROP INDEX xtjp_xtjo;
CREATE INDEX xtjp_xtjo
ON xts_job_process
USING btree
(xtjo_id)
WITH (FILLFACTOR=95);
--Rows (estimated) 1320620
CREATE TABLE xts_job
(
id serial NOT NULL,
xlsu_id bigint NOT NULL,
xprs_id bigint NOT NULL,
name character varying(256) NOT NULL,
start_date timestamp without time zone,
end_date timestamp without time zone,
due_date timestamp without time zone,
doc_download_date timestamp without time zone,
merge_file character(1) NOT NULL DEFAULT 'N'::bpchar,
term_concordance_valid character(1),
wf_itemkey character varying(32),
wf_itemtype character varying(32),
xtjo_comment character varying(32),
block_context_matches character(1) NOT NULL DEFAULT 'N'::bpchar,
src_xtfl_id bigint,
tgt_xtfl_id bigint,
quote_reached character(1) NOT NULL DEFAULT 'N'::bpchar,
suspended character(1) NOT NULL DEFAULT 'N'::bpchar,
retry_flag character(1) NOT NULL DEFAULT 'N'::bpchar,
fdcheck_done character(1) NOT NULL DEFAULT 'N'::bpchar,
flow_end_date timestamp without time zone,
delivery_method character varying(32),
merge_type character(1),
merged_from bigint,
master_xtjo_id bigint,
CONSTRAINT xts_job_pkey PRIMARY KEY (id),
CONSTRAINT xt_xt_jxtjo_fk FOREIGN KEY (merged_from)
REFERENCES xts_job (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtjo_xlsu_fk FOREIGN KEY (xlsu_id)
REFERENCES xts_lsu (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtjo_xprs_fk FOREIGN KEY (xprs_id)
REFERENCES xts_status (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtjo_xtfi_id_tgt FOREIGN KEY (tgt_xtfl_id)
REFERENCES xts_job_file (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtjo_xtfl_fk FOREIGN KEY (src_xtfl_id)
REFERENCES xts_job_file (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtjo_xtjo_fk FOREIGN KEY (master_xtjo_id)
REFERENCES xts_job (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (
OIDS=FALSE
);
ALTER TABLE xts_job
OWNER TO tstream;
GRANT ALL ON TABLE xts_job TO tstream;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xts_job TO app_tstream_role;
ALTER TABLE xts_job ALTER COLUMN xlsu_id SET STATISTICS 2500;
ALTER TABLE xts_job ALTER COLUMN src_xtfl_id SET STATISTICS 2500;
ALTER TABLE xts_job ALTER COLUMN tgt_xtfl_id SET STATISTICS 2500;
ALTER TABLE xts_job ALTER COLUMN merged_from SET STATISTICS 2500;
ALTER TABLE xts_job ALTER COLUMN master_xtjo_id SET STATISTICS 2500;
-- Index: xtjo_master_xtjo_id
-- DROP INDEX xtjo_master_xtjo_id;
CREATE INDEX xtjo_master_xtjo_id
ON xts_job
USING btree
(master_xtjo_id)
WITH (FILLFACTOR=95)
WHERE master_xtjo_id IS NOT NULL;
-- Index: xtjo_merged
-- DROP INDEX xtjo_merged;
CREATE INDEX xtjo_merged
ON xts_job
USING btree
(merged_from)
WITH (FILLFACTOR=95)
WHERE merged_from IS NOT NULL;
-- Index: xtjo_src_xtfl
-- DROP INDEX xtjo_src_xtfl;
CREATE INDEX xtjo_src_xtfl
ON xts_job
USING btree
(src_xtfl_id)
WITH (FILLFACTOR=95);
-- Index: xtjo_start_date
-- DROP INDEX xtjo_start_date;
CREATE INDEX xtjo_start_date
ON xts_job
USING btree
((start_date::date));
-- Index: xtjo_tgt_xtfl
-- DROP INDEX xtjo_tgt_xtfl;
CREATE INDEX xtjo_tgt_xtfl
ON xts_job
USING btree
(tgt_xtfl_id)
WITH (FILLFACTOR=95)
WHERE tgt_xtfl_id IS NOT NULL;
-- Index: xtjo_xlsu
-- DROP INDEX xtjo_xlsu;
CREATE INDEX xtjo_xlsu
ON xts_job
USING btree
(xlsu_id)
WITH (FILLFACTOR=95);
-- Index: xtjo_xprs
-- DROP INDEX xtjo_xprs;
CREATE INDEX xtjo_xprs
ON xts_job
USING btree
(xprs_id)
WHERE xprs_id <> ALL (ARRAY[96::bigint, 19::bigint, 42::bigint]);
--rows (estimated) 88
CREATE TABLE xts_status
(
id serial NOT NULL,
code character varying(32) NOT NULL,
name character varying(64) NOT NULL,
value character varying(64),
active character(1) NOT NULL DEFAULT 'Y'::bpchar,
discriminator character varying(32),
CONSTRAINT xts_status_pkey PRIMARY KEY (id),
CONSTRAINT code_discriminator_uni UNIQUE (code, discriminator)
)
WITH (
OIDS=FALSE
);
ALTER TABLE xts_status
OWNER TO tstream;
GRANT ALL ON TABLE xts_status TO tstream;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xts_status TO app_tstream_role;
-- Index: xtst_name
-- DROP INDEX xtst_name;
CREATE INDEX xtst_name
ON xts_status
USING btree
(name COLLATE pg_catalog."default");
--Rows (estimated) 722867
CREATE TABLE xts_customer_activity
(
id serial NOT NULL,
xcsp_id bigint NOT NULL,
xspa_id bigint NOT NULL,
estimated_workload smallint,
last_update_date timestamp without time zone NOT NULL DEFAULT clock_timestamp(),
last_update_by bigint NOT NULL,
active character(1) NOT NULL DEFAULT 'Y'::bpchar,
default_skip character(1) NOT NULL DEFAULT 'N'::bpchar,
default_stop character(1) NOT NULL DEFAULT 'N'::bpchar,
default_acceptance_timeout integer NOT NULL,
customer_selectable character(1) NOT NULL DEFAULT 'N'::bpchar,
tm_concordance character(1) NOT NULL DEFAULT 'Y'::bpchar,
workspec_instructions character varying(4000),
CONSTRAINT xts_customer_activity_pkey PRIMARY KEY (id),
CONSTRAINT xtca_xcsp_fk FOREIGN KEY (xcsp_id)
REFERENCES xts_customer_service_process (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtca_xspa_fk FOREIGN KEY (xspa_id)
REFERENCES xts_service_process_activity (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtca_xtco_fk FOREIGN KEY (last_update_by)
REFERENCES xts_contact (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (
OIDS=FALSE
);
ALTER TABLE xts_customer_activity
OWNER TO tstream;
GRANT ALL ON TABLE xts_customer_activity TO tstream;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xts_customer_activity TO app_tstream_role;
ALTER TABLE xts_customer_activity ALTER COLUMN xcsp_id SET STATISTICS 2500;
ALTER TABLE xts_customer_activity ALTER COLUMN xspa_id SET STATISTICS 2500;
ALTER TABLE xts_customer_activity ALTER COLUMN last_update_by SET STATISTICS 2500;
-- Index: xtca_last_upd_by
-- DROP INDEX xtca_last_upd_by;
CREATE INDEX xtca_last_upd_by
ON xts_customer_activity
USING btree
(last_update_by)
WITH (FILLFACTOR=95)
WHERE last_update_by <> ALL (ARRAY[0::bigint, 1::bigint]);
-- Index: xtca_xcsp
-- DROP INDEX xtca_xcsp;
CREATE INDEX xtca_xcsp
ON xts_customer_activity
USING btree
(xcsp_id);
-- Index: xtca_xspa
-- DROP INDEX xtca_xspa;
CREATE INDEX xtca_xspa
ON xts_customer_activity
USING brin
(xspa_id);
--Rows (estimated) 245
CREATE TABLE xts_service_process_activity
(
id serial NOT NULL,
xsep_id bigint NOT NULL,
xpra_id bigint NOT NULL,
active character(1) NOT NULL DEFAULT 'Y'::bpchar,
default_acceptance_timeout bigint NOT NULL,
default_skip character(1) NOT NULL DEFAULT 'N'::bpchar,
CONSTRAINT xts_service_process_activity_pkey PRIMARY KEY (id),
CONSTRAINT xspa_xpra_fk FOREIGN KEY (xpra_id)
REFERENCES xts_process_activity (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xspa_xsep_fk FOREIGN KEY (xsep_id)
REFERENCES xts_service_process (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xspa_fk_uk UNIQUE (xpra_id, xsep_id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE xts_service_process_activity
OWNER TO tstream;
GRANT ALL ON TABLE xts_service_process_activity TO tstream;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xts_service_process_activity TO app_tstream_role;
-- Index: xspa_xpra
-- DROP INDEX xspa_xpra;
CREATE INDEX xspa_xpra
ON xts_service_process_activity
USING btree
(xpra_id)
WITH (FILLFACTOR=95);
-- Index: xspa_xsep
-- DROP INDEX xspa_xsep;
CREATE INDEX xspa_xsep
ON xts_service_process_activity
USING btree
(xsep_id)
WITH (FILLFACTOR=95);
--Rows (estimated) 98
CREATE TABLE xts_process_activity
(
id serial NOT NULL,
xpro_id bigint NOT NULL,
xtac_id bigint NOT NULL,
sequence smallint,
skippable character(1) NOT NULL,
default_timeout bigint NOT NULL,
active character(1) NOT NULL DEFAULT 'Y'::bpchar,
xtds_id bigint,
xtis_id bigint,
default_estimated_workload bigint,
group_by character varying(8),
need_latest_tm character(1) NOT NULL DEFAULT 'N'::bpchar,
name character varying(64),
need_orig_source character(1) NOT NULL DEFAULT 'N'::bpchar,
rollbackable character(1) NOT NULL DEFAULT 'Y'::bpchar,
suspendable character(1) NOT NULL DEFAULT 'N'::bpchar,
tm_concordance character(1) NOT NULL DEFAULT 'Y'::bpchar,
category_blockable character(1) NOT NULL DEFAULT 'N'::bpchar,
CONSTRAINT xts_process_activity_pkey PRIMARY KEY (id),
CONSTRAINT xpra_xpro_fk FOREIGN KEY (xpro_id)
REFERENCES xts_process (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xpra_xtac_fk FOREIGN KEY (xtac_id)
REFERENCES xts_activity (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xpra_xtis_fk FOREIGN KEY (xtis_id)
REFERENCES xts_inquiry_scheme (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtpa_xtds_fk FOREIGN KEY (xtds_id)
REFERENCES xts_dependency_set (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (
OIDS=FALSE
);
ALTER TABLE xts_process_activity
OWNER TO tstream;
GRANT ALL ON TABLE xts_process_activity TO tstream;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xts_process_activity TO app_tstream_role;
ALTER TABLE xts_process_activity ALTER COLUMN xtac_id SET STATISTICS 2500;
ALTER TABLE xts_process_activity ALTER COLUMN xtis_id SET STATISTICS 2500;
-- Index: xpra_name
-- DROP INDEX xpra_name;
CREATE INDEX xpra_name
ON xts_process_activity
USING brin
(name COLLATE pg_catalog."default");
-- Index: xpra_xpro
-- DROP INDEX xpra_xpro;
CREATE INDEX xpra_xpro
ON xts_process_activity
USING brin
(xpro_id);
-- Index: xpra_xtac
-- DROP INDEX xpra_xtac;
CREATE INDEX xpra_xtac
ON xts_process_activity
USING btree
(xtac_id);
-- Index: xpra_xtds
-- DROP INDEX xpra_xtds;
CREATE INDEX xpra_xtds
ON xts_process_activity
USING brin
(xtds_id);
-- Index: xpra_xtis
-- DROP INDEX xpra_xtis;
CREATE INDEX xpra_xtis
ON xts_process_activity
USING btree
(xtis_id)
WHERE xtis_id IS NOT NULL;
--rows (estimated) 47
CREATE TABLE xts_activity
(
id serial NOT NULL,
name character varying(32),
code character varying(32),
human_activity character(1) NOT NULL,
active character(1) NOT NULL DEFAULT 'Y'::bpchar,
system_activity character(1) NOT NULL DEFAULT 'N'::bpchar,
cost_type character varying(32) NOT NULL DEFAULT 'TRANS'::character varying,
customer_activity character(1) NOT NULL DEFAULT 'N'::bpchar,
handler character varying(32),
ignore_missing_agent character(1),
CONSTRAINT xts_activity_pkey PRIMARY KEY (id),
CONSTRAINT xtac_code_uk UNIQUE (code)
)
WITH (
OIDS=FALSE
);
ALTER TABLE xts_activity
OWNER TO tstream;
GRANT ALL ON TABLE xts_activity TO tstream;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xts_activity TO app_tstream_role;
ALTER TABLE xts_activity ALTER COLUMN code SET STATISTICS 2500;
-- result of show_all
/*
allow_system_table_mods | off | Allows modifications of the structure of system tables.
application_name | psql | Sets the application name to be reported in statistics and logs.
archive_command | (disabled) | Sets the shell command that will be called to archive a WAL file.
archive_mode | off | Allows archiving of WAL files using archive_command.
archive_timeout | 0 | Forces a switch to the next xlog file if a new file has not been started within N seconds.
array_nulls | on | Enable input of NULL elements in arrays.
authentication_timeout | 1min | Sets the maximum allowed time to complete client authentication.
autovacuum | on | Starts the autovacuum subprocess.
autovacuum_analyze_scale_factor | 0.1 | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.
autovacuum_analyze_threshold | 50 | Minimum number of tuple inserts, updates, or deletes prior to analyze.
autovacuum_freeze_max_age | 200000000 | Age at which to autovacuum a table to prevent transaction ID wraparound.
autovacuum_max_workers | 3 | Sets the maximum number of simultaneously running autovacuum worker processes.
autovacuum_multixact_freeze_max_age | 400000000 | Multixact age at which to autovacuum a table to prevent multixact wraparound.
autovacuum_naptime | 1min | Time to sleep between autovacuum runs.
autovacuum_vacuum_cost_delay | 20ms | Vacuum cost delay in milliseconds, for autovacuum.
autovacuum_vacuum_cost_limit | -1 | Vacuum cost amount available before napping, for autovacuum.
autovacuum_vacuum_scale_factor | 0.1 | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold | 50 | Minimum number of tuple updates or deletes prior to vacuum.
autovacuum_work_mem | -1 | Sets the maximum memory to be used by each autovacuum worker process.
backslash_quote | safe_encoding | Sets whether "\'" is allowed in string literals.
bgwriter_delay | 200ms | Background writer sleep time between rounds.
bgwriter_lru_maxpages | 100 | Background writer maximum number of LRU pages to flush per round.
bgwriter_lru_multiplier | 2 | Multiple of the average buffer usage to free per round.
block_size | 8192 | Shows the size of a disk block.
bonjour | off | Enables advertising the server via Bonjour.
bonjour_name | | Sets the Bonjour service name.
bytea_output | hex | Sets the output format for bytea.
check_function_bodies | on | Check function bodies during CREATE FUNCTION.
checkpoint_completion_target | 0.9 | Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint inter
checkpoint_timeout | 5min | Sets the maximum time between automatic WAL checkpoints.
checkpoint_warning | 30s | Enables warnings if checkpoint segments are filled more frequently than this.
client_encoding | UTF8 | Sets the client's character set encoding.
client_min_messages | notice | Sets the message levels that are sent to the client.
cluster_name | | Sets the name of the cluster, which is included in the process title.
commit_delay | 0 | Sets the delay in microseconds between transaction commit and flushing WAL to disk.
commit_siblings | 5 | Sets the minimum concurrent open transactions before performing commit_delay.
config_file | /space/postgres/postgresql.conf | Sets the server's main configuration file.
constraint_exclusion | partition | Enables the planner to use constraints to optimize queries.
cpu_index_tuple_cost | 0.005 | Sets the planner's estimate of the cost of processing each index entry during an index scan.
cpu_operator_cost | 0.0025 | Sets the planner's estimate of the cost of processing each operator or function call.
cpu_tuple_cost | 0.01 | Sets the planner's estimate of the cost of processing each tuple (row).
cursor_tuple_fraction | 0.1 | Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved.
data_checksums | on | Shows whether data checksums are turned on for this cluster.
data_directory | /space/postgres | Sets the server's data directory.
DateStyle | ISO, MDY | Sets the display format for date and time values.
db_user_namespace | off | Enables per-database user names.
deadlock_timeout | 1s | Sets the time to wait on a lock before checking for deadlock.
debug_assertions | off | Shows whether the running server has assertion checks enabled.
debug_pretty_print | on | Indents parse and plan tree displays.
debug_print_parse | off | Logs each query's parse tree.
debug_print_plan | off | Logs each query's execution plan.
debug_print_rewritten | off | Logs each query's rewritten parse tree.
default_statistics_target | 250 | Sets the default statistics target.
default_tablespace | | Sets the default tablespace to create tables and indexes in.
default_text_search_config | pg_catalog.english | Sets default text search configuration.
default_transaction_deferrable | off | Sets the default deferrable status of new transactions.
default_transaction_isolation | read committed | Sets the transaction isolation level of each new transaction.
default_transaction_read_only | off | Sets the default read-only status of new transactions.
default_with_oids | off | Create new tables with OIDs by default.
dynamic_library_path | $libdir | Sets the path for dynamically loadable modules.
dynamic_shared_memory_type | posix | Selects the dynamic shared memory implementation used.
effective_cache_size | 6GB | Sets the planner's assumption about the size of the disk cache.
effective_io_concurrency | 4 | Number of simultaneous requests that can be handled efficiently by the disk subsystem.
enable_bitmapscan | on | Enables the planner's use of bitmap-scan plans.
enable_hashagg | on | Enables the planner's use of hashed aggregation plans.
enable_hashjoin | on | Enables the planner's use of hash join plans.
enable_indexonlyscan | on | Enables the planner's use of index-only-scan plans.
enable_indexscan | on | Enables the planner's use of index-scan plans.
enable_material | on | Enables the planner's use of materialization.
enable_mergejoin | on | Enables the planner's use of merge join plans.
enable_nestloop | on | Enables the planner's use of nested-loop join plans.
enable_seqscan | on | Enables the planner's use of sequential-scan plans.
enable_sort | on | Enables the planner's use of explicit sort steps.
enable_tidscan | on | Enables the planner's use of TID scan plans.
escape_string_warning | on | Warn about backslash escapes in ordinary string literals.
event_source | PostgreSQL | Sets the application name used to identify PostgreSQL messages in the event log.
exit_on_error | off | Terminate session on any error.
external_pid_file | | Writes the postmaster PID to the specified file.
extra_float_digits | 0 | Sets the number of digits displayed for floating-point values.
from_collapse_limit | 8 | Sets the FROM-list size beyond which subqueries are not collapsed.
fsync | on | Forces synchronization of updates to disk.
full_page_writes | on | Writes full pages to WAL when first modified after a checkpoint.
geqo | on | Enables genetic query optimization.
geqo_effort | 5 | GEQO: effort is used to set the default for other GEQO parameters.
geqo_generations | 0 | GEQO: number of iterations of the algorithm.
geqo_pool_size | 0 | GEQO: number of individuals in the population.
geqo_seed | 0 | GEQO: seed for random path selection.
geqo_selection_bias | 2 | GEQO: selective pressure within the population.
geqo_threshold | 24 | Sets the threshold of FROM items beyond which GEQO is used.
gin_fuzzy_search_limit | 0 | Sets the maximum allowed result for exact search by GIN.
gin_pending_list_limit | 4MB | Sets the maximum size of the pending list for GIN index.
hba_file | /space/postgres/pg_hba.conf | Sets the server's "hba" configuration file.
hot_standby | off | Allows connections and queries during recovery.
hot_standby_feedback | off | Allows feedback from a hot standby to the primary that will avoid query conflicts.
huge_pages | try | Use of huge pages on Linux.
ident_file | /space/postgres/pg_ident.conf | Sets the server's "ident" configuration file.
ignore_checksum_failure | off | Continues processing after a checksum failure.
ignore_system_indexes | off | Disables reading from system indexes.
integer_datetimes | on | Datetimes are integer based.
IntervalStyle | postgres | Sets the display format for interval values.
join_collapse_limit | 8 | Sets the FROM-list size beyond which JOIN constructs are not flattened.
krb_caseins_users | off | Sets whether Kerberos and GSSAPI user names should be treated as case-insensitive.
krb_server_keyfile | FILE:/etc/sysconfig/pgsql/krb5.keytab | Sets the location of the Kerberos server key file.
lc_collate | en_US.UTF-8 | Shows the collation order locale.
lc_ctype | en_US.UTF-8 | Shows the character classification and case conversion locale.
lc_messages | en_US.UTF-8 | Sets the language in which messages are displayed.
lc_monetary | en_US.UTF-8 | Sets the locale for formatting monetary amounts.
lc_numeric | en_US.UTF-8 | Sets the locale for formatting numbers.
lc_time | en_US.UTF-8 | Sets the locale for formatting date and time values.
listen_addresses | * | Sets the host name or IP address(es) to listen to.
lo_compat_privileges | off | Enables backward compatibility mode for privilege checks on large objects.
local_preload_libraries | | Lists unprivileged shared libraries to preload into each backend.
lock_timeout | 0 | Sets the maximum allowed duration of any wait for a lock.
log_autovacuum_min_duration | 3s | Sets the minimum execution time above which autovacuum actions will be logged.
log_checkpoints | on | Logs each checkpoint.
log_connections | on | Logs each successful connection.
log_destination | stderr | Sets the destination for server log output.
log_directory | pg_log | Sets the destination directory for log files.
log_disconnections | on | Logs end of a session, including duration.
log_duration | off | Logs the duration of each completed SQL statement.
log_error_verbosity | default | Sets the verbosity of logged messages.
log_executor_stats | off | Writes executor performance statistics to the server log.
log_file_mode | 0600 | Sets the file permissions for log files.
log_filename | postgresql-%Y-%m-%d_%H%M%S.log | Sets the file name pattern for log files.
log_hostname | off | Logs the host name in the connection logs.
log_line_prefix | < %m > | Controls information prefixed to each log line.
log_lock_waits | off | Logs long lock waits.
log_min_duration_statement | 3min | Sets the minimum execution time above which statements will be logged.
log_min_error_statement | error | Causes all statements generating error at or above this level to be logged.
log_min_messages | warning | Sets the message levels that are logged.
log_parser_stats | off | Writes parser performance statistics to the server log.
log_planner_stats | off | Writes planner performance statistics to the server log.
log_replication_commands | off | Logs each replication command.
log_rotation_age | 1d | Automatic log file rotation will occur after N minutes.
log_rotation_size | 0 | Automatic log file rotation will occur after N kilobytes.
log_statement | none | Sets the type of statements logged.
log_statement_stats | off | Writes cumulative performance statistics to the server log.
log_temp_files | -1 | Log the use of temporary files larger than this number of kilobytes.
log_timezone | Europe/Brussels | Sets the time zone to use in log messages.
log_truncate_on_rotation | on | Truncate existing log files of same name during log rotation.
logging_collector | on | Start a subprocess to capture stderr output and/or csvlogs into log files.
maintenance_work_mem | 64MB | Sets the maximum memory to be used for maintenance operations.
max_connections | 50 | Sets the maximum number of concurrent connections.
max_files_per_process | 1000 | Sets the maximum number of simultaneously open files for each server process.
max_function_args | 100 | Shows the maximum number of function arguments.
max_identifier_length | 63 | Shows the maximum identifier length.
max_index_keys | 32 | Shows the maximum number of index keys.
max_locks_per_transaction | 64 | Sets the maximum number of locks per transaction.
max_pred_locks_per_transaction | 64 | Sets the maximum number of predicate locks per transaction.
max_prepared_transactions | 0 | Sets the maximum number of simultaneously prepared transactions.
max_replication_slots | 0 | Sets the maximum number of simultaneously defined replication slots.
max_stack_depth | 4MB | Sets the maximum stack depth, in kilobytes.
max_standby_archive_delay | 30s | Sets the maximum delay before canceling queries when a hot standby server is processing archived WAL da
ta.
max_standby_streaming_delay | 30s | Sets the maximum delay before canceling queries when a hot standby server is processing streamed WAL da
ta.
max_wal_senders | 0 | Sets the maximum number of simultaneously running WAL sender processes.
max_wal_size | 1GB | Sets the WAL size that triggers a checkpoint.
max_worker_processes | 8 | Maximum number of concurrent worker processes.
min_wal_size | 80MB | Sets the minimum size to shrink the WAL to.
operator_precedence_warning | off | Emit a warning for constructs that changed meaning since PostgreSQL 9.4.
password_encryption | on | Encrypt passwords.
pg_stat_statements.max | 5000 | Sets the maximum number of statements tracked by pg_stat_statements.
pg_stat_statements.save | on | Save pg_stat_statements statistics across server shutdowns.
pg_stat_statements.track | top | Selects which statements are tracked by pg_stat_statements.
pg_stat_statements.track_utility | on | Selects whether utility commands are tracked by pg_stat_statements.
port | 5432 | Sets the TCP port the server listens on.
post_auth_delay | 0 | Waits N seconds on connection startup after authentication.
pre_auth_delay | 0 | Waits N seconds on connection startup before authentication.
quote_all_identifiers | off | When generating SQL fragments, quote all identifiers.
random_page_cost | 4 | Sets the planner's estimate of the cost of a nonsequentially fetched disk page.
restart_after_crash | on | Reinitialize server after backend crash.
row_security | on | Enable row security.
search_path | "$user", public | Sets the schema search order for names that are not schema-qualified.
segment_size | 1GB | Shows the number of pages per disk file.
seq_page_cost | 1 | Sets the planner's estimate of the cost of a sequentially fetched disk page.
server_encoding | UTF8 | Sets the server (database) character set encoding.
server_version | 9.5.3 | Shows the server version.
server_version_num | 90503 | Shows the server version as an integer.
session_preload_libraries | | Lists shared libraries to preload into each backend.
session_replication_role | origin | Sets the session's behavior for triggers and rewrite rules.
shared_buffers | 2GB | Sets the number of shared memory buffers used by the server.
shared_preload_libraries | pg_stat_statements | Lists shared libraries to preload into server.
sql_inheritance | on | Causes subtables to be included by default in various commands.
ssl | off | Enables SSL connections.
ssl_ca_file | | Location of the SSL certificate authority file.
ssl_cert_file | server.crt | Location of the SSL server certificate file.
ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL | Sets the list of allowed SSL ciphers.
ssl_crl_file | | Location of the SSL certificate revocation list file.
ssl_ecdh_curve | prime256v1 | Sets the curve to use for ECDH.
ssl_key_file | server.key | Location of the SSL server private key file.
ssl_prefer_server_ciphers | on | Give priority to server ciphersuite order.
standard_conforming_strings | on | Causes '...' strings to treat backslashes literally.
statement_timeout | 0 | Sets the maximum allowed duration of any statement.
stats_temp_directory | pg_stat_tmp | Writes temporary statistics files to the specified directory.
superuser_reserved_connections | 3 | Sets the number of connection slots reserved for superusers.
synchronize_seqscans | on | Enable synchronized sequential scans.
synchronous_commit | off | Sets the current transaction's synchronization level.
synchronous_standby_names | | List of names of potential synchronous standbys.
syslog_facility | local0 | Sets the syslog "facility" to be used when syslog enabled.
syslog_ident | postgres | Sets the program name used to identify PostgreSQL messages in syslog.
tcp_keepalives_count | 0 | Maximum number of TCP keepalive retransmits.
tcp_keepalives_idle | 0 | Time between issuing TCP keepalives.
tcp_keepalives_interval | 0 | Time between TCP keepalive retransmits.
temp_buffers | 32MB | Sets the maximum number of temporary buffers used by each session.
temp_file_limit | -1 | Limits the total size of all temporary files used by each session.
temp_tablespaces | | Sets the tablespace(s) to use for temporary tables and sort files.
TimeZone | Europe/Brussels | Sets the time zone for displaying and interpreting time stamps.
timezone_abbreviations | Default | Selects a file of time zone abbreviations.
trace_notify | off | Generates debugging output for LISTEN and NOTIFY.
trace_recovery_messages | log | Enables logging of recovery-related debugging information.
trace_sort | off | Emit information about resource usage in sorting.
track_activities | on | Collects information about executing commands.
track_activity_query_size | 16384 | Sets the size reserved for pg_stat_activity.query, in bytes.
track_commit_timestamp | off | Collects transaction commit time.
track_counts | on | Collects statistics on database activity.
track_functions | all | Collects function-level statistics on database activity.
track_io_timing | on | Collects timing statistics for database I/O activity.
transaction_deferrable | off | Whether to defer a read-only serializable transaction until it can be executed with no possible seriali
zation failures.
transaction_isolation | read committed | Sets the current transaction's isolation level.
transaction_read_only | off | Sets the current transaction's read-only status.
transform_null_equals | off | Treats "expr=NULL" as "expr IS NULL".
unix_socket_directories | /var/run/postgresql, /tmp | Sets the directories where Unix-domain sockets will be created.
unix_socket_group | | Sets the owning group of the Unix-domain socket.
unix_socket_permissions | 0777 | Sets the access permissions of the Unix-domain socket.
update_process_title | on | Updates the process title to show the active SQL command.
vacuum_cost_delay | 0 | Vacuum cost delay in milliseconds.
vacuum_cost_limit | 200 | Vacuum cost amount available before napping.
vacuum_cost_page_dirty | 20 | Vacuum cost for a page dirtied by vacuum.
vacuum_cost_page_hit | 1 | Vacuum cost for a page found in the buffer cache.
vacuum_cost_page_miss | 10 | Vacuum cost for a page not found in the buffer cache.
vacuum_defer_cleanup_age | 0 | Number of transactions by which VACUUM and HOT cleanup should be deferred, if any.
vacuum_freeze_min_age | 50000000 | Minimum age at which VACUUM should freeze a table row.
vacuum_freeze_table_age | 150000000 | Age at which VACUUM should scan whole table to freeze tuples.
vacuum_multixact_freeze_min_age | 5000000 | Minimum age at which VACUUM should freeze a MultiXactId in a table row.
vacuum_multixact_freeze_table_age | 150000000 | Multixact age at which VACUUM should scan whole table to freeze tuples.
wal_block_size | 8192 | Shows the block size in the write ahead log.
wal_buffers | 16MB | Sets the number of disk-page buffers in shared memory for WAL.
wal_compression | on | Compresses full-page writes written in WAL file.
wal_keep_segments | 0 | Sets the number of WAL files held for standby servers.
wal_level | minimal | Set the level of information written to the WAL.
wal_log_hints | off | Writes full pages to WAL when first modified after a checkpoint, even for a non-critical modifications.
wal_receiver_status_interval | 10s | Sets the maximum interval between WAL receiver status reports to the primary.
wal_receiver_timeout | 1min | Sets the maximum wait time to receive data from the primary.
wal_retrieve_retry_interval | 5s | Sets the time to wait before retrying to retrieve WAL after a failed attempt.
wal_segment_size | 16MB | Shows the number of pages per write ahead log segment.
wal_sender_timeout | 1min | Sets the maximum time to wait for WAL replication.
wal_sync_method | fdatasync | Selects the method used for forcing WAL updates to disk.
wal_writer_delay | 200ms | WAL writer sleep time between WAL flushes.
work_mem | 64MB | Sets the maximum memory to be used for query workspaces.
xmlbinary | base64 | Sets how binary values are to be encoded in XML.
xmloption | content | Sets whether XML data in implicit parsing and serialization operations is to be considered as documents
or content fragments.
zero_damaged_pages | off | Continues processing past damaged page headers.
*/
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Attachments:
[text/plain] info_id_helper_problem.txt (51.1K, ../../CAKaDz_EVOgNE7NkBeP=VUpnz5KEBzNgujGcqCBRehP945nXhOw@mail.gmail.com/3-info_id_helper_problem.txt)
download | inline:
--Rows (estimated) 133429
CREATE UNLOGGED TABLE id_helper3
(
reference_id integer,
set_id integer
)
WITH (
OIDS=FALSE
);
CREATE INDEX id_helper_set
ON id_helper3
USING btree
(set_id)
WITH (FILLFACTOR=100);
--Rows (estimated) 17843700
CREATE TABLE xts_job_activity
(
id serial NOT NULL,
xtca_id bigint NOT NULL,
skip character(1) NOT NULL DEFAULT 'N'::bpchar,
stop character(1) NOT NULL DEFAULT 'N'::bpchar,
start_date timestamp without time zone,
end_date timestamp without time zone,
due_date timestamp without time zone,
act_result character varying(25),
original_document character(1) NOT NULL,
retry_flag character(1),
last_update_date timestamp without time zone DEFAULT clock_timestamp(),
last_update_by bigint NOT NULL,
xtja_comment character varying(128),
xtps_id bigint,
sequence_id bigint,
xprs_id bigint NOT NULL,
fixed_due_date character(1) NOT NULL,
wait_player_linked character(1) NOT NULL DEFAULT 'N'::bpchar,
wait_workspec_accept character(1) NOT NULL DEFAULT 'N'::bpchar,
sequence_group smallint DEFAULT 1,
player_mail_sent character(1) NOT NULL DEFAULT 'N'::bpchar,
skippable character(1) DEFAULT 'Y'::bpchar,
need_orig_source character(1),
rollbackable character(1) NOT NULL DEFAULT 'Y'::bpchar,
CONSTRAINT xts_job_activity_pkey PRIMARY KEY (id),
CONSTRAINT xtja_xprs_fk FOREIGN KEY (xprs_id)
REFERENCES xts_status (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtja_xtca_fk FOREIGN KEY (xtca_id)
REFERENCES xts_customer_activity (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtja_xtco_fk FOREIGN KEY (last_update_by)
REFERENCES xts_contact (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtja_xtps_fk FOREIGN KEY (xtps_id)
REFERENCES xts_param_set (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (
OIDS=FALSE
);
ALTER TABLE xts_job_activity
OWNER TO tstream;
GRANT ALL ON TABLE xts_job_activity TO tstream;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xts_job_activity TO app_tstream_role;
ALTER TABLE xts_job_activity ALTER COLUMN xtca_id SET STATISTICS 2500;
ALTER TABLE xts_job_activity ALTER COLUMN last_update_by SET STATISTICS 2500;
ALTER TABLE xts_job_activity ALTER COLUMN xtps_id SET STATISTICS 2500;
ALTER TABLE xts_job_activity ALTER COLUMN xprs_id SET STATISTICS 2500;
-- Index: xtja_act_result
-- DROP INDEX xtja_act_result;
CREATE INDEX xtja_act_result
ON xts_job_activity
USING brin
(act_result COLLATE pg_catalog."default");
-- Index: xtja_start_date
-- DROP INDEX xtja_start_date;
CREATE INDEX xtja_start_date
ON xts_job_activity
USING btree
((start_date::date));
-- Index: xtja_xprs
-- DROP INDEX xtja_xprs;
CREATE INDEX xtja_xprs
ON xts_job_activity
USING brin
(xprs_id);
-- Index: xtja_xtca
-- DROP INDEX xtja_xtca;
CREATE INDEX xtja_xtca
ON xts_job_activity
USING brin
(xtca_id);
-- Index: xtja_xtco
-- DROP INDEX xtja_xtco;
CREATE INDEX xtja_xtco
ON xts_job_activity
USING btree
(last_update_by);
-- Index: xtja_xtps
-- DROP INDEX xtja_xtps;
CREATE INDEX xtja_xtps
ON xts_job_activity
USING btree
(xtps_id);
--Rows (estimated) 19628100
CREATE TABLE xts_act_in_job
(
id serial NOT NULL,
xtjp_id bigint NOT NULL,
xtja_id bigint NOT NULL,
active character(1) NOT NULL DEFAULT 'Y'::bpchar,
xtja_id_merge bigint,
CONSTRAINT xts_act_in_job_pkey PRIMARY KEY (id),
CONSTRAINT xaip_xtja_fk FOREIGN KEY (xtja_id)
REFERENCES xts_job_activity (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xaip_xtjp_fk FOREIGN KEY (xtjp_id)
REFERENCES xts_job_process (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (
OIDS=FALSE
);
ALTER TABLE xts_act_in_job
OWNER TO tstream;
GRANT ALL ON TABLE xts_act_in_job TO tstream;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xts_act_in_job TO app_tstream_role;
ALTER TABLE xts_act_in_job ALTER COLUMN xtjp_id SET STATISTICS 2500;
ALTER TABLE xts_act_in_job ALTER COLUMN xtja_id SET STATISTICS 2500;
-- Index: xaij_active
-- DROP INDEX xaij_active;
CREATE INDEX xaij_active
ON xts_act_in_job
USING brin
(active COLLATE pg_catalog."default");
-- Index: xaij_xtja
-- DROP INDEX xaij_xtja;
CREATE INDEX xaij_xtja
ON xts_act_in_job
USING btree
(xtja_id)
WITH (FILLFACTOR=98);
-- Index: xaij_xtjp
-- DROP INDEX xaij_xtjp;
CREATE INDEX xaij_xtjp
ON xts_act_in_job
USING btree
(xtjp_id)
WITH (FILLFACTOR=98);
--Rows (estimated) 1434270
CREATE TABLE xts_job_process
(
id serial NOT NULL,
xtjo_id bigint NOT NULL,
xcsp_id bigint NOT NULL,
start_date timestamp without time zone,
due_date timestamp without time zone,
end_date timestamp without time zone,
active character(1) NOT NULL DEFAULT 'Y'::bpchar,
CONSTRAINT xts_job_process_pkey PRIMARY KEY (id),
CONSTRAINT xtjp_xcsp_fk FOREIGN KEY (xcsp_id)
REFERENCES xts_customer_service_process (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtjp_xtjo_fk FOREIGN KEY (xtjo_id)
REFERENCES xts_job (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (
OIDS=FALSE
);
ALTER TABLE xts_job_process
OWNER TO tstream;
GRANT ALL ON TABLE xts_job_process TO tstream;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xts_job_process TO app_tstream_role;
ALTER TABLE xts_job_process ALTER COLUMN xtjo_id SET STATISTICS 2500;
ALTER TABLE xts_job_process ALTER COLUMN xcsp_id SET STATISTICS 2500;
-- Index: xtjp_active
-- DROP INDEX xtjp_active;
CREATE INDEX xtjp_active
ON xts_job_process
USING brin
(active COLLATE pg_catalog."default");
-- Index: xtjp_xcsp
-- DROP INDEX xtjp_xcsp;
CREATE INDEX xtjp_xcsp
ON xts_job_process
USING btree
(xcsp_id)
WITH (FILLFACTOR=95);
-- Index: xtjp_xtjo
-- DROP INDEX xtjp_xtjo;
CREATE INDEX xtjp_xtjo
ON xts_job_process
USING btree
(xtjo_id)
WITH (FILLFACTOR=95);
--Rows (estimated) 1320620
CREATE TABLE xts_job
(
id serial NOT NULL,
xlsu_id bigint NOT NULL,
xprs_id bigint NOT NULL,
name character varying(256) NOT NULL,
start_date timestamp without time zone,
end_date timestamp without time zone,
due_date timestamp without time zone,
doc_download_date timestamp without time zone,
merge_file character(1) NOT NULL DEFAULT 'N'::bpchar,
term_concordance_valid character(1),
wf_itemkey character varying(32),
wf_itemtype character varying(32),
xtjo_comment character varying(32),
block_context_matches character(1) NOT NULL DEFAULT 'N'::bpchar,
src_xtfl_id bigint,
tgt_xtfl_id bigint,
quote_reached character(1) NOT NULL DEFAULT 'N'::bpchar,
suspended character(1) NOT NULL DEFAULT 'N'::bpchar,
retry_flag character(1) NOT NULL DEFAULT 'N'::bpchar,
fdcheck_done character(1) NOT NULL DEFAULT 'N'::bpchar,
flow_end_date timestamp without time zone,
delivery_method character varying(32),
merge_type character(1),
merged_from bigint,
master_xtjo_id bigint,
CONSTRAINT xts_job_pkey PRIMARY KEY (id),
CONSTRAINT xt_xt_jxtjo_fk FOREIGN KEY (merged_from)
REFERENCES xts_job (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtjo_xlsu_fk FOREIGN KEY (xlsu_id)
REFERENCES xts_lsu (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtjo_xprs_fk FOREIGN KEY (xprs_id)
REFERENCES xts_status (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtjo_xtfi_id_tgt FOREIGN KEY (tgt_xtfl_id)
REFERENCES xts_job_file (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtjo_xtfl_fk FOREIGN KEY (src_xtfl_id)
REFERENCES xts_job_file (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtjo_xtjo_fk FOREIGN KEY (master_xtjo_id)
REFERENCES xts_job (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (
OIDS=FALSE
);
ALTER TABLE xts_job
OWNER TO tstream;
GRANT ALL ON TABLE xts_job TO tstream;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xts_job TO app_tstream_role;
ALTER TABLE xts_job ALTER COLUMN xlsu_id SET STATISTICS 2500;
ALTER TABLE xts_job ALTER COLUMN src_xtfl_id SET STATISTICS 2500;
ALTER TABLE xts_job ALTER COLUMN tgt_xtfl_id SET STATISTICS 2500;
ALTER TABLE xts_job ALTER COLUMN merged_from SET STATISTICS 2500;
ALTER TABLE xts_job ALTER COLUMN master_xtjo_id SET STATISTICS 2500;
-- Index: xtjo_master_xtjo_id
-- DROP INDEX xtjo_master_xtjo_id;
CREATE INDEX xtjo_master_xtjo_id
ON xts_job
USING btree
(master_xtjo_id)
WITH (FILLFACTOR=95)
WHERE master_xtjo_id IS NOT NULL;
-- Index: xtjo_merged
-- DROP INDEX xtjo_merged;
CREATE INDEX xtjo_merged
ON xts_job
USING btree
(merged_from)
WITH (FILLFACTOR=95)
WHERE merged_from IS NOT NULL;
-- Index: xtjo_src_xtfl
-- DROP INDEX xtjo_src_xtfl;
CREATE INDEX xtjo_src_xtfl
ON xts_job
USING btree
(src_xtfl_id)
WITH (FILLFACTOR=95);
-- Index: xtjo_start_date
-- DROP INDEX xtjo_start_date;
CREATE INDEX xtjo_start_date
ON xts_job
USING btree
((start_date::date));
-- Index: xtjo_tgt_xtfl
-- DROP INDEX xtjo_tgt_xtfl;
CREATE INDEX xtjo_tgt_xtfl
ON xts_job
USING btree
(tgt_xtfl_id)
WITH (FILLFACTOR=95)
WHERE tgt_xtfl_id IS NOT NULL;
-- Index: xtjo_xlsu
-- DROP INDEX xtjo_xlsu;
CREATE INDEX xtjo_xlsu
ON xts_job
USING btree
(xlsu_id)
WITH (FILLFACTOR=95);
-- Index: xtjo_xprs
-- DROP INDEX xtjo_xprs;
CREATE INDEX xtjo_xprs
ON xts_job
USING btree
(xprs_id)
WHERE xprs_id <> ALL (ARRAY[96::bigint, 19::bigint, 42::bigint]);
--rows (estimated) 88
CREATE TABLE xts_status
(
id serial NOT NULL,
code character varying(32) NOT NULL,
name character varying(64) NOT NULL,
value character varying(64),
active character(1) NOT NULL DEFAULT 'Y'::bpchar,
discriminator character varying(32),
CONSTRAINT xts_status_pkey PRIMARY KEY (id),
CONSTRAINT code_discriminator_uni UNIQUE (code, discriminator)
)
WITH (
OIDS=FALSE
);
ALTER TABLE xts_status
OWNER TO tstream;
GRANT ALL ON TABLE xts_status TO tstream;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xts_status TO app_tstream_role;
-- Index: xtst_name
-- DROP INDEX xtst_name;
CREATE INDEX xtst_name
ON xts_status
USING btree
(name COLLATE pg_catalog."default");
--Rows (estimated) 722867
CREATE TABLE xts_customer_activity
(
id serial NOT NULL,
xcsp_id bigint NOT NULL,
xspa_id bigint NOT NULL,
estimated_workload smallint,
last_update_date timestamp without time zone NOT NULL DEFAULT clock_timestamp(),
last_update_by bigint NOT NULL,
active character(1) NOT NULL DEFAULT 'Y'::bpchar,
default_skip character(1) NOT NULL DEFAULT 'N'::bpchar,
default_stop character(1) NOT NULL DEFAULT 'N'::bpchar,
default_acceptance_timeout integer NOT NULL,
customer_selectable character(1) NOT NULL DEFAULT 'N'::bpchar,
tm_concordance character(1) NOT NULL DEFAULT 'Y'::bpchar,
workspec_instructions character varying(4000),
CONSTRAINT xts_customer_activity_pkey PRIMARY KEY (id),
CONSTRAINT xtca_xcsp_fk FOREIGN KEY (xcsp_id)
REFERENCES xts_customer_service_process (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtca_xspa_fk FOREIGN KEY (xspa_id)
REFERENCES xts_service_process_activity (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtca_xtco_fk FOREIGN KEY (last_update_by)
REFERENCES xts_contact (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (
OIDS=FALSE
);
ALTER TABLE xts_customer_activity
OWNER TO tstream;
GRANT ALL ON TABLE xts_customer_activity TO tstream;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xts_customer_activity TO app_tstream_role;
ALTER TABLE xts_customer_activity ALTER COLUMN xcsp_id SET STATISTICS 2500;
ALTER TABLE xts_customer_activity ALTER COLUMN xspa_id SET STATISTICS 2500;
ALTER TABLE xts_customer_activity ALTER COLUMN last_update_by SET STATISTICS 2500;
-- Index: xtca_last_upd_by
-- DROP INDEX xtca_last_upd_by;
CREATE INDEX xtca_last_upd_by
ON xts_customer_activity
USING btree
(last_update_by)
WITH (FILLFACTOR=95)
WHERE last_update_by <> ALL (ARRAY[0::bigint, 1::bigint]);
-- Index: xtca_xcsp
-- DROP INDEX xtca_xcsp;
CREATE INDEX xtca_xcsp
ON xts_customer_activity
USING btree
(xcsp_id);
-- Index: xtca_xspa
-- DROP INDEX xtca_xspa;
CREATE INDEX xtca_xspa
ON xts_customer_activity
USING brin
(xspa_id);
--Rows (estimated) 245
CREATE TABLE xts_service_process_activity
(
id serial NOT NULL,
xsep_id bigint NOT NULL,
xpra_id bigint NOT NULL,
active character(1) NOT NULL DEFAULT 'Y'::bpchar,
default_acceptance_timeout bigint NOT NULL,
default_skip character(1) NOT NULL DEFAULT 'N'::bpchar,
CONSTRAINT xts_service_process_activity_pkey PRIMARY KEY (id),
CONSTRAINT xspa_xpra_fk FOREIGN KEY (xpra_id)
REFERENCES xts_process_activity (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xspa_xsep_fk FOREIGN KEY (xsep_id)
REFERENCES xts_service_process (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xspa_fk_uk UNIQUE (xpra_id, xsep_id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE xts_service_process_activity
OWNER TO tstream;
GRANT ALL ON TABLE xts_service_process_activity TO tstream;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xts_service_process_activity TO app_tstream_role;
-- Index: xspa_xpra
-- DROP INDEX xspa_xpra;
CREATE INDEX xspa_xpra
ON xts_service_process_activity
USING btree
(xpra_id)
WITH (FILLFACTOR=95);
-- Index: xspa_xsep
-- DROP INDEX xspa_xsep;
CREATE INDEX xspa_xsep
ON xts_service_process_activity
USING btree
(xsep_id)
WITH (FILLFACTOR=95);
--Rows (estimated) 98
CREATE TABLE xts_process_activity
(
id serial NOT NULL,
xpro_id bigint NOT NULL,
xtac_id bigint NOT NULL,
sequence smallint,
skippable character(1) NOT NULL,
default_timeout bigint NOT NULL,
active character(1) NOT NULL DEFAULT 'Y'::bpchar,
xtds_id bigint,
xtis_id bigint,
default_estimated_workload bigint,
group_by character varying(8),
need_latest_tm character(1) NOT NULL DEFAULT 'N'::bpchar,
name character varying(64),
need_orig_source character(1) NOT NULL DEFAULT 'N'::bpchar,
rollbackable character(1) NOT NULL DEFAULT 'Y'::bpchar,
suspendable character(1) NOT NULL DEFAULT 'N'::bpchar,
tm_concordance character(1) NOT NULL DEFAULT 'Y'::bpchar,
category_blockable character(1) NOT NULL DEFAULT 'N'::bpchar,
CONSTRAINT xts_process_activity_pkey PRIMARY KEY (id),
CONSTRAINT xpra_xpro_fk FOREIGN KEY (xpro_id)
REFERENCES xts_process (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xpra_xtac_fk FOREIGN KEY (xtac_id)
REFERENCES xts_activity (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xpra_xtis_fk FOREIGN KEY (xtis_id)
REFERENCES xts_inquiry_scheme (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT xtpa_xtds_fk FOREIGN KEY (xtds_id)
REFERENCES xts_dependency_set (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (
OIDS=FALSE
);
ALTER TABLE xts_process_activity
OWNER TO tstream;
GRANT ALL ON TABLE xts_process_activity TO tstream;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xts_process_activity TO app_tstream_role;
ALTER TABLE xts_process_activity ALTER COLUMN xtac_id SET STATISTICS 2500;
ALTER TABLE xts_process_activity ALTER COLUMN xtis_id SET STATISTICS 2500;
-- Index: xpra_name
-- DROP INDEX xpra_name;
CREATE INDEX xpra_name
ON xts_process_activity
USING brin
(name COLLATE pg_catalog."default");
-- Index: xpra_xpro
-- DROP INDEX xpra_xpro;
CREATE INDEX xpra_xpro
ON xts_process_activity
USING brin
(xpro_id);
-- Index: xpra_xtac
-- DROP INDEX xpra_xtac;
CREATE INDEX xpra_xtac
ON xts_process_activity
USING btree
(xtac_id);
-- Index: xpra_xtds
-- DROP INDEX xpra_xtds;
CREATE INDEX xpra_xtds
ON xts_process_activity
USING brin
(xtds_id);
-- Index: xpra_xtis
-- DROP INDEX xpra_xtis;
CREATE INDEX xpra_xtis
ON xts_process_activity
USING btree
(xtis_id)
WHERE xtis_id IS NOT NULL;
--rows (estimated) 47
CREATE TABLE xts_activity
(
id serial NOT NULL,
name character varying(32),
code character varying(32),
human_activity character(1) NOT NULL,
active character(1) NOT NULL DEFAULT 'Y'::bpchar,
system_activity character(1) NOT NULL DEFAULT 'N'::bpchar,
cost_type character varying(32) NOT NULL DEFAULT 'TRANS'::character varying,
customer_activity character(1) NOT NULL DEFAULT 'N'::bpchar,
handler character varying(32),
ignore_missing_agent character(1),
CONSTRAINT xts_activity_pkey PRIMARY KEY (id),
CONSTRAINT xtac_code_uk UNIQUE (code)
)
WITH (
OIDS=FALSE
);
ALTER TABLE xts_activity
OWNER TO tstream;
GRANT ALL ON TABLE xts_activity TO tstream;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xts_activity TO app_tstream_role;
ALTER TABLE xts_activity ALTER COLUMN code SET STATISTICS 2500;
-- result of show_all
/*
allow_system_table_mods | off | Allows modifications of the structure of system tables.
application_name | psql | Sets the application name to be reported in statistics and logs.
archive_command | (disabled) | Sets the shell command that will be called to archive a WAL file.
archive_mode | off | Allows archiving of WAL files using archive_command.
archive_timeout | 0 | Forces a switch to the next xlog file if a new file has not been started within N seconds.
array_nulls | on | Enable input of NULL elements in arrays.
authentication_timeout | 1min | Sets the maximum allowed time to complete client authentication.
autovacuum | on | Starts the autovacuum subprocess.
autovacuum_analyze_scale_factor | 0.1 | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.
autovacuum_analyze_threshold | 50 | Minimum number of tuple inserts, updates, or deletes prior to analyze.
autovacuum_freeze_max_age | 200000000 | Age at which to autovacuum a table to prevent transaction ID wraparound.
autovacuum_max_workers | 3 | Sets the maximum number of simultaneously running autovacuum worker processes.
autovacuum_multixact_freeze_max_age | 400000000 | Multixact age at which to autovacuum a table to prevent multixact wraparound.
autovacuum_naptime | 1min | Time to sleep between autovacuum runs.
autovacuum_vacuum_cost_delay | 20ms | Vacuum cost delay in milliseconds, for autovacuum.
autovacuum_vacuum_cost_limit | -1 | Vacuum cost amount available before napping, for autovacuum.
autovacuum_vacuum_scale_factor | 0.1 | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold | 50 | Minimum number of tuple updates or deletes prior to vacuum.
autovacuum_work_mem | -1 | Sets the maximum memory to be used by each autovacuum worker process.
backslash_quote | safe_encoding | Sets whether "\'" is allowed in string literals.
bgwriter_delay | 200ms | Background writer sleep time between rounds.
bgwriter_lru_maxpages | 100 | Background writer maximum number of LRU pages to flush per round.
bgwriter_lru_multiplier | 2 | Multiple of the average buffer usage to free per round.
block_size | 8192 | Shows the size of a disk block.
bonjour | off | Enables advertising the server via Bonjour.
bonjour_name | | Sets the Bonjour service name.
bytea_output | hex | Sets the output format for bytea.
check_function_bodies | on | Check function bodies during CREATE FUNCTION.
checkpoint_completion_target | 0.9 | Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint inter
checkpoint_timeout | 5min | Sets the maximum time between automatic WAL checkpoints.
checkpoint_warning | 30s | Enables warnings if checkpoint segments are filled more frequently than this.
client_encoding | UTF8 | Sets the client's character set encoding.
client_min_messages | notice | Sets the message levels that are sent to the client.
cluster_name | | Sets the name of the cluster, which is included in the process title.
commit_delay | 0 | Sets the delay in microseconds between transaction commit and flushing WAL to disk.
commit_siblings | 5 | Sets the minimum concurrent open transactions before performing commit_delay.
config_file | /space/postgres/postgresql.conf | Sets the server's main configuration file.
constraint_exclusion | partition | Enables the planner to use constraints to optimize queries.
cpu_index_tuple_cost | 0.005 | Sets the planner's estimate of the cost of processing each index entry during an index scan.
cpu_operator_cost | 0.0025 | Sets the planner's estimate of the cost of processing each operator or function call.
cpu_tuple_cost | 0.01 | Sets the planner's estimate of the cost of processing each tuple (row).
cursor_tuple_fraction | 0.1 | Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved.
data_checksums | on | Shows whether data checksums are turned on for this cluster.
data_directory | /space/postgres | Sets the server's data directory.
DateStyle | ISO, MDY | Sets the display format for date and time values.
db_user_namespace | off | Enables per-database user names.
deadlock_timeout | 1s | Sets the time to wait on a lock before checking for deadlock.
debug_assertions | off | Shows whether the running server has assertion checks enabled.
debug_pretty_print | on | Indents parse and plan tree displays.
debug_print_parse | off | Logs each query's parse tree.
debug_print_plan | off | Logs each query's execution plan.
debug_print_rewritten | off | Logs each query's rewritten parse tree.
default_statistics_target | 250 | Sets the default statistics target.
default_tablespace | | Sets the default tablespace to create tables and indexes in.
default_text_search_config | pg_catalog.english | Sets default text search configuration.
default_transaction_deferrable | off | Sets the default deferrable status of new transactions.
default_transaction_isolation | read committed | Sets the transaction isolation level of each new transaction.
default_transaction_read_only | off | Sets the default read-only status of new transactions.
default_with_oids | off | Create new tables with OIDs by default.
dynamic_library_path | $libdir | Sets the path for dynamically loadable modules.
dynamic_shared_memory_type | posix | Selects the dynamic shared memory implementation used.
effective_cache_size | 6GB | Sets the planner's assumption about the size of the disk cache.
effective_io_concurrency | 4 | Number of simultaneous requests that can be handled efficiently by the disk subsystem.
enable_bitmapscan | on | Enables the planner's use of bitmap-scan plans.
enable_hashagg | on | Enables the planner's use of hashed aggregation plans.
enable_hashjoin | on | Enables the planner's use of hash join plans.
enable_indexonlyscan | on | Enables the planner's use of index-only-scan plans.
enable_indexscan | on | Enables the planner's use of index-scan plans.
enable_material | on | Enables the planner's use of materialization.
enable_mergejoin | on | Enables the planner's use of merge join plans.
enable_nestloop | on | Enables the planner's use of nested-loop join plans.
enable_seqscan | on | Enables the planner's use of sequential-scan plans.
enable_sort | on | Enables the planner's use of explicit sort steps.
enable_tidscan | on | Enables the planner's use of TID scan plans.
escape_string_warning | on | Warn about backslash escapes in ordinary string literals.
event_source | PostgreSQL | Sets the application name used to identify PostgreSQL messages in the event log.
exit_on_error | off | Terminate session on any error.
external_pid_file | | Writes the postmaster PID to the specified file.
extra_float_digits | 0 | Sets the number of digits displayed for floating-point values.
from_collapse_limit | 8 | Sets the FROM-list size beyond which subqueries are not collapsed.
fsync | on | Forces synchronization of updates to disk.
full_page_writes | on | Writes full pages to WAL when first modified after a checkpoint.
geqo | on | Enables genetic query optimization.
geqo_effort | 5 | GEQO: effort is used to set the default for other GEQO parameters.
geqo_generations | 0 | GEQO: number of iterations of the algorithm.
geqo_pool_size | 0 | GEQO: number of individuals in the population.
geqo_seed | 0 | GEQO: seed for random path selection.
geqo_selection_bias | 2 | GEQO: selective pressure within the population.
geqo_threshold | 24 | Sets the threshold of FROM items beyond which GEQO is used.
gin_fuzzy_search_limit | 0 | Sets the maximum allowed result for exact search by GIN.
gin_pending_list_limit | 4MB | Sets the maximum size of the pending list for GIN index.
hba_file | /space/postgres/pg_hba.conf | Sets the server's "hba" configuration file.
hot_standby | off | Allows connections and queries during recovery.
hot_standby_feedback | off | Allows feedback from a hot standby to the primary that will avoid query conflicts.
huge_pages | try | Use of huge pages on Linux.
ident_file | /space/postgres/pg_ident.conf | Sets the server's "ident" configuration file.
ignore_checksum_failure | off | Continues processing after a checksum failure.
ignore_system_indexes | off | Disables reading from system indexes.
integer_datetimes | on | Datetimes are integer based.
IntervalStyle | postgres | Sets the display format for interval values.
join_collapse_limit | 8 | Sets the FROM-list size beyond which JOIN constructs are not flattened.
krb_caseins_users | off | Sets whether Kerberos and GSSAPI user names should be treated as case-insensitive.
krb_server_keyfile | FILE:/etc/sysconfig/pgsql/krb5.keytab | Sets the location of the Kerberos server key file.
lc_collate | en_US.UTF-8 | Shows the collation order locale.
lc_ctype | en_US.UTF-8 | Shows the character classification and case conversion locale.
lc_messages | en_US.UTF-8 | Sets the language in which messages are displayed.
lc_monetary | en_US.UTF-8 | Sets the locale for formatting monetary amounts.
lc_numeric | en_US.UTF-8 | Sets the locale for formatting numbers.
lc_time | en_US.UTF-8 | Sets the locale for formatting date and time values.
listen_addresses | * | Sets the host name or IP address(es) to listen to.
lo_compat_privileges | off | Enables backward compatibility mode for privilege checks on large objects.
local_preload_libraries | | Lists unprivileged shared libraries to preload into each backend.
lock_timeout | 0 | Sets the maximum allowed duration of any wait for a lock.
log_autovacuum_min_duration | 3s | Sets the minimum execution time above which autovacuum actions will be logged.
log_checkpoints | on | Logs each checkpoint.
log_connections | on | Logs each successful connection.
log_destination | stderr | Sets the destination for server log output.
log_directory | pg_log | Sets the destination directory for log files.
log_disconnections | on | Logs end of a session, including duration.
log_duration | off | Logs the duration of each completed SQL statement.
log_error_verbosity | default | Sets the verbosity of logged messages.
log_executor_stats | off | Writes executor performance statistics to the server log.
log_file_mode | 0600 | Sets the file permissions for log files.
log_filename | postgresql-%Y-%m-%d_%H%M%S.log | Sets the file name pattern for log files.
log_hostname | off | Logs the host name in the connection logs.
log_line_prefix | < %m > | Controls information prefixed to each log line.
log_lock_waits | off | Logs long lock waits.
log_min_duration_statement | 3min | Sets the minimum execution time above which statements will be logged.
log_min_error_statement | error | Causes all statements generating error at or above this level to be logged.
log_min_messages | warning | Sets the message levels that are logged.
log_parser_stats | off | Writes parser performance statistics to the server log.
log_planner_stats | off | Writes planner performance statistics to the server log.
log_replication_commands | off | Logs each replication command.
log_rotation_age | 1d | Automatic log file rotation will occur after N minutes.
log_rotation_size | 0 | Automatic log file rotation will occur after N kilobytes.
log_statement | none | Sets the type of statements logged.
log_statement_stats | off | Writes cumulative performance statistics to the server log.
log_temp_files | -1 | Log the use of temporary files larger than this number of kilobytes.
log_timezone | Europe/Brussels | Sets the time zone to use in log messages.
log_truncate_on_rotation | on | Truncate existing log files of same name during log rotation.
logging_collector | on | Start a subprocess to capture stderr output and/or csvlogs into log files.
maintenance_work_mem | 64MB | Sets the maximum memory to be used for maintenance operations.
max_connections | 50 | Sets the maximum number of concurrent connections.
max_files_per_process | 1000 | Sets the maximum number of simultaneously open files for each server process.
max_function_args | 100 | Shows the maximum number of function arguments.
max_identifier_length | 63 | Shows the maximum identifier length.
max_index_keys | 32 | Shows the maximum number of index keys.
max_locks_per_transaction | 64 | Sets the maximum number of locks per transaction.
max_pred_locks_per_transaction | 64 | Sets the maximum number of predicate locks per transaction.
max_prepared_transactions | 0 | Sets the maximum number of simultaneously prepared transactions.
max_replication_slots | 0 | Sets the maximum number of simultaneously defined replication slots.
max_stack_depth | 4MB | Sets the maximum stack depth, in kilobytes.
max_standby_archive_delay | 30s | Sets the maximum delay before canceling queries when a hot standby server is processing archived WAL da
ta.
max_standby_streaming_delay | 30s | Sets the maximum delay before canceling queries when a hot standby server is processing streamed WAL da
ta.
max_wal_senders | 0 | Sets the maximum number of simultaneously running WAL sender processes.
max_wal_size | 1GB | Sets the WAL size that triggers a checkpoint.
max_worker_processes | 8 | Maximum number of concurrent worker processes.
min_wal_size | 80MB | Sets the minimum size to shrink the WAL to.
operator_precedence_warning | off | Emit a warning for constructs that changed meaning since PostgreSQL 9.4.
password_encryption | on | Encrypt passwords.
pg_stat_statements.max | 5000 | Sets the maximum number of statements tracked by pg_stat_statements.
pg_stat_statements.save | on | Save pg_stat_statements statistics across server shutdowns.
pg_stat_statements.track | top | Selects which statements are tracked by pg_stat_statements.
pg_stat_statements.track_utility | on | Selects whether utility commands are tracked by pg_stat_statements.
port | 5432 | Sets the TCP port the server listens on.
post_auth_delay | 0 | Waits N seconds on connection startup after authentication.
pre_auth_delay | 0 | Waits N seconds on connection startup before authentication.
quote_all_identifiers | off | When generating SQL fragments, quote all identifiers.
random_page_cost | 4 | Sets the planner's estimate of the cost of a nonsequentially fetched disk page.
restart_after_crash | on | Reinitialize server after backend crash.
row_security | on | Enable row security.
search_path | "$user", public | Sets the schema search order for names that are not schema-qualified.
segment_size | 1GB | Shows the number of pages per disk file.
seq_page_cost | 1 | Sets the planner's estimate of the cost of a sequentially fetched disk page.
server_encoding | UTF8 | Sets the server (database) character set encoding.
server_version | 9.5.3 | Shows the server version.
server_version_num | 90503 | Shows the server version as an integer.
session_preload_libraries | | Lists shared libraries to preload into each backend.
session_replication_role | origin | Sets the session's behavior for triggers and rewrite rules.
shared_buffers | 2GB | Sets the number of shared memory buffers used by the server.
shared_preload_libraries | pg_stat_statements | Lists shared libraries to preload into server.
sql_inheritance | on | Causes subtables to be included by default in various commands.
ssl | off | Enables SSL connections.
ssl_ca_file | | Location of the SSL certificate authority file.
ssl_cert_file | server.crt | Location of the SSL server certificate file.
ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL | Sets the list of allowed SSL ciphers.
ssl_crl_file | | Location of the SSL certificate revocation list file.
ssl_ecdh_curve | prime256v1 | Sets the curve to use for ECDH.
ssl_key_file | server.key | Location of the SSL server private key file.
ssl_prefer_server_ciphers | on | Give priority to server ciphersuite order.
standard_conforming_strings | on | Causes '...' strings to treat backslashes literally.
statement_timeout | 0 | Sets the maximum allowed duration of any statement.
stats_temp_directory | pg_stat_tmp | Writes temporary statistics files to the specified directory.
superuser_reserved_connections | 3 | Sets the number of connection slots reserved for superusers.
synchronize_seqscans | on | Enable synchronized sequential scans.
synchronous_commit | off | Sets the current transaction's synchronization level.
synchronous_standby_names | | List of names of potential synchronous standbys.
syslog_facility | local0 | Sets the syslog "facility" to be used when syslog enabled.
syslog_ident | postgres | Sets the program name used to identify PostgreSQL messages in syslog.
tcp_keepalives_count | 0 | Maximum number of TCP keepalive retransmits.
tcp_keepalives_idle | 0 | Time between issuing TCP keepalives.
tcp_keepalives_interval | 0 | Time between TCP keepalive retransmits.
temp_buffers | 32MB | Sets the maximum number of temporary buffers used by each session.
temp_file_limit | -1 | Limits the total size of all temporary files used by each session.
temp_tablespaces | | Sets the tablespace(s) to use for temporary tables and sort files.
TimeZone | Europe/Brussels | Sets the time zone for displaying and interpreting time stamps.
timezone_abbreviations | Default | Selects a file of time zone abbreviations.
trace_notify | off | Generates debugging output for LISTEN and NOTIFY.
trace_recovery_messages | log | Enables logging of recovery-related debugging information.
trace_sort | off | Emit information about resource usage in sorting.
track_activities | on | Collects information about executing commands.
track_activity_query_size | 16384 | Sets the size reserved for pg_stat_activity.query, in bytes.
track_commit_timestamp | off | Collects transaction commit time.
track_counts | on | Collects statistics on database activity.
track_functions | all | Collects function-level statistics on database activity.
track_io_timing | on | Collects timing statistics for database I/O activity.
transaction_deferrable | off | Whether to defer a read-only serializable transaction until it can be executed with no possible seriali
zation failures.
transaction_isolation | read committed | Sets the current transaction's isolation level.
transaction_read_only | off | Sets the current transaction's read-only status.
transform_null_equals | off | Treats "expr=NULL" as "expr IS NULL".
unix_socket_directories | /var/run/postgresql, /tmp | Sets the directories where Unix-domain sockets will be created.
unix_socket_group | | Sets the owning group of the Unix-domain socket.
unix_socket_permissions | 0777 | Sets the access permissions of the Unix-domain socket.
update_process_title | on | Updates the process title to show the active SQL command.
vacuum_cost_delay | 0 | Vacuum cost delay in milliseconds.
vacuum_cost_limit | 200 | Vacuum cost amount available before napping.
vacuum_cost_page_dirty | 20 | Vacuum cost for a page dirtied by vacuum.
vacuum_cost_page_hit | 1 | Vacuum cost for a page found in the buffer cache.
vacuum_cost_page_miss | 10 | Vacuum cost for a page not found in the buffer cache.
vacuum_defer_cleanup_age | 0 | Number of transactions by which VACUUM and HOT cleanup should be deferred, if any.
vacuum_freeze_min_age | 50000000 | Minimum age at which VACUUM should freeze a table row.
vacuum_freeze_table_age | 150000000 | Age at which VACUUM should scan whole table to freeze tuples.
vacuum_multixact_freeze_min_age | 5000000 | Minimum age at which VACUUM should freeze a MultiXactId in a table row.
vacuum_multixact_freeze_table_age | 150000000 | Multixact age at which VACUUM should scan whole table to freeze tuples.
wal_block_size | 8192 | Shows the block size in the write ahead log.
wal_buffers | 16MB | Sets the number of disk-page buffers in shared memory for WAL.
wal_compression | on | Compresses full-page writes written in WAL file.
wal_keep_segments | 0 | Sets the number of WAL files held for standby servers.
wal_level | minimal | Set the level of information written to the WAL.
wal_log_hints | off | Writes full pages to WAL when first modified after a checkpoint, even for a non-critical modifications.
wal_receiver_status_interval | 10s | Sets the maximum interval between WAL receiver status reports to the primary.
wal_receiver_timeout | 1min | Sets the maximum wait time to receive data from the primary.
wal_retrieve_retry_interval | 5s | Sets the time to wait before retrying to retrieve WAL after a failed attempt.
wal_segment_size | 16MB | Shows the number of pages per write ahead log segment.
wal_sender_timeout | 1min | Sets the maximum time to wait for WAL replication.
wal_sync_method | fdatasync | Selects the method used for forcing WAL updates to disk.
wal_writer_delay | 200ms | WAL writer sleep time between WAL flushes.
work_mem | 64MB | Sets the maximum memory to be used for query workspaces.
xmlbinary | base64 | Sets how binary values are to be encoded in XML.
xmloption | content | Sets whether XML data in implicit parsing and serialization operations is to be considered as documents
or content fragments.
zero_damaged_pages | off | Continues processing past damaged page headers.
*/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ANSI JOINS versus , like JOINS
@ 2016-06-20 13:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Herwig Goemans <herwig.goemans@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2016-06-20 13:42 UTC (permalink / raw)
To: Herwig Goemans <herwig.goemans@gmail.com>; +Cc: pgsql-sql
Herwig Goemans <herwig.goemans@gmail.com> writes:
> Is there anyone who can help so that the mixed query has the same or
> equivalent execution plan (performance) as the ansi query ?
I think you need to raise join_collapse_limit and/or from_collapse_limit.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ANSI JOINS versus , like JOINS
@ 2016-06-20 15:35 Herwig Goemans <herwig.goemans@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Herwig Goemans @ 2016-06-20 15:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Your help is very much appreciated, I did that already short after I sent
the mail and it resolved the issue. However I do not understand why there
is a difference in behaviour between ansi join and comma like join.
Thanks
Herwig
Op 20 jun. 2016 3:42 p.m. schreef "Tom Lane" <tgl@sss.pgh.pa.us>:
> Herwig Goemans <herwig.goemans@gmail.com> writes:
> > Is there anyone who can help so that the mixed query has the same or
> > equivalent execution plan (performance) as the ansi query ?
>
> I think you need to raise join_collapse_limit and/or from_collapse_limit.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2016-06-20 15:35 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-06-20 10:06 ANSI JOINS versus , like JOINS Herwig Goemans <herwig.goemans@gmail.com>
2016-06-20 13:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-06-20 15:35 ` Herwig Goemans <herwig.goemans@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox