pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery went slow all of sudden. ON V 11.3
5+ messages / 4 participants
[nested] [flat]
* Query went slow all of sudden. ON V 11.3
@ 2019-10-04 10:22 nikhil raj <nikhilraj474@gmail.com>
2019-10-04 13:20 ` Re: Query went slow all of sudden. ON V 11.3 Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 5+ messages in thread
From: nikhil raj @ 2019-10-04 10:22 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hi All,
All of sudden the query went slow before the query was executing in 30- 35
sec now even after 30 mins i am not getting any result.
later I have dropped a table ( t_meners) and recreated it and again it
started working very fast.
is there way to find what happen on that why is not any issue in table how
to find out. i Have the same issue on the other databases also so that i
can check on it
SELECT ((UID-1)/10000) AS BatchNo,
* INTO "temp_tt1"
FROM
(SELECT ROW_NUMBER() OVER (
ORDER BY a."rno") AS UID,
a.*
FROM "temp_10032019020721_4470" AS a
INNER JOIN "t_ages" AS b ON LOWER(a."cr") = LOWER(b."c_pagealias")
LEFT JOIN "t_meners" AS c ON LOWER(a."cr") = LOWER(c."c_id")
WHERE c."c_id" IS NULL ) AS TempTable
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query went slow all of sudden. ON V 11.3
2019-10-04 10:22 Query went slow all of sudden. ON V 11.3 nikhil raj <nikhilraj474@gmail.com>
@ 2019-10-04 13:20 ` Justin Pryzby <pryzby@telsasoft.com>
2019-10-04 13:58 ` Re: Query went slow all of sudden. ON V 11.3 nikhil raj <nikhilraj474@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Justin Pryzby @ 2019-10-04 13:20 UTC (permalink / raw)
To: nikhil raj <nikhilraj474@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Fri, Oct 04, 2019 at 03:52:26PM +0530, nikhil raj wrote:
> Hi All,
>
> All of sudden the query went slow before the query was executing in 30- 35
> sec now even after 30 mins i am not getting any result.
Can you show "explain(analyze,buffers)" when it's running fast, and at least
"explain" when it's slow ?
> later I have dropped a table ( t_meners) and recreated it and again it
> started working very fast.
What indexes exist on that table and on temp_10032019020721_4470 ?
Justin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query went slow all of sudden. ON V 11.3
2019-10-04 10:22 Query went slow all of sudden. ON V 11.3 nikhil raj <nikhilraj474@gmail.com>
2019-10-04 13:20 ` Re: Query went slow all of sudden. ON V 11.3 Justin Pryzby <pryzby@telsasoft.com>
@ 2019-10-04 13:58 ` nikhil raj <nikhilraj474@gmail.com>
2019-10-04 19:07 ` Re: Query went slow all of sudden. ON V 11.3 Michael Lewis <mlewis@entrata.com>
2019-10-06 20:30 ` Re: Query went slow all of sudden. ON V 11.3 Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 2 replies; 5+ messages in thread
From: nikhil raj @ 2019-10-04 13:58 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org
Hi Justin,
Its been executing for 35 + mins due to statement time out its getting
canceled.
Yes temp_10032019020721_4470table index is there on cr column.
On Fri, Oct 4, 2019 at 6:50 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
> On Fri, Oct 04, 2019 at 03:52:26PM +0530, nikhil raj wrote:
> > Hi All,
> >
> > All of sudden the query went slow before the query was executing in 30-
> 35
> > sec now even after 30 mins i am not getting any result.
>
> Can you show "explain(analyze,buffers)" when it's running fast, and at
> least
> "explain" when it's slow ?
>
> > later I have dropped a table ( t_meners) and recreated it and again it
> > started working very fast.
>
> What indexes exist on that table and on temp_10032019020721_4470 ?
>
> Justin
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query went slow all of sudden. ON V 11.3
2019-10-04 10:22 Query went slow all of sudden. ON V 11.3 nikhil raj <nikhilraj474@gmail.com>
2019-10-04 13:20 ` Re: Query went slow all of sudden. ON V 11.3 Justin Pryzby <pryzby@telsasoft.com>
2019-10-04 13:58 ` Re: Query went slow all of sudden. ON V 11.3 nikhil raj <nikhilraj474@gmail.com>
@ 2019-10-04 19:07 ` Michael Lewis <mlewis@entrata.com>
1 sibling, 0 replies; 5+ messages in thread
From: Michael Lewis @ 2019-10-04 19:07 UTC (permalink / raw)
To: nikhil raj <nikhilraj474@gmail.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@lists.postgresql.org
What are approx row counts and distribution of data in the concerned tables
and columns? Have you run EXPLAIN (query plan) to get the plan that will be
executed and can you paste on https://explain.depesz.com/ and share the
link that results?
Do you have an index on LOWER( cr ) on table temp_10032019020721_4470?
Do you have an index on LOWER( c_pagealias ) on table t_ages?
Do you have an index on LOWER( c_id ) on table t_meners?
If temp_10032019020721_4470 is truly temp table, was it analyzed after
creating/inserting/updating/deleting data last, so that the optimizer knows
the number of distinct values, how many rows, most common values, etc?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query went slow all of sudden. ON V 11.3
2019-10-04 10:22 Query went slow all of sudden. ON V 11.3 nikhil raj <nikhilraj474@gmail.com>
2019-10-04 13:20 ` Re: Query went slow all of sudden. ON V 11.3 Justin Pryzby <pryzby@telsasoft.com>
2019-10-04 13:58 ` Re: Query went slow all of sudden. ON V 11.3 nikhil raj <nikhilraj474@gmail.com>
@ 2019-10-06 20:30 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
1 sibling, 0 replies; 5+ messages in thread
From: Tomas Vondra @ 2019-10-06 20:30 UTC (permalink / raw)
To: nikhil raj <nikhilraj474@gmail.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@lists.postgresql.org
On Fri, Oct 04, 2019 at 07:28:54PM +0530, nikhil raj wrote:
>Hi Justin,
>
>Its been executing for 35 + mins due to statement time out its getting
>canceled.
>
Well, without a query plan it's really hard to give you any advice. We
need to see at least EXPLAIN output (without analyze) to get an idea of
how the query will be executed. Even better, disable the statement
timeout in the session and dive use EXPLAIN ANALYZE. Of course, it's
unclear how long it'll run.
Earlier you mentioned the query started running fast after you recreated
one of the tables. That likely means the table (or the indexes on it)
are getting bloated over time. Try looking at the sizes of those objects
(and maybe use pgstattuple to get more detailed statistics before
rebuilding it next time.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2019-10-06 20:30 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-10-04 10:22 Query went slow all of sudden. ON V 11.3 nikhil raj <nikhilraj474@gmail.com>
2019-10-04 13:20 ` Justin Pryzby <pryzby@telsasoft.com>
2019-10-04 13:58 ` nikhil raj <nikhilraj474@gmail.com>
2019-10-04 19:07 ` Michael Lewis <mlewis@entrata.com>
2019-10-06 20:30 ` Tomas Vondra <tomas.vondra@2ndquadrant.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