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.
