Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ro1VM-00GciI-GO for pgsql-admin@arkaria.postgresql.org; Sat, 23 Mar 2024 13:33:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ro1VK-009zxj-UV for pgsql-admin@arkaria.postgresql.org; Sat, 23 Mar 2024 13:33:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ro1VK-009zxb-En for pgsql-admin@lists.postgresql.org; Sat, 23 Mar 2024 13:33:47 +0000 Received: from mail-ej1-x62c.google.com ([2a00:1450:4864:20::62c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1ro1VH-0065JC-Sk for pgsql-admin@lists.postgresql.org; Sat, 23 Mar 2024 13:33:46 +0000 Received: by mail-ej1-x62c.google.com with SMTP id a640c23a62f3a-a4749eecff7so24243766b.2 for ; Sat, 23 Mar 2024 06:33:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20230601.gappssmtp.com; s=20230601; t=1711200821; x=1711805621; darn=lists.postgresql.org; h=mime-version:user-agent:content-transfer-encoding:autocrypt :references:in-reply-to:date:to:from:subject:message-id:from:to:cc :subject:date:message-id:reply-to; bh=93NHJ/OhlzG13OTiqQt7sIzLZx5sUPf1lPp8zGyLhXQ=; b=v7RI6/YHkC5qst7TgX/E+G0rEMMjrHBtjLuYoNF6kOZxRrCg/W1VufwjAWr7Xz9X0p HH22Bfx1sAGSfKbdD+tw8MIeI7motypdEYj6rAYVyPCe/uuK7obsDqxgtPc992N6rcog 4oYWcnRZfwiFoLZ3iH6wTQUKjLs3iSd+Ak+nUptfPwacwpARW8jvrt1BzWSVbbdcIXv9 zvJwb6fRVt8P0UPLtpHGB+sTnaq/S1tbd1qRxnjGl+s2O3wJGWSX49r46TimK+DF03dy CtdYU62H11G4ba3hntFZ/7v0V7bkvFaQ4qTVEQBRHQX0TbTGJxiEzZ/ayaJ+Bred+FbS Lc2g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1711200821; x=1711805621; h=mime-version:user-agent:content-transfer-encoding:autocrypt :references:in-reply-to:date:to:from:subject:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=93NHJ/OhlzG13OTiqQt7sIzLZx5sUPf1lPp8zGyLhXQ=; b=bOZCa9RoRHjdRQPMDyUrdnBJPnCSl8wpRLStqsiSHQO+7RWUA2dtFMipT+a+EKkgq6 jFlUQj3Yfxq/E9jCMjuYO8bC4jrNBMwlk/WUA9/YtAmHcMuB9bOoYOKlHT0bGOKYm0N+ FItxdnQyYia/E2U3yE6m5v9QL2ie8HHYJLFMnowbkBEtu9CEV76MBhL5AyZzExx9pbOa uV3mvRNthBj+u/5TDvRrU8gvFIjPiLaHz5+2Fh83dZZY2rLa3LnU+4kYR8KguS4fICul YOu7SIRX9EWanCgIERTGZp1AgBw+E29P8vn9ndkDt+jPuwGafYEYYOm9iX0EKO1RcycC ewLQ== X-Forwarded-Encrypted: i=1; AJvYcCX+0JuNL9zh12+TKFdQE7mrLljlEhu81R+1TbMIHHkYL2VvL18qIcT3oPCH0XRUI18jZ+p5XgCIE4dToloLwTayZg+5sYXTcDiRJsA/EgW5aQ== X-Gm-Message-State: AOJu0Yz/HbWYuPUKJZNjIFEVwpsNU3WwQsSRrjNA6ZRWm9PeL2CZw08Z EdSVt7wI/SnuyRh8fuRAxBxipfDXORliMqbz2gfIVbXkRANkMex2GkrHKSzG2UM= X-Google-Smtp-Source: AGHT+IE8EclOBsvtyBIMIrVhinYkpsKjBjAGuQeXjpoVHvFSCCxDm/aTzX082uMHlfzNdqZ2MUCUpQ== X-Received: by 2002:a17:906:2f95:b0:a47:36b1:8992 with SMTP id w21-20020a1709062f9500b00a4736b18992mr1365178eji.10.1711200820727; Sat, 23 Mar 2024 06:33:40 -0700 (PDT) Received: from localhost.localdomain ([2001:871:260:6762:5752:af3b:750e:22e7]) by smtp.gmail.com with ESMTPSA id bn23-20020a170906c0d700b00a46a9425fe5sm900923ejb.212.2024.03.23.06.33.40 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Sat, 23 Mar 2024 06:33:40 -0700 (PDT) Message-ID: Subject: Re: Cursor with hold for select From: Laurenz Albe To: Bindra Bambharoliya , pgsql-admin@lists.postgresql.org Date: Sat, 23 Mar 2024 14:33:39 +0100 In-Reply-To: References: Autocrypt: addr=laurenz.albe@cybertec.at; prefer-encrypt=mutual; keydata=mQINBGGDwAQBEADgbWy5cKXQld3N2mF+DFyiNFbi2oBl2T+XgxpPF8wTRw2D/u4bBKXP0SYSE/lA86jIVNWWU0gf1KODIkVvgJm2w4vH2VBV1b7ddVViGl1Iu+9zaRnv9wulhnH42KefepXnoean6UT1EzLM0opF/Ik0j+40TxdRtobkBprkQUyHDXWlHc2ffPs3SipyFEP9AVLf7ejRC46CXWDnsqjOBSMEW8Z4HiK/8RrPZBsKLts8dJxKF4pygOdJb0CWk8k/X1jbcfdxo+zOLjOMvJcSJ2pFdJmQHU+JufB3rePziqQ2S9Ur6sccr9XnTC1GVBWN4Lf5VHq+vf+bFJjVwg+2hrySZnAVfcOrxoqFLErr7ug1zN2nM1kcpgA4VWn4gxlJtYNYYq+9WxX5dtvnNANlG3ZCrRKQzl8lxtzoF6Zo7LUhEqPaHDwn7Rvs+IdbOn41lF5UDTJGqmC4gS/bZydW2Fy3YWm4aSaN9fgFf8D+PVkrlKAZB7gBLz1TyHjbcRf85cYF+GKKrDld5SzMB/V60VX3oP/Eo8ikFpyWaqiz1f9X7MBot3/PjJkY+wDzp3nmb19QEcOBuQiSQ4xds2r0HewbuHTAR68u8jNNMGmpm2j4x+g09Jd/WQDjqlTBZ/jEltH41fYCCPWMfljXTOOXu2eLNGdfi7ETZogtwjM9oTtSPQARAQABtCdMYXVyZW56IEFsYmUgPGxhdXJlbnouYWxiZUBjeWJlcnRlYy5hdD6JAk4EEwEIADgWIQR0CqhbZGGABqoaSbdi8bhXA2EdmAUCYYPABAIbAwULCQgHAgYVCgkICwIEFgIDAQIeAQIXgAAKCRBi8bhXA2EdmM/6EADK232JCwmBzhlj8h7U9CjG6kx0JHP3uJGv+XfsHtHAlmY/RCwF1BHMEsRlk bT5UrLvJ2jb99bA9QARzhFaxzyn0F/BUKzuIjRGNs/n6d5dNUFA0kOt8sX+TacmC GEyjEBCrVCm4ranBiUyePn9NhHNWnaex7pJyqvMLLdwW9BEMJx0Fqo+DN8ukbXmYRsmhEtd3ue+x/luYmOmJnaGtzInaY5aOJYbW9XqoRIZkZvOCgbi1FfvNmoqWa+3oVxTOgw9RafjJDyW0lTHzKGjbGI5ofMU98l+/hKJFYJqWUF6VpFJY5YIcN/1lf4ZICMwDl+MPIVo/tpq8L10seJL28nLlvw3K+cI+TVW8IW/qL/LyVoDofI3USeOORuYmhpWRhik8JXX6xf3v6GrRilJIPWNFIJbxm1ZblQiQnOw3IOW7T+8nAmPin1HKqM3VrOrJQ2VtShsefNBibNAsr1oFaqcDBkn3yGG8i6CTW+FyO4PZ+/EwNxMVgktxbYdy5AT1/lpXr5tB+phhLIyVfiBvrWs5EThxYMQ/L8Y85c3GMsAy1l/x4h3jqySIYy3SCU9+jc5UVuNnXljbvkEzJ+NLWJ6C1rACFWrMszgPdh5tCrlRY9PpmYll4JbCgb8BtxEIUmR+xr50/ZElEK5iml7Q00KUekCcDt+36PsyGFTXBzNOrkCDQRhg8AEARAAzOZ2tLHlI4rrhG411h6cdCFjBZxuljaFCxFyHn3m6wbGLqwBUWC5k8UrRqjHMz88KcTSaNO7XGAmCqPdWd2SeflPZRnNTbjsVpw7mLdffsBm4JX7kki2Pvk5h0NtYeidXT1PSpc2ri4DutYXuT9uD8RAm1wUDCE5HQNUihT/WH6opt+hskHW21uHao0+y822tG0QQcGMqdQR5Vxdxj89wiEPdqW+HpU/oOZIhrf2E7prduAppxixjHy/o1rcnoznnJvc8D3+YgI9O0LrBMij89dM55pRGbLovTR1oGR3U74sX774+0xmSzeIKwZfiMUz7Atlvfk5SHOsRUFPN2Ux9kaXiiBibQpHFxt7b lDrT4wxdLJ/XCdbPPAyl+lZtOLsaHEEZvYNyTXwZc35dVf3R4/oz20HoG6s7ct8e1 AQygj43XAERzty9SkWgxs8+grp1PrGx6FHVSYRqBM8dS/ZR6yRVwOwJXPyaSSqfIF21DkE4j1y4n+ItSewPGoRp8K/yWCikt6qlkVkO2ASNIiX04fAbtzwVOaNn8ZMRNqyvLc1fED4sr49onE4cAIcBLjcC3KL+w9DUGRQCdziROj5H2Yl/sXGPdMciUHo/Uz2rggc+2th3bQiMhrHWSsBpUkDQp0yWewemstPpPgBL3h2fHKaX8B9oH5Qu/H1IgrOuX8AEQEAAYkCNgQYAQgAIBYhBHQKqFtkYYAGqhpJt2LxuFcDYR2YBQJhg8AEAhsMAAoJEGLxuFcDYR2YuPwQAMkpGtR80pQ1gVsONhdkqj0H2eU66efP/gO3CoyaoIcvrpKYj7C2HipVSmkt1gpByL0X4AMQ/vKuknUz3wd28Ba+G1dCfbVs/Xiusq+SmpUj5rTwmYqdSjWMuCo1R6oS5hdJMdUUJYGMT0QkVlm1KnW8jkmCTl9GzjDxOAsN9O6/6lPzaGFtk9XF+34Bry/N4HKiJkqpC4+UTd0AprPfzJ2jdT64e1F0+W88X8y1bTTgNrHwK4mDiLnlE4SKRuEm54lNhJz//ar86Or5BErzNpM6TL7lk44QS06hwsMrEdKIy8J/SYJPjfzR8tIUnKscclVpOgjKaBqC+0iFiVaRqAgfOlIEiezX6kMh5Q2FIUfqs46qWhhXjRrdKOEoStYAaikdLu5ZXr7vfb0ZaDh+ZwTQtbSMFolyOkecwI81MCdbMfT/1TqIGTOdAj5as9fAakk0jb2pXgUYQ8X1DVTR8ahSDVEaw9VTmWiSvTxvguVJ1Mb7gG4Gmh6aviDTJhfXtH4rPUNXhDLqrTH8JkJjyKROOMakIF68Hjse5vUfUxreBEOtb5r1Coa2Fe7ncJayaSE7ryrDbFqpZ 36UMAx4ulWMyqJajLNGY0DdG8qIsR5nxRhrnK/mrCidZ8F9/D3bWAl4rjtHlsztN59 +AnW5l0HsQcY9ntFL/zEBOaonjdJf Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.50.4 (3.50.4-1.fc39) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, 2024-03-22 at 21:54 +0530, Bindra Bambharoliya wrote: > I am facing issue with postgresql 11.17 version. > When I use normal select it executes within 15 seconds. > When use same query with begin; cursor "sql1" hold for select.... > Fetch 100 to "sql1"=C2=A0 it takes 3 minutes and sometime not executing e= ven in 10 minutes. And I need to kill it. > What could be the root cause. And how can we over one form this. The reason could be that PostgreSQL plans statements in a cursor so that the first 10% of the result are returned as fast as possible, rather than for the shortest time to retrieve the whole result set. But WITH HOLD cursors are materialized at COMMIT, so the whole result set gets calculated. You may experience better performance if you set "cursor_tuple_fraction =3D= 1.0". Yours, Laurenz Albe --=20 Cybertec | https://www.cybertec-postgresql.com