From: Tom Lane <tgl@sss.pgh.pa.us>
To: Justin Pryzby <pryzby@telsasoft.com>
cc: Mayank Kandari <mayank.kandari@gmail.com>,
        pgsql-performance@postgresql.org
Subject: Re: Query is taking too long i intermittent
In-reply-to: <20220606103504.GS29853@telsasoft.com>
References: 
 <CAArYVwgjwhsm8opP5gNt7e189=zvdw0VHSx2Uaq9X5pna+2_Nw@mail.gmail.com>
 <20220606103504.GS29853@telsasoft.com>
Comments: In-reply-to Justin Pryzby <pryzby@telsasoft.com>
	message dated "Mon, 06 Jun 2022 05:35:04 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <894423.1654524702.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 06 Jun 2022 10:11:42 -0400
Message-ID: <894424.1654524702@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/894424.1654524702%40sss.pgh.pa.us>
Precedence: bulk

Justin Pryzby <pryzby@telsasoft.com> writes:
> On Mon, Jun 06, 2022 at 03:28:43PM +0530, Mayank Kandari wrote:
>> SELECT event_id FROM event WHERE (event_sec > time.seconds) OR
>> ((event_sec=3Dtime.seconds) AND (event_usec>=3Dtime.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 rowco=
unts if
> the conditions are not independent.  I don't know if it's related to thi=
s
> problem, though.

Also, even if you can't change the data representation, there's a more
idiomatic way to do that in SQL: use a row comparison.

SELECT ...
WHERE row(event_sec, event_usec) >=3D row(time.seconds, time.useconds) ...

I doubt this is notably more execution-efficient, but if you're getting a
bad rowcount estimate it should help with that.  It's easier to read too.

			regards, tom lane