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)/1) 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
Re: Query went slow all of sudden. ON V 11.3
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
Re: Query went slow all of sudden. ON V 11.3
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 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 >
Re: Query went slow all of sudden. ON V 11.3
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?
