Hi,
Just realised that the time difference between explain analyze plan
and /timing result is due to the implicit commit.
Sorry about that.
Regards,
Nanda
On Sun, 22 Nov 2020 at 01:57, Nandakumar M wrote:
>
> Hi,
>
> I noticed something strange in our PG server. I have a
Hi,
I noticed something strange in our PG server. I have a table named
'timetable' that has only one bigint column and one row.
Once in every 5 seconds this row is updated to the current time epoch
value in milliseconds.
The update query seems to be taking considerable time (avg 50
milliseconds)
On Thu, Jan 31, 2019 at 1:00 PM Nandakumar M wrote:
> This is particularly confusing because in this thread Tom Lane says
> the following
>
Missed to link the thread..
https://postgrespro.com/list/thread-id/1813920
Regards,
Nanda
Hi,
According to https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server :
> effective_cache_size should be set to an estimate of how much memory is
> available for disk caching by the operating system and within the database
> itself, after taking into account what's used by the OS itse
=0.013..0.024 rows=10 loops=1)
Planning time: 0.505 ms
Execution time: 311.682 ms
(13 rows)
Time: 312.673 ms
Is there something I can do that will improve the query performance
much more than this?
Thanks.
Regards,
Nanda
On Mon, 9 Jul 2018, 19:53 Tom Lane, wrote:
>
> Nandakumar M w
Hi,
I am having a query that has an order by and a limit clause. The
column on which I am doing order by is indexed (default b tree index).
However the index is not being used. On tweaking the query a bit I
found that when I use left join index is not used whereas when I use
inner join the index i
Hi,
I have also faced the same problem with citext extension. It does not
use index when thereby making it almost unusable. The problem has to
do with how collation is handled from what I have read in old threads
in postgres mailing list (please refer
https://dba.stackexchange.com/questions/105244
Hi,
https://heapanalytics.com/blog/engineering/running-10-million-postgresql-indexes-in-production
>From the link shared above, it looks like what Meenatchi has done should work.
Do the conditions on the partial index and query match exactly? (
greater than / greater than equals mismatch maybe?)
Hi,
Correction in the query. I missed to add limit 10 in the outer most query..
> select * from posts where posts.author_id in (select id from users
> where id in (select friend_id from user_friend where user_id = 1) and
> last_posted_time is not null order by last_posted_time desc limit 10);
>
Hi,
I myself am new to performance tuning queries. But, from what you have
said it looks like Postgres has to go through all the posts using the
backward index scan and find out whether their author is amongst the
user's friends list.
Since the number of friends is arbitrary for any user, even if
Hi,
On Fri, Feb 2, 2018 at 9:28 PM, David G. Johnston
wrote:
> You probably can (I assume the nulls aspect of the index doesn't prevent PK
> usage), but you must add the PK to the table after creating the index and
> not let the system auto-generate the index for you.
>
> https://www.postgresql.
Hi,
On Fri, Feb 2, 2018 at 8:30 PM, Tom Lane wrote:
>
> The planner does not consider this and it doesn't really seem like
> something worth expending cycles on. If you know that there won't be
> nulls in the column, why are you insisting on specifying a nondefault
> value of NULLS FIRST/LAST in
Hi,
On 2 Feb 2018 15:06, "Laurenz Albe" wrote:
>In the above case, the optimizer does >not know that it will get the rows
>in the correct order: indexes are >sorted ASC NULLS LAST by default,
>so a backwards index scan will >produce the results NULLS FIRST,
>which is the default for ORDER BY ..
Hi,
I am using Postgres version 9.4.4 on a Mac machine. I have 2 queries that
differ only in the order by clause. One of it has 'nulls last' and the
other one does not have it. The performance difference between the two is
considerable.
The slower of the two queries is
SELECT wos.notificatio
Hi,
I tried pg_prewarm as suggested by Jeff Janes and it works - thanks a lot
Jeff. Now the query planning is fast on the first execution.
Here is the list of tables that needed to be pre warmed (or you could just
pre warm all the 'pg_%' tables. :-) ).
select pg_prewarm('pg_statistic');
select p
Hi,
On 17 Jan 2018 12:55, "POUSSEL, Guillaume"
wrote:
Are you on Windows or Linux? I’m on Windows and wondering if the issue is
the same on Linux?
I have experienced this on Mac and Linux machines.
You can try pg_prewarm, on pg_statistic table and its index. But I'd
probably just put an entr
Missed to have mailing list in to address.. forwarding now.
-- Forwarded message --
From: "Nandakumar M"
Date: 15 Jan 2018 12:16
Subject: Re: Query is slow when run for first time; subsequent execution is
fast
To: "Pavel Stehule"
Cc:
Hi,
On Fri, Jan 12, 20
Hello Jeff,
Thanks for the insights.
>Don't keep closing and reopening connections.
Even if I close a connection and open a new one and execute the same query,
the planning time is considerably less than the first time. Only when I
restart the Postgres server then I face high planning time again
Hello,
This is my first question in postgres mailing list. If there are any
mistakes, please don't mind.
I am using PostgreSQL 9.4.4 on a Mac machine executing queries on postgres
server through the psql client.
servicedesk=# select version();
version
19 matches
Mail list logo
