CREATE UNLOGGED MATERIALIZED VIEW
Hi! I am looking at parser grammar rules and it looks like the following is a valid query: CREATE UNLOGGED MATERIALIZED VIEW Any particular reason this is not documented? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
Is there something wrong with my test case?
Hi all and merry Christmas
I was under the impression that updating a table with values from (an)
other table(s) would be implemented most efficiently with a correlated
subquery a long the schema as follows, let's name it A.
update TO_BE_UPDATED U
set ({column list}) = ({correlated subquery 1})
where exists ({correlated subquery 1})
;
I set up a test case to figure out if this is the case. I compared the
execution plan of a query with above pattern with the execution plans
with the following patterns.
B
update TO_BE_UPDATED U
set COL_1 = ({correlated subquery 1}),
COL_2 = ({correlated subquery 2})
where exists ({correlated subquery 3})
;
C
update TO_BE_UPDATED U
set COL_1 = ({correlated subquery 1}),
COL_2 = ({correlated subquery 2})
where U.KEY_U in ({correlated subquery 3})
;
Explain analyze verbose showed for:
A (cost=264.72..626.97 rows=31 width=90) (actual time=1.117..1.117
rows=0 loops=1)
B (cost=264.72..974.25 rows=31 width=58) (actual time=1.508..1.508
rows=0 loops=1)
C (cost=0.42..611.19 rows=31 width=52) (actual time=2.217..2.217
rows=0 loops=1)
I am very surprised that the cost of A is (much) higher than that of C
which I suspected to be the most inefficient. I was that much fixed on
the costs that I initially ignored the actual time where my
assumptions on efficiency are reflected. Funny though is that the
subjective impression when waiting for the update queries to complete
was that C was fastest by far, followed by B and only at the end was
update A.
Now I wonder whether I do not know, how to read the explain plan
summary, I should not trust my subjective impression with respect to
time measurement or if my test case is faulty.
I carried out the test on an idle Windows 10 laptop with portable
PostgreSQL 10.4 provided by PortableApps.
You can find test case script and log at https://pastebin.com/W2HsTBwi
I would appreciate your two dimes.
Kind regards
Thiemo
--
Signal: +49 1578 7723737
Handys: +41 78 947 36 21 | +49 1578 772 37 37
Tox-Id:
B7282A9E31D944DDAD1ECC5B33C36AAE80B32D119AB7391D602C937A65E8CA0625C495C5322B
Enerprisedb compatibility
Hello, We would like to upgrade an database from postgresql installation to enterprisedb platform. Do you know if there is any guide to follow in this case? Thanks, Carlos
Re: Enerprisedb compatibility
On 12/25/18 3:53 AM, az@outlook.es wrote: Hello, We would like to upgrade an database from postgresql installation to enterprisedb platform. You are going to have to be more specific: 1) What is your current Postgres version and source? 2) What EDB platform are you referring to and its version? 3) What are your time requirements? - Do you know if there is any guide to follow in this case? Thanks, Carlos -- Adrian Klaver adrian.kla...@aklaver.com
Re: CREATE UNLOGGED MATERIALIZED VIEW
On 12/25/18 1:13 AM, Mitar wrote: Hi! I am looking at parser grammar rules and it looks like the following is a valid query: CREATE UNLOGGED MATERIALIZED VIEW Any particular reason this is not documented? My guess because of this: https://doxygen.postgresql.org/matview_8c.html Line 458-461 "/* * We can skip WAL-logging the insertions, unless PITR or streaming * replication is in use. We can skip the FSM in any case. */ " Mitar -- Adrian Klaver adrian.kla...@aklaver.com
Re: CREATE UNLOGGED MATERIALIZED VIEW
Hi! That is for transient tables (NEW and OLD tables), no? Not for the main materialized view table. Mitar On Tue, Dec 25, 2018 at 10:14 AM Adrian Klaver wrote: > > On 12/25/18 1:13 AM, Mitar wrote: > > Hi! > > > > I am looking at parser grammar rules and it looks like the following > > is a valid query: > > > > CREATE UNLOGGED MATERIALIZED VIEW > > > > Any particular reason this is not documented? > > My guess because of this: > > https://doxygen.postgresql.org/matview_8c.html > > Line 458-461 > > "/* >* We can skip WAL-logging the insertions, unless PITR or streaming >* replication is in use. We can skip the FSM in any case. > */ > " > > > > > > > Mitar > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com -- http://mitar.tnode.com/ https://twitter.com/mitar_m
Re: CREATE UNLOGGED MATERIALIZED VIEW
On 12/25/18 10:16 AM, Mitar wrote: Hi! That is for transient tables (NEW and OLD tables), no? Not for the main materialized view table. Don't know. From my POV MATERIALIZED VIEWS are transient any way you look at them. They capture a state at a point in time and then the data they depend on moves on. Given that they can be refreshed at will not sure logging/not logging really matters. Mitar On Tue, Dec 25, 2018 at 10:14 AM Adrian Klaver wrote: On 12/25/18 1:13 AM, Mitar wrote: Hi! I am looking at parser grammar rules and it looks like the following is a valid query: CREATE UNLOGGED MATERIALIZED VIEW Any particular reason this is not documented? My guess because of this: https://doxygen.postgresql.org/matview_8c.html Line 458-461 "/* * We can skip WAL-logging the insertions, unless PITR or streaming * replication is in use. We can skip the FSM in any case. */ " Mitar -- Adrian Klaver adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com
Re: CREATE UNLOGGED MATERIALIZED VIEW
Hi! On Tue, Dec 25, 2018 at 10:25 AM Adrian Klaver wrote: > Don't know. From my POV MATERIALIZED VIEWS are transient any way you > look at them. They capture a state at a point in time and then the data > they depend on moves on. Given that they can be refreshed at will not > sure logging/not logging really matters. I agree with you. But current default is RELPERSISTENCE_PERMANENT. So there is an option for RELPERSISTENCE_UNLOGGED. So, I think that we should or a) make RELPERSISTENCE_UNLOGGED default, b) document RELPERSISTENCE_UNLOGGED or c) remove it. I propose b). Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
