From: Justin Pryzby <pryzby@telsasoft.com>
To: Mayank Kandari <mayank.kandari@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query is taking too long i intermittent
Date: Mon, 6 Jun 2022 05:35:04 -0500
Message-ID: <20220606103504.GS29853@telsasoft.com> (raw)
In-Reply-To: <CAArYVwgjwhsm8opP5gNt7e189=zvdw0VHSx2Uaq9X5pna+2_Nw@mail.gmail.com>
References: <CAArYVwgjwhsm8opP5gNt7e189=zvdw0VHSx2Uaq9X5pna+2_Nw@mail.gmail.com>
On Mon, Jun 06, 2022 at 03:28:43PM +0530, Mayank Kandari wrote:
> <https://stackoverflow.com/posts/72515636/timeline;
Thanks for including the link*.
(*FYI, I find it to be kind of unfriendly to ask the same question in multiple
forums, simultaneously - it's like cross-posting. The goal seems to be to
demand an answer from the internet community as quickly as possible.)
> Indexes:
> "event_pkey" PRIMARY KEY, btree (event_id, pkey)
> "event_event_sec_event_usec_idx" btree (event_sec, event_usec)
> When I execute the following query it takes 1 - 2 milliseconds to execute.
> I am using libpq to connect the Postgres server in c++ code. Postgres
> server version is 12.10
> Time is provided as a parameter to function executing this query, it
> contains epoche seconds and microseconds.
Are you using the simple query protocol or the extended protocol ?
> This query is executed every 30 seconds on the same client connection
> (Which is persistent for weeks). This process runs for weeks, but some time
> same query starts taking more than 10 minutes. Once it takes 10 minutes,
> after that every execution takes > 10 minutes.
> If I restart the process it recreated connection with the server and now
> execution time again falls back to 1-2 milliseconds. This issue is
> intermittent, sometimes it triggers after a week of the running process and
> sometime after 2 - 3 weeks of the running process.
Could you get the query plan for the good vs bad executions ?
To get the "bad" plan, I suggest to enable auto-explain and set its min
duration to 10 seconds or 1 minute. The "good" plan you can get any time from
psql.
> SELECT event_id FROM event WHERE (event_sec > time.seconds) OR
> ((event_sec=time.seconds) AND (event_usec>=time.useconds) ORDER BY
> event_sec, event_usec LIMIT 1
I think it'd be better if the column was a float storing the fractional number
of seconds. Currently, it may be hard for the planner to estimate rowcounts if
the conditions are not independent. I don't know if it's related to this
problem, though.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, mayank.kandari@gmail.com
Subject: Re: Query is taking too long i intermittent
In-Reply-To: <20220606103504.GS29853@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox