Re: Query is taking too long i intermittent

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.