From: Tom Lane <tgl@sss.pgh.pa.us>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Mayank Kandari <mayank.kandari@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query is taking too long i intermittent
Date: Mon, 06 Jun 2022 10:11:42 -0400
Message-ID: <894424.1654524702@sss.pgh.pa.us> (raw)
In-Reply-To: <20220606103504.GS29853@telsasoft.com>
References: <CAArYVwgjwhsm8opP5gNt7e189=zvdw0VHSx2Uaq9X5pna+2_Nw@mail.gmail.com>
<20220606103504.GS29853@telsasoft.com>
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=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.
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) >= 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
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: tgl@sss.pgh.pa.us, pryzby@telsasoft.com, mayank.kandari@gmail.com
Subject: Re: Query is taking too long i intermittent
In-Reply-To: <894424.1654524702@sss.pgh.pa.us>
* 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