Date: Mon, 6 Jun 2022 05:35:04 -0500
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
Message-ID: <20220606103504.GS29853@telsasoft.com>
References: 
 <CAArYVwgjwhsm8opP5gNt7e189=zvdw0VHSx2Uaq9X5pna+2_Nw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAArYVwgjwhsm8opP5gNt7e189=zvdw0VHSx2Uaq9X5pna+2_Nw@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20220606103504.GS29853%40telsasoft.com>
Precedence: bulk

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.