[GENERAL] Re: Why is the comparison between timestamp and date so much slower then between two dates

Alban Hertroys schrieb am 14.04.2016 um 21:22: now() (and current_timestamp as well) are defined to return the same value throughout the entire transaction. So the optimizer _should_ be smart enough to do the conversion only once at the beginning of the statement and then use that converted

Re: [GENERAL] Why is the comparison between timestamp and date so much slower then between two dates

> On 14 Apr 2016, at 15:12, Thomas Kellerer wrote: > > now() (and current_timestamp as well) are defined to return the > same value throughout the entire transaction. > > So the optimizer _should_ be smart enough to do the conversion only > once at the beginning of the

Re: [GENERAL] Deadlock between VACUUM and ALTER TABLE commands

Hi Alexey, Thank you for your suggestion. We had to rewrite our constraints to be DEFERRABLE INITIALLY IMMEDIATE, but after we did that we saw a nice speedup in our clear-all-tables query. Unfortunately *I'm still seeing a very slow query which is affecting our tests. *It's happening with roughly

Re: [GENERAL] pg_upgrade error regarding hstore operator

Thanks for the reply Tom. For the first query, I get the following result on both 9.1 and pg_upgrade'd 9.5. SELECT am.amname AS index_method, opf.opfname AS opfamily_name, amop.amopopr::regoperator AS opfamily_operator FROM pg_am am, pg_opfamily opf, pg_amop amop WHERE

Re: [GENERAL] Cross DB insert with returning id

You may wish to consider using the Postgres Foreign Data Wrapper to access your other database: http://www.postgresql.org/docs/9.3/static/postgres-fdw.html And then use RETURNING to get your ID, e.g.: INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets') RETURNING did;

Re: [GENERAL] Site down

On 04/14/2016 07:19 AM, Magnus Hagander wrote: On Thu, Apr 14, 2016 at 4:14 PM, Adrian Klaver > wrote: Multiple attempts from multiple locations show that the Web site is down for me at least. I can ping the address, just

Re: [GENERAL] Site down

On Thu, Apr 14, 2016 at 4:14 PM, Adrian Klaver wrote: > Multiple attempts from multiple locations show that the Web site is down > for me at least. I can ping the address, just not pull up the Web content. > Sorry about that. Botched upgrade and the alerts got lost

Re: [GENERAL] Site down

On 04/14/2016 07:14 AM, Adrian Klaver wrote: Multiple attempts from multiple locations show that the Web site is down for me at least. I can ping the address, just not pull up the Web content. Just came back up. Thanks, -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via

[GENERAL] Site down

Multiple attempts from multiple locations show that the Web site is down for me at least. I can ping the address, just not pull up the Web content. Thanks, -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

[GENERAL] Re: Why is the comparison between timestamp and date so much slower then between two dates

Tom Lane schrieb am 14.04.2016 um 15:57: >> So the optimizer _should_ be smart enough to do the conversion only >> once at the beginning of the statement and then use that converted >> value during the execution of the statement without the need >> to re-evaluate it for each row. > > It's not;

[GENERAL] Cross DB insert with returning id

Hi Team, I am new to postgresql ,i need to create one function where i have to insert data into cross database table from that table identity value i need to insert some other table other database within one function .i am able to insert the data into cross DB table using dblink_exec but i unable

Re: [GENERAL] Re: Why is the comparison between timestamp and date so much slower then between two dates

Thomas Kellerer writes: > So the optimizer _should_ be smart enough to do the conversion only > once at the beginning of the statement and then use that converted > value during the execution of the statement without the need > to re-evaluate it for each row. It's not;

[GENERAL] Re: Why is the comparison between timestamp and date so much slower then between two dates

Mike Sofen schrieb am 14.04.2016 um 14:29: > The general rule in the SQL Server world is that using a function in > a Where clause or join will eliminate usage of an index that would > have been leveraged if the function didn't exist. The reason is that > functions are non-deterministic, so the

Re: [GENERAL] Re: Why is the comparison between timestamp and date so much slower then between two dates

|-Original Message- |From: Thomas Kellerer Sent: Wednesday, April 13, 2016 11:37 PM | |Alban Hertroys schrieb am 13.04.2016 um 16:39: |>>> So my question is: why is comparing a timestamp to a date so much slower? |> |> The reason that the other way around is so much more expensive is

Re: [GENERAL] Multimaster

On 10 April 2016 at 22:48, Dorian Hoxha wrote: > Postgres-XL has no highavailibility > Postgres-XL 9.5 has had lots of additional work put in, HA being one of those areas. -- Simon Riggshttp://www.2ndQuadrant.com/

Re: [GENERAL] Multimaster

On Apr 14, 2016, at 8:41 AM, Craig Ringer wrote: > On 1 April 2016 at 19:50, Konstantin Knizhnik > wrote: > > Right now the main problem is parallel apply: we need to apply changes > concurrently to avoid unintended dependencies causing deadlocks and provide >

Re: [GENERAL] Multimaster

Il 14/04/2016 07:45, Craig Ringer ha scritto: On 2 April 2016 at 02:15, Moreno Andreo wrote:   Actually we have to improve what our replicator is doing:

[GENERAL] Re: Why is the comparison between timestamp and date so much slower then between two dates

Alban Hertroys schrieb am 13.04.2016 um 16:39: >>> So my question is: why is comparing a timestamp to a date so much slower? > > The reason that the other way around is so much more expensive is that > the database needs to do that conversion twice for every row in the > table. When

[GENERAL] Re: Why is the comparison between timestamp and date so much slower then between two dates

Tom Lane schrieb am 13.04.2016 um 15:45: >> So my question is: why is comparing a timestamp to a date so much slower? > > The date has to be up-converted to a timestamptz (not timestamp). > I think the expensive part of that is determining what timezone > applies, in particular whether DST is