On 3/29/17, Vitaly Burovoy <vitaly.buro...@gmail.com> wrote:
> On 3/29/17, Andrus <kobrule...@hot.ee> wrote:
>>
>> select current_time at time zone 'GMT-2'
>>
>> returns
>>
>> "11:54:40.22045+02"
>>
>> but correct
ing time was changed by one hour a week ago.
> Maybe postgres didnt recognized it.
>
> Posted also in
>
> http://stackoverflow.com/questions/43090328/how-to-return-correct-local-time-in-postgres
>
> Andrus.
>
Try
SELECT now();
or
SELECT current_timestamp;
--
Best regards,
Vi
e many other factors influence to PG's decision including
random_page_cost and seq_page_cost for tablespaces; fillfactor for
indexes and tables and many more.
You have sent neither table DDL nor EXPLAIN ANALYZE result.
If a query runs fast enough, I would not pay attention to used access method.
Scan. That's why it can
ignore existing indexes.
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
n to use an ordinary type column(s) with range type
column(s).
P.S.: Postgres can not to use indexes even if they are right because
according to a statistics SeqScan will take similar access time.
[1]https://www.postgresql.org/docs/9.6/static/rangetypes.html#RANGETYPES-DEFINING
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 1/4/17, Gwork <n...@riseup.net> wrote:
> On 1/5/17 2:51 AM, Vitaly Burovoy wrote:
>> On 1/4/17, Gwork <n...@riseup.net> wrote:
>>> On 1/5/17 2:22 AM, Vitaly Burovoy wrote:
>>>> On 1/4/17, Vitaly Burovoy <vitaly.buro...@gmail.com> wrote
On 1/4/17, Gwork <n...@riseup.net> wrote:
> On 1/5/17 2:22 AM, Vitaly Burovoy wrote:
>> On 1/4/17, Vitaly Burovoy <vitaly.buro...@gmail.com> wrote:
>>> On 1/4/17, Gwork <n...@riseup.net> wrote:
>>>> Version: Postgresql 9.5
>>>> OS: Deb
transaction in the same command.
I think it is an obvious bug because there is no "concurrent update".
There is no update at all.
ON CONFLICT handling just does not cover all possible ways which can happen.
Normally (without "ON CONFLICT" clause) INSERT raises "duplicate key
value viola
FOR UPDATE LIMIT 1
)t WHERE s._pk_column_=t._pk_column_;
> Unfortunately I can't test on product servers, so Im looking for some
> advice or some one to point me the right direction how I can alter table
> today without clients to notice their query is locked and need to wait.
[1]
ds.
Creating a new dictionary for compressing purposes supposes updating
rows of the original table to replace entries to references to a
dictionary.
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
expression*
>
To update rows of one table by rows from another table you should use
UPDATE ... SET ... FROM ... WHERE ...
clause described in the docs[1] (see example around the sentence "A
similar result could be accomplished with a join:" and note below).
[1] https://www.postgresq
..., ... OVER()... FROM ins_table_1
) ins ON (...)
Note than CTEs not have indexes and a join process is not fast (for
bigger number of rows).
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> On Sep 23, 2016 5:12 PM, "Vitaly Burovoy" <vitaly.buro...@gmail.com> wrote:
> On 9/23/16, Deven Phillips <deven.phill...@gmail.com> wrote:
>> On Fri, Sep 23, 2016 at 10:14 AM, Deven Phillips
>> <deven.phill...@gmail.com> wrote:
>>> Is t
it can be like:
SELECT
jsonb_set(
CASE
WHEN DATA ? 'boo'
THEN DATA
ELSE jsonb_set(DATA, array['boo'], '{}')
END,
'{boo,baz}'::text[],
'"newvalue"'
)
FROM (VALUES('{"foo": "bar"}'::jsonb)) AS t(data)
[1] https://www.postgresql.org/docs/devel/static/functions-json.html
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
:
>
>
> select modified_date from clients ORDER BY modified_date ASC
>
>
> modified_date
>> ---
>> 2015-07-11 17:23:40
it is 2015 year, more than 1 year ago
vv because expression is rewritten as "modified_date <= 2016-09-13
0
On 7/29/16, Keith Fiske <ke...@omniti.com> wrote:
> On Fri, Jul 29, 2016 at 11:49 AM, Vitaly Burovoy <vitaly.buro...@gmail.com>
> wrote:
>
>> On 7/29/16, Keith Fiske <ke...@omniti.com> wrote:
>> > On Fri, Jul 29, 2016 at 12:53 AM, Vitaly Burovoy
On 7/29/16, Keith Fiske <ke...@omniti.com> wrote:
> On Fri, Jul 29, 2016 at 12:53 AM, Vitaly Burovoy <vitaly.buro...@gmail.com>
> wrote:
>
>> On 7/28/16, Keith Fiske <ke...@omniti.com> wrote:
>> > Working on trying to get a C version of the maintenance
ime).
Please, answer questions:
1. How many rows SPI_execute returns (value of "ret" variable)?
2. Is last_partition_timestamp != NULL? Where it points to?
3. Try to check SPI_result just after SPI_getbinval. Has it error code?
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 4/13/16, Vitaly Burovoy <vitaly.buro...@gmail.com> wrote:
> On 4/13/16, sighup <r...@sighup.eu> wrote:
>> Hi, please excuse either my stupidity or naivety regarding this but I'm
>> a bit confused. Give the following basic table structure :
>>
>&g
ve answer, or maybe that should read
> 'one I can understand'.
>
> I'm using PostgreSQL 9.5 on Linux with pgAdmin III on Mac.
>
> --
> Bill
[1] http://www.postgresql.org/docs/9.5/static/functions-json.html
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 3/2/16, drum.lu...@gmail.com <drum.lu...@gmail.com> wrote:
> On 3 March 2016 at 10:33, Vitaly Burovoy <vitaly.buro...@gmail.com> wrote:
>> On 3/2/16, drum.lu...@gmail.com <drum.lu...@gmail.com> wrote:
>> > Hi all...
>> >
>> > I'm working
is 2.8sec! and index scan in ix_notes_jobid_per_type
500rows(loops) * 9.878ms!!! = 4.939sec.
Why does it take so long time?
For example, index scan in ja_customers_pkey is only 0.781 per row...
10 times faster!
What definition of the ix_notes_jobid_per_type? Is it bloated?
> Explain analyze link:
On 3/2/16, Alexander Farber <alexander.far...@gmail.com> wrote:
> On Wed, Mar 2, 2016 at 11:09 AM, Vitaly Burovoy <vitaly.buro...@gmail.com>
> wrote:
>
>> On 3/2/16, Alexander Farber <alexander.far...@gmail.com> wrote:
>> >
>>
t; Thank you
> Alex
>
[1]https://en.wikipedia.org/wiki/Null_(SQL)#Comparisons_with_NULL_and_the_three-valued_logic_.283VL.29
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
config files are copied
wrongly).
>
> --
> john r pierce, recycling bits in santa cruz
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
er the new index is
used in an EXPLAIN of any query that use it or not. In the first case
do COMMIT, in the second case just do ROLLBACK and leave old index for
using.
> Thanks!
> Victor
[1]http://www.postgresql.org/docs/devel/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING
[2]http://www.
lck
WHERE
t.id=lck.id
>
> Thank you,
> Nikolai
[1]http://www.postgresql.org/docs/current/static/queries-with.html
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
'America/New_York') -- "<=" or just "<"?
ORDER BY
...
> http://www.postgresql.org/docs/9.5/interactive/functions-formatting.html
> "Ordinary text is allowed in to_char templates and will be output literally.
> You can put a substring in double quotes to
to_timestamp and to_date exist to handle input formats
> that cannot be converted by simple casting.
> ERROR: invalid value ":0" for "MI"
> DETAIL: Value must be an integer.
> Time: 1.016 ms
>
> Could anyone suggest what it is that I might be doing wrong here?
&
9.9070...@posteo.de
[2]http://www.postgresql.org/message-id/cakoswnkrb0kfwhcw9cvocrmks8huzrpvflr0kkcjuyn6juk...@mail.gmail.com
[3]http://www.postgresql.org/message-id/CAKOSWN=wX9Myw4q9mpiqESyizU4tWrGivgbw+3Ef=5q60vi...@mail.gmail.com
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (
ips out the
>> id and sends it id in the next query again. So the server can compute
>> the result set without external dependencies as fast as possible.
>
> Sounds like what you're really after is a stored procedure, isn't it?
Unfortunately, his case is different, because he
On 2/9/16, Johannes <jo...@posteo.de> wrote:
> Am 09.02.2016 um 00:56 schrieb Vitaly Burovoy:
>> On 2/8/16, Johannes <jo...@posteo.de> wrote:
>>> Am 08.02.2016 um 21:50 schrieb Vitaly Burovoy:
>>>> On 2/8/16, Johannes <jo...@posteo.de> wrote:
>&g
s one row: "(id, col1,
col2)", and the second one returns rows "(val1), (val2), (val3), ..."
(values of the t1.col1).
If you use joining, you get rows:
(id, col1, col2, val1)
(id, col1, col2, val2)
(id, col1, col2, val3)
...
where values of the first three columns are
On 2/8/16, Johannes <jo...@posteo.de> wrote:
>
> Am 08.02.2016 um 20:32 schrieb Vitaly Burovoy:
>> On 2/8/16, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
>>> Based on rough guess of the above, without seeing actual table schemas:
>>>
>>> sel
On 2/8/16, Johannes <jo...@posteo.de> wrote:
> Am 08.02.2016 um 21:17 schrieb Vitaly Burovoy:
>> On 2/8/16, Johannes <jo...@posteo.de> wrote:
>>> Am 08.02.2016 um 20:15 schrieb David G. Johnston:
>>>> On Mon, Feb 8, 2016 at 12:05 PM, Johan
t; t1.t0_id where id = (select max(id) from t0 where col1 = value1 and col2
> = value2 and ...);
I don't think it is a good solution because it leads to copying
columns from the t0 which is wasting net traffic and increasing
complexity at the client side. Moreover it works iff t0 returns only
on
returns multiple rows.
>
> begin;
> select id, col1, col2, ... from t0 where id = (select max(id) from t0
> where col1 = value1 and col2 = value2 and ...);
> select col1 from t1 where t0_id = (select max(id) from t0 where col1 =
> value1 and col2 = value2 and ...);
> commit;
>
at client are you using to execute these statements?
>
> JDBC. I execute both statements at once and iterate through the resultsets.
>
> Johannes
Hmm. Could you clarify why you don't want to pass id from the first
query to the second one:
select col1 from t1 where t0_id = value_id_from_the_first_query
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
t0
WHERE col1 = value1 and col2 = value2 and ...;
SELECT id, col1, col2, ... FROM t0 INNER NATURAL JOIN temptable;
SELECT col1 FROM t1 INNER JOIN temptable ON (t0_id = temptable.id);
COMMIT;
[1]http://www.postgresql.org/docs/9.5/static/sql-createtable.html
--
Best regards,
Vitaly Buro
On 2/8/16, Johannes <jo...@posteo.de> wrote:
> Am 08.02.2016 um 21:50 schrieb Vitaly Burovoy:
>> On 2/8/16, Johannes <jo...@posteo.de> wrote:
>>> Am 08.02.2016 um 21:17 schrieb Vitaly Burovoy:
>>>> Hmm. Could you clarify why you don't want to pass id f
have notified the
> Joomla team with this suggestion. That is as much as I can do to speed
> up PostgreSQL uptake in Joomla for now.
>
> Thanks for providing postgreSQL to us.
You are welcome.
>
> Best wishes,
> Dominic
--
Best regards,
Vitaly Burovoy
--
Sent via pgsq
ur answers even if he haven't subscribed to the
mailing list.
[1]http://www.postgresql.org/docs/9.2/static/sql-altertable.html
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
t;
> Hey guys..
> How could I create a timestampandtz index?
> CREATE TABLE gorfs.inode_segments
> (
> <>
> )
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
quot;gender" not necessarily connected
to a gender of real user who is reading the doc and in most cases such
pronouns are "masculine", that's why we don't concern to replace "he"
to "he/she" or somewhat else.
--
Best regards,
Vitaly Burovoy
--
Sent vi
INTERVAL)". You have to change the condition the way where one part of
a condition at an optimization part can be simplified to a constant
and the other part of the condition represents a column of an existent
index (as it was written in my first answer).
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
inodes" "t" (cost=0.00..1411147.24 rows=13416537 width=29)
> (actual time=94987.224..94987.224 rows=0 loops=1)
> Filter: ("timezone"('UTC'::"text", "transaction_timestamp"()) >
> (("st_ctime")::timestamp without time zone + '2 years'
On 1/10/16, Vitaly Burovoy <vitaly.buro...@gmail.com> wrote:
> On 1/10/16, Saulo Merlo <smerl...@outlook.com> wrote:
>> I've got a slow query.. I'd like to make it faster.. Make add an index?
>> Query:
>> SELECT
>> <>
>> FROM gorfs.
//www.postgresql.org/docs/9.2/static/sql-createindex.html
> I've rewriten the query as well. Thank you for that!
>
> Thank you
> Lucas
>> Date: Sun, 10 Jan 2016 21:23:01 -0800
>> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
>> From: vitaly.buro...@gmail.com
>&
www.postgresql.org/docs/9.2/static/sql-createindex.html
>>
>> > I've rewriten the query as well. Thank you for that!
>> >
>> > Thank you
>> > Lucas
>>
>> >> Date: Sun, 10 Jan 2016 21:23:01 -0800
>> >> Subject: Re: [GENERAL]
;> >> >> (f.last_changed
>> >> >> >> +
>> >> >> >> '24
>> >> >> >> months' :: INTERVAL)) LIMIT 100;
>> >> >> >
>> >> >> >> <>
>> >> >> >> "Tot
to filter or group rows after the window
calculations are performed, you can use a sub-select."
Unfortunately it is impossible to give an access to window function to
rows not selected by a current query.
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
51 matches
Mail list logo
