Concurrent CTE
Hello! We have a lot of big CTE (~40 statements, ~1000 lines) for very dynamic OLTP content and avg response time 50-300ms. Our setup has 96 threads (Intel Xeon Gold 6128), 256 GB RAM and 12 SSD (3 tablespaces). DB size < RAM. Simplifying the problem: WITH aa as ( SELECT * FROM table1 ), bb ( SELECT * FROM table2 ), cc ( SELECT * FROM table3 ), dd ( SELECT * FROM aa,bb ), ee ( SELECT * FROM aa,bb,cc ), ff ( SELECT * FROM ee,dd ), gg ( SELECT * FROM table4 ), hh ( SELECT * FROM aa ) SELECT * FROM gg,hh,ff /* primary statement */ Execution now: time--> Thread1: aa | bb | cc | dd | ee | ff | gg | hh | primary And the question: is it possible to achieve more concurrent execution plan to reduce the response time? For example: Thread1: aa | dd | ff | primary Thread2: bb | ee | gg Thread3: cc | -- | hh Table1, table2 and table3 are located on separate tablespaces and are independent. Partial results (aa,bb,cc,dd,ee) are quite big and slow (full text search, arrays, custom collations, function scans...). We consider resigning from the CTE and rewrite to RX Java but we are afraid of downloading partial results and sending it back with WHERE IN(...). Thanks! Artur Formella
Re: Concatenate of values in hierarchical data
> On 2 Apr 2018, at 19:23, Mr. Baseball 34 wrote:
>
> I have the data below, returned from a PostgreSQL table using this SQL:
>
> SELECT ila.treelevel,
>ila.app,
>ila.lrflag,
>ila.ic,
>ila.price,
>ila.treelevel-1 as parent,
>ila.seq
> FROM indexlistapp ila
> WHERE ila.indexlistid IN (SELECT il.indexlistid
> FROM indexlist il
> WHERE il.model = '$model'
> AND ('$year' BETWEEN il.beginyear AND
> il.endyear)
> AND il.type = '$part')
> ORDER BY ila.seq;
>
> Data Returned
>
> level app lrflag ic price parent seq
> indexlistid
>
> -
> 1, 'Front', null, null, null, 0, 27,
> 439755
> 2, 'V-Series' null, null, null, 1, 28,
> 439755
> 3, 'opt J56', null, null, null, 2, 29,
> 439755
> 4, 'R.','R','536-01132AR','693.00', 3, 30,
> 439755
> 4, 'L.','L','536-01133AL','693.00', 3, 31,
> 439755
> 3, 'opt J63', null, null, null, 2, 32,
> 439755
> 4, 'R.','R','536-01130R', null, 3, 33,
> 439755
> 4, 'L.','L','536-01131L', null, 3, 34,
> 439755
> 2, 'exc. V-Series', null, null, null, 1, 35,
> 439755
> 3, 'opt JE5', null, null, null, 2, 36,
> 439755
> 4, 'AWD', null, null, null, 3, 37,
> 439755
> 5, 'R.',null, '536-01142', null, 4, 38,
> 439755
> 5, 'L.',null, '536-01143', null, 4, 39,
> 439755
> 4, 'RWD', null, null, null, 3, 40,
> 439755
> 5, 'R.',null, '536-01143', null, 4, 41,
> 439755
> 5, 'L.',null, '536-01142', null, 4, 42,
> 439755
> 3, 'opt J55', null, null, null, 2, 43,
> 439755
> 4, 'AWD', null, null, null, 3, 44,
> 439755
> 5, 'R.',null, '536-01036', null, 4, 45,
> 439755
> 5, 'L.',null, '536-01037', null, 4, 46,
> 439755
> 4, 'RWD', null, null, null, 3, 47,
> 439755
> 5, 'R.',null, '536-01037', null, 4, 48,
> 439755
> 5, 'L.',null, '536-01036', null, 4, 49,
> 439755
> 1, 'Rear', null, null, null, 0, 260,
> 439765
> 2, 'Base', null, null, null, 1, 261,
> 439765
> 3, 'opt JE5', null, null, null, 2, 262,
> 439765
> 4, 'R.','R','536-01038R', null, 3, 263,
> 439765
> 4, 'L.','L','536-01039L', null, 3, 264,
> 439765
> 3, 'opt J55', null, null, null, 2, 265,
> 439765
> 4, 'R.','R','536-01042R', null, 3, 266,
> 439765
> 4, 'L.','L','536-01043L', null, 3, 267,
> 439765
> 2, 'V-Series', null, null, null, 1, 268,
> 439765
> 3, 'R.','R','536-01134AR', '403.00', 2, 269,
> 439765
> 3, 'L.','L','536-01135AL', '466.00', 2, 270,
> 439765
>
> matching data from indexlist
>
> model type beginyear endyear indexlistid
> -
> 'CTS', '536', 2009, 2010,439755
> 'CTS', '536', 2009, 2010,439765
>
> There are primary keys on indexlist (on indexlistid) and indexlistapp (on
> indexlistid) but there is no foreign key pointing to the other table. The
> indexlistid in indexlist
> points directly to the indexlistid in indexlistapp. The parent column is
> simply calculated from the treelevel. The tree is built entirely from the seq
> and treelevel.
>
> I need the data to be returned in this format:
>
> app price ic
> ---
> 'Front-V-Series-opt J56-R.', '$693','536-01132AR'
> 'Front-V-Series-opt J56-L.', '$693','536-01132AL'
> 'Front-V-Series-opt J63-R.', null, '536-01130R'
> 'Front-V-Series-opt J63-L.', null, '536-01131L'
> 'Front-exc. V-Series-opt JE5-AWD-R.', null, '536-01142'
> 'Front-exc. V-Series-opt JE5-AWD-L.', null, '536-01143'
> '
PgUpgrade bumped my XIDs by ~50M?
We have a large >20TB system just pg_upgraded from 9.5 to 9.6 as per the
versions shown below.
The system does <5M transactions/day based on sum(commit + abort) from
pg_stat_database.
Autovac is running all possible threads now and upon investigating I see
that thousands of tables are now above the freeze threshold. Same
tables all appear ~50M xids older than they did yesterday and the
upgrade was less than 24 hours ago.
I have a "safety" snap made of the system before upgrade that can be
used for inspection.
Any ideas why the age jump?
select age(l.relfrozenxid), l.oid::regclass::text as relation, l.relkind,
l.relpages, r.oid::regclass::text as "toast for"
from pg_class l
left join pg_class r on l.oid = r.reltoastrelid
where l.relkind in ('r', 't')
order by age desc, relation
limit :limit
ii postgresql-9.5 9.5.12-1.pgdg16.04+1
amd64object-relational SQL database, version 9.5 server
ii postgresql-9.6 9.6.8-1.pgdg16.04+1
amd64object-relational SQL database, version 9.6 server
Thanks
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800
Re: PgUpgrade bumped my XIDs by ~50M?
On Wed, Apr 4, 2018 at 05:29:46PM -0500, Jerry Sievers wrote: > We have a large >20TB system just pg_upgraded from 9.5 to 9.6 as per the > versions shown below. > > The system does <5M transactions/day based on sum(commit + abort) from > pg_stat_database. > > Autovac is running all possible threads now and upon investigating I see > that thousands of tables are now above the freeze threshold. Same > tables all appear ~50M xids older than they did yesterday and the > upgrade was less than 24 hours ago. > > I have a "safety" snap made of the system before upgrade that can be > used for inspection. > > Any ideas why the age jump? Uh, you can read how pg_upgrade handles frozen xids in pg_upgrade.c: https://doxygen.postgresql.org/pg__upgrade_8c_source.html#l00543 I am not sure what would have caused such a jump. pg_upgrade brings over the frozen values for each table, and sets the server's frozen counter to match the old one. If you run 'pg_dump --binary-upgrade' you will see the frozen xids being transfered: -- For binary upgrade, set heap's relfrozenxid and relminmxid UPDATE pg_catalog.pg_class SET relfrozenxid = '558', relminmxid = '1' WHERE oid = 'public.test'::pg_catalog.regclass; Is it possible that pg_upgrade used 50M xids while upgrading? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
SQL statement in an error report for deferred constraint violation.
Hi, While PQresultErrorField() from libpq allows to get context in which an error occurred for immediate constraints, and thus an SQL statement which caused the constraint violation, I cannot see any way to find out which SQL statement caused an error in case of deferred constraints, in particular deferred foreign key constraints. Is there any way to check which SQL statement or at least which row violated a constraint when it's deferred? If not does anyone know why there is such restriction? Konrad signature.asc Description: OpenPGP digital signature
Re: PgUpgrade bumped my XIDs by ~50M?
Bruce Momjian writes: > On Wed, Apr 4, 2018 at 05:29:46PM -0500, Jerry Sievers wrote: > >> We have a large >20TB system just pg_upgraded from 9.5 to 9.6 as per the >> versions shown below. >> >> The system does <5M transactions/day based on sum(commit + abort) from >> pg_stat_database. >> >> Autovac is running all possible threads now and upon investigating I see >> that thousands of tables are now above the freeze threshold. Same >> tables all appear ~50M xids older than they did yesterday and the >> upgrade was less than 24 hours ago. >> >> I have a "safety" snap made of the system before upgrade that can be >> used for inspection. >> >> Any ideas why the age jump? > > Uh, you can read how pg_upgrade handles frozen xids in pg_upgrade.c: > > https://doxygen.postgresql.org/pg__upgrade_8c_source.html#l00543 > > I am not sure what would have caused such a jump. pg_upgrade brings > over the frozen values for each table, and sets the server's frozen > counter to match the old one. > > If you run 'pg_dump --binary-upgrade' you will see the frozen xids being > transfered: > > -- For binary upgrade, set heap's relfrozenxid and relminmxid > UPDATE pg_catalog.pg_class > SET relfrozenxid = '558', relminmxid = '1' > WHERE oid = 'public.test'::pg_catalog.regclass; > > Is it possible that pg_upgrade used 50M xids while upgrading? Hi Bruce. Don't think so, as I did just snap the safety snap and ran another upgrade on that. And I also compared txid_current for the upgraded snap and our running production instance. The freshly upgraded snap is ~50M txids behind the prod instance. If this is a not too uncommon case of users running amok, then this time in particular they really went off the charts :-) Will investigate... FYI, this is the same system that a few weeks ago issued complaints during vacuum of an XID younger than relfrozenxid, which a system restart did mysteriously resolve. I hope we're going to be OK here. Thx -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800
Extension make installcheck: include update/insert feedback?
Hello,
I have a custom extension that uses the usual REGRESS Makefile variable
to indicate files in {sql,expected} that should be used when you say
`make installcheck`.
I've noticed that if my test code does an INSERT or DELETE, the usual
`INSERT 0 1` and `UPDATE 2` messages don't appear in the *.out files,
even though those otherwise mirror psql. I thought maybe there was some
psql switch that turns those on/off, but I couldn't find one. I'd like
to include those messages in my expected/*.out files though, so that my
tests verify I'm adding/updating as many rows as I intend. Is there any
way to do that?
Thanks,
--
Paul ~{:-)
p...@illuminatedcomputing.com
Re: PgUpgrade bumped my XIDs by ~50M?
On Wed, Apr 4, 2018 at 07:13:36PM -0500, Jerry Sievers wrote: > Bruce Momjian writes: > > Is it possible that pg_upgrade used 50M xids while upgrading? > > Hi Bruce. > > Don't think so, as I did just snap the safety snap and ran another > upgrade on that. > > And I also compared txid_current for the upgraded snap and our running > production instance. > > The freshly upgraded snap is ~50M txids behind the prod instance. Are the objects 50M behind or is txid_current 50M different? Higher or lower? > > If this is a not too uncommon case of users running amok, then this time > in particular they really went off the charts :-) I have never heard of this problem. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Re: LDAP Bind Password
On 4/3/18 16:12, Kumar, Virendra wrote: > Is anybody aware of how to encrypt bind password for ldap authentication > in pg_hba.conf. Anonymous bind is disabled in our organization so we > have to use bind ID and password but to keep them as plaintext in > pg_hba.conf defeat security purposes. We want to either encrypt it or > authenticate without binding. Any insights into this is appreciated. You can use the "simple bind" method that is described in the documentation. That one doesn't involve a second bind step. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Extension make installcheck: include update/insert feedback?
Paul Jungwirth writes: > I've noticed that if my test code does an INSERT or DELETE, the usual > `INSERT 0 1` and `UPDATE 2` messages don't appear in the *.out files, > even though those otherwise mirror psql. I thought maybe there was some > psql switch that turns those on/off, but I couldn't find one. That's because pg_regress launches psql with the -q option (as well as -a). I think you might be able to override that within a particular test script by fooling with QUIET, or whichever psql variable it is that that switch sets. regards, tom lane
Re: PgUpgrade bumped my XIDs by ~50M?
Bruce Momjian writes: > On Wed, Apr 4, 2018 at 07:13:36PM -0500, Jerry Sievers wrote: > >> Bruce Momjian writes: >> > Is it possible that pg_upgrade used 50M xids while upgrading? >> >> Hi Bruce. >> >> Don't think so, as I did just snap the safety snap and ran another >> upgrade on that. >> >> And I also compared txid_current for the upgraded snap and our running >> production instance. >> >> The freshly upgraded snap is ~50M txids behind the prod instance. > > Are the objects 50M behind or is txid_current 50M different? Higher or > lower? txid_current is another 12M higher then a few hours ago. Still waiting to hear from my reporting team if they changed anything. This thing is running PgLogical and has a few of our event triggers as well. But nothing in this regard changed with the upgrade. What if some very frequent but trivial statements that did not get assigned a real TXID in 9.5 on this configuration now are being treated differently? What's puzzling too is that when I run my TPS monitor script, it's clicking along at what looks typical, presently would only amount to 700k transactions/day but we're off-peak. Thx > > >> >> If this is a not too uncommon case of users running amok, then this time >> in particular they really went off the charts :-) > > I have never heard of this problem. -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800
Re: Concurrent CTE
On Wed, Apr 4, 2018 at 3:20 AM Artur Formella wrote: > Hello! > We have a lot of big CTE (~40 statements, ~1000 lines) for very dynamic > OLTP content and avg response time 50-300ms. Our setup has 96 threads > (Intel Xeon Gold 6128), 256 GB RAM and 12 SSD (3 tablespaces). DB size < > RAM. > Simplifying the problem: > > WITH aa as ( >SELECT * FROM table1 > ), bb ( >SELECT * FROM table2 > ), cc ( >SELECT * FROM table3 > ), dd ( >SELECT * FROM aa,bb > ), ee ( >SELECT * FROM aa,bb,cc > ), ff ( >SELECT * FROM ee,dd > ), gg ( >SELECT * FROM table4 > ), hh ( >SELECT * FROM aa > ) > SELECT * FROM gg,hh,ff /* primary statement */ > > Execution now: > time--> > Thread1: aa | bb | cc | dd | ee | ff | gg | hh | primary > > And the question: is it possible to achieve more concurrent execution > plan to reduce the response time? For example: > Thread1: aa | dd | ff | primary > Thread2: bb | ee | gg > Thread3: cc | -- | hh > > Table1, table2 and table3 are located on separate tablespaces and are > independent. > Partial results (aa,bb,cc,dd,ee) are quite big and slow (full text > search, arrays, custom collations, function scans...). > > We consider resigning from the CTE and rewrite to RX Java but we are > afraid of downloading partial results and sending it back with WHERE > IN(...). > > Thanks! > > Artur Formella It is very difficult from your example to tell just what kind of data you are querying and why you are doing it this way. I will give it a try. If you are filtering any of this data later you are fencing off that optimization. Also in your example it makes no sense to have cte aa when you could just cross join table1 directly in all your other ctes (and bb and cc for the same reason). Also in my experience, you are not going to have a great query plan with that many CTEs. Also are you using functions or prepared statements or are you paying the price of planning this query every time? It is hard to tell but your example leads me to question if there are some serious issues in your db design. Where are your joins and where are you leveraging indexes? Also it is very easy to misuse use a raise and function scans to even make performance worse. Thanks, Jeremy >
Re: Concurrent CTE
On Tuesday, April 3, 2018, Artur Formella wrote: > > And the question: is it possible to achieve more concurrent execution plan > to reduce the response time? For example: > Thread1: aa | dd | ff | primary > Thread2: bb | ee | gg > Thread3: cc | -- | hh > If and how depends greatly on your version. https://www.postgresql.org/docs/10/static/parallel-query.html David J.
Re: Concurrent CTE
On Wed, Apr 4, 2018 at 8:01 AM, Artur Formella wrote: > Execution now: > time--> > Thread1: aa | bb | cc | dd | ee | ff | gg | hh | primary > > And the question: is it possible to achieve more concurrent execution plan > to reduce the response time? For example: > Thread1: aa | dd | ff | primary > Thread2: bb | ee | gg > Thread3: cc | -- | hh Parallel query can't be used for CTE queries currently. Other good things also don't happen when you use CTEs -- it's an "optimiser fence" (though there is discussion of changing that eventually). Maybe try rewriting your query as: SELECT ... FROM (SELECT ...) AS aa, (SELECT ...) AS bb, ... Note that in the form of parallelism supported in PostgreSQL 10, every process (we use processes instead of threads) runs the same execution plan at the same time, but gives each worker only a part of the problem using disk block granularity, so it looks more like this: Process1: fragments of aa | fragments of bb | ... Process2: fragments of aa | fragments of bb | ... PostgreSQL 11 (not yet released) will introduce an exception that looks more like what you showed: the Parallel Append operator (for unions and scans of partitions) can give each worker a different part of the plan approximately as you showed, but IIUC that's used as a fallback strategy when it can't use block granularity (because of technical restrictions). The problem with sub-plan granularity is that the various sub-plans can finish at different times leaving some CPU cores with nothing to do while others are still working, whereas block granularity keeps everyone busy until the work is done and should finish faster. -- Thomas Munro http://www.enterprisedb.com
Re: Concurrent CTE
On Wed, Apr 4, 2018 at 10:12 PM, Thomas Munro wrote: > Parallel query can't be used for CTE queries currently. A pointer to the location in the docs covering this limitation would be appreciated. It isn't covered here: https://www.postgresql.org/docs/10/static/when-can-parallel-query-be-used.html David J.
Re: Concurrent CTE
On Thu, Apr 5, 2018 at 5:16 PM, David G. Johnston wrote: > On Wed, Apr 4, 2018 at 10:12 PM, Thomas Munro > wrote: >> >> Parallel query can't be used for CTE queries currently. > > A pointer to the location in the docs covering this limitation would be > appreciated. It isn't covered here: > > https://www.postgresql.org/docs/10/static/when-can-parallel-query-be-used.html https://www.postgresql.org/docs/10/static/parallel-safety.html -- Thomas Munro http://www.enterprisedb.com
AW: Concurrent CTE
Did you look at this approach using dblink already? https://gist.github.com/mjgleaso/8031067 In your situation, you will have to modify the example but it may give an idea where to start. Klaus > -Ursprüngliche Nachricht- > Von: Artur Formella > Gesendet: Dienstag, 3. April 2018 22:01 > An: pgsql-general@lists.postgresql.org > Betreff: Concurrent CTE > > Hello! > We have a lot of big CTE (~40 statements, ~1000 lines) for very dynamic OLTP > content and avg response time 50-300ms. Our setup has 96 threads (Intel > Xeon Gold 6128), 256 GB RAM and 12 SSD (3 tablespaces). DB size < RAM. > Simplifying the problem: > > WITH aa as ( >SELECT * FROM table1 > ), bb ( >SELECT * FROM table2 > ), cc ( >SELECT * FROM table3 > ), dd ( >SELECT * FROM aa,bb > ), ee ( >SELECT * FROM aa,bb,cc > ), ff ( >SELECT * FROM ee,dd > ), gg ( >SELECT * FROM table4 > ), hh ( >SELECT * FROM aa > ) > SELECT * FROM gg,hh,ff /* primary statement */ > > Execution now: > time--> > Thread1: aa | bb | cc | dd | ee | ff | gg | hh | primary > > And the question: is it possible to achieve more concurrent execution plan to > reduce the response time? For example: > Thread1: aa | dd | ff | primary > Thread2: bb | ee | gg > Thread3: cc | -- | hh > > Table1, table2 and table3 are located on separate tablespaces and are > independent. > Partial results (aa,bb,cc,dd,ee) are quite big and slow (full text search, > arrays, > custom collations, function scans...). > > We consider resigning from the CTE and rewrite to RX Java but we are afraid > of downloading partial results and sending it back with WHERE IN(...). > > Thanks! > > Artur Formella > >
