Thread: Query went slow all of sudden. ON V 11.3
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
* 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
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
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
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( 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?
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