pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery is taking too long i intermittent
4+ messages / 3 participants
[nested] [flat]
* Query is taking too long i intermittent
@ 2022-06-06 09:58 Mayank Kandari <mayank.kandari@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Mayank Kandari @ 2022-06-06 09:58 UTC (permalink / raw)
To: pgsql-performance
0
<https://stackoverflow.com/posts/72515636/timeline;
I am using libpq to connect the Postgres server in c++ code. Postgres
server version is 12.10
My table schema is defined below
Column | Type | Collation | Nullable | Default |
Storage | Stats target | Description
---------------------+----------+-----------+----------+------------+----------+--------------+-------------
event_id | bigint | | not null | |
plain | |
event_sec | integer | | not null | |
plain | |
event_usec | integer | | not null | |
plain | |
event_op | smallint | | not null | |
plain | |
rd | bigint | | not null | |
plain | |
addr | bigint | | not null | |
plain | |
masklen | bigint | | not null | |
plain | |
path_id | bigint | | | |
plain | |
attribs_tbl_last_id | bigint | | not null | |
plain | |
attribs_tbl_next_id | bigint | | not null | |
plain | |
bgp_id | bigint | | not null | |
plain | |
last_lbl_stk | bytea | | not null | |
extended | |
next_lbl_stk | bytea | | not null | |
extended | |
last_state | smallint | | | |
plain | |
next_state | smallint | | | |
plain | |
pkey | integer | | not null | 1654449420 |
plain | | Partition key: LIST (pkey)
Indexes:
"event_pkey" PRIMARY KEY, btree (event_id, pkey)
"event_event_sec_event_usec_idx" btree (event_sec, event_usec)
Partitions: event_spl_1651768781 FOR VALUES IN (1651768781),
event_spl_1652029140 FOR VALUES IN (1652029140),
event_spl_1652633760 FOR VALUES IN (1652633760),
event_spl_1653372439 FOR VALUES IN (1653372439),
event_spl_1653786420 FOR VALUES IN (1653786420),
event_spl_1654449420 FOR VALUES IN (1654449420)
When I execute the following query it takes 1 - 2 milliseconds to execute.
Time is provided as a parameter to function executing this query, it
contains epoche seconds and microseconds.
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
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.
We add a new partition to the table every Sunday and write new data in the
new partition.
--
regards
Mayank Kandari
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query is taking too long i intermittent
@ 2022-06-06 10:35 Justin Pryzby <pryzby@telsasoft.com>
parent: Mayank Kandari <mayank.kandari@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Justin Pryzby @ 2022-06-06 10:35 UTC (permalink / raw)
To: Mayank Kandari <mayank.kandari@gmail.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query is taking too long i intermittent
@ 2022-06-06 14:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2022-06-06 14:11 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Mayank Kandari <mayank.kandari@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query is taking too long i intermittent
@ 2022-06-06 17:13 Mayank Kandari <mayank.kandari@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Mayank Kandari @ 2022-06-06 17:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance
Thanks for the tip! I will update my process and monitor it.
On Mon, Jun 6, 2022 at 7:41 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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
>
--
regards
Mayank Kandari
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-06-06 17:13 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-06 09:58 Query is taking too long i intermittent Mayank Kandari <mayank.kandari@gmail.com>
2022-06-06 10:35 ` Justin Pryzby <pryzby@telsasoft.com>
2022-06-06 14:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-06-06 17:13 ` Mayank Kandari <mayank.kandari@gmail.com>
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