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 1rZJ1A-003RAo-Em for pgsql-general@arkaria.postgresql.org; Sun, 11 Feb 2024 23:13:48 +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 1rZJ19-005j13-KM for pgsql-general@arkaria.postgresql.org; Sun, 11 Feb 2024 23:13:47 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rZJ19-005j0t-8w for pgsql-general@lists.postgresql.org; Sun, 11 Feb 2024 23:13:47 +0000 Received: from mail-wm1-x332.google.com ([2a00:1450:4864:20::332]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rZJ16-006Gv7-C6 for pgsql-general@lists.postgresql.org; Sun, 11 Feb 2024 23:13:45 +0000 Received: by mail-wm1-x332.google.com with SMTP id 5b1f17b1804b1-4109b23a227so9240605e9.3 for ; Sun, 11 Feb 2024 15:13:44 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1707693223; x=1708298023; darn=lists.postgresql.org; h=content-language:thread-index:content-transfer-encoding :mime-version:message-id:date:subject:in-reply-to:references:to:from :from:to:cc:subject:date:message-id:reply-to; bh=Mr/jvBBQXa/ePGT3Mj56C4HGbnXs3hMKEWRkQcWlHJw=; b=X77AoNpY2w+CAK/yhSHKmoKlrE40qOgGIh17hks7JMy0IGmFoGUiG6/6dIFs6g0RSv YTGv5VBdx4vQ/j+hqUYMbJ992KwA9w/86+KGpjiCzyf1EMHhGEEco0CQtLZVujPPYOAb D1g7Lqp3s7lOpLql87/huLV4gGZuzeh3mi5hmazblXJna4oqbxPm2rtaRr5QE8jkJbLh /p1LsFZU2FYSNq/0h9j67cx9ydIJlqP3OcuVKX/77JrbnP5mWzP0K9SvstTuqNOYMFXA FKweIlqhq7klx0DWu1sBFWWdoZtyvhNLq+mBY8uK2Vn7+IY3t1NN6+9DKVUdT549XhpX Trdw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1707693223; x=1708298023; h=content-language:thread-index:content-transfer-encoding :mime-version:message-id:date:subject:in-reply-to:references:to:from :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=Mr/jvBBQXa/ePGT3Mj56C4HGbnXs3hMKEWRkQcWlHJw=; b=dHQ81yY78+8yFX0oqTNV1D6YvgmWIWgvzvIf9FtRkPR+VJErgWMM5USIhzfgv473T4 fVAVaajdqTZSaTrWSFts2yQygb3V16RZQE4XE2rX/cMSBrUMwrerZlKWhxsuNWzeKTqI aidVdL+NOau9qkQasdROqJ+fObLg3vKZiI3Lhv1D7igsp4mFe6mz5KiO91RZwTdKKsrA wH35qxWbiMO5YHwd0AvoxnjenI/CYxbhr0WouY5ra31fLGxgfE1tOpw9K/1zLhMLTGKX nSB7wMbYTWh8sPxtBvVSuhl88I2LOQ/FBwgOOQRnGAri86oHeyquhU3ugfKa9KkqMPY9 Py6Q== X-Gm-Message-State: AOJu0YyKiVh3xT4xf/15XtHGqbm9FMlC48cAkUU8MnMqXkD+LPHPMI5s 8hNRkQeQfJYoS8xmQM9iwRzZlshj+HWpQN2HW6KxW1lRwyZNCqFQCEqRYZvw/ts= X-Google-Smtp-Source: AGHT+IFkAwyVPuLlcT6spV+Ur4z1g1qPodI/lqfcBljlP/yjxsau/WDuPOZU9s+3w58F0cNTHRY+hg== X-Received: by 2002:a05:600c:3586:b0:40e:d30b:6129 with SMTP id p6-20020a05600c358600b0040ed30b6129mr4180797wmq.13.1707693222581; Sun, 11 Feb 2024 15:13:42 -0800 (PST) Received: from STEFANOOTTA36DB ([134.19.170.128]) by smtp.gmail.com with ESMTPSA id i15-20020a05600c354f00b00410e2214d4bsm322960wmq.42.2024.02.11.15.13.41 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sun, 11 Feb 2024 15:13:42 -0800 (PST) From: To: References: <002401da5d32$8637c200$92a74600$@gmail.com> <98a7e782-96bc-4c55-a919-bda0d5d35f87@aklaver.com> In-Reply-To: <98a7e782-96bc-4c55-a919-bda0d5d35f87@aklaver.com> Subject: RE: Query hangs (and then timeout) after using COPY to import data Date: Mon, 12 Feb 2024 00:13:40 +0100 Message-ID: <004301da5d3f$f3d0ef50$db72cdf0$@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQFmGn6UYdVDecJ0WtSu7Vwz+YoTdgLDbAzHsdgugVA= Content-Language: it List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Thanks, Adrian, for the suggestion, but same problem. I've just tried to execute "ANALYZE" (on the whole database) after the = import of all the tables (with COPY) and before the other queries, but = the query always hangs (I hope this was the way you suggested). Stefano > -----Original Message----- > From: Adrian Klaver > Sent: Sunday, February 11, 2024 10:42 PM > To: steott@gmail.com; pgsql-general@lists.postgresql.org > Subject: Re: Query hangs (and then timeout) after using COPY to import = data >=20 > On 2/11/24 13:37, steott@gmail.com wrote: > > Hello, > > > > I=E2=80=99m new to PostgreSQL. I=E2=80=99m trying to migrate an app = from SqlServer to > > Postgres (it=E2=80=99s written in C# and uses Npgsql) > > > > I=E2=80=99ve tried with Postgres 16.1 and 16.2 on Windows Server = 2019. > > > > This app used SqlServer=E2=80=99s Bulk Insert to import some tables = (about 50 > > tables) from another database, I replaced it with Postgres=E2=80=99 = COPY > > function: this part works correctly. > > > > After the import, I execute sequentially (not in parallel) some > > queries in these tables, to update some data and to make some = validations. > > > > At some point, systematically, one of these queries hangs, and after > > 10 minutes (the CommandTimeout that I set) it throws this exception: > > > > Exception while reading from stream ---> System.TimeoutException: > > Timeout during reading attempt > > > > at Npgsql.Internal.NpgsqlConnector > > > > The query is this one: > > > > > > SELECT Id FROM Item > > > > WHERE Id NOT IN ( > > > > SELECT ItemId FROM ItemUom) > > > > LIMIT 100 > > > > The same query, executed from pgAdmin, returns the result in less = than > > a second (even if it=E2=80=99s executed while the query from my app = is running). > > > > (actually the result are 0 record, but it=E2=80=99s correct: the = query it=E2=80=99s > > just a validation that there are no records in that query) > > > > While the query is running from my app, I noticed that the CPU goes > > beyond 95%, even up to 100%, due to 3 postgres.exe processes. > > > > The RAM usage is less than 70%. > > > > In pgAdmin I=E2=80=99ve executed a query to list the running = queries, and I > > can see that one. > > > > My issue seems to be very similar to this one: > > https://stackoverflow.com/questions/77195107/npgsql-timeout-during- > rea > > ding-attempt > > re > > ading-attempt> but I didn=E2=80=99t understand how that guy solved = the > > problem. > > > > If I import less tables from the external database, the query = doesn=E2=80=99t > > hang and runs correctly, so this make me think about some resources > > that could =E2=80=9Cfinish=E2=80=9D, but I haven=E2=80=99t = understood which one (for example > > the connections used to import the tables and all the commands and > > datareader used to execute the queries seem disposed correctly). > > > > I don=E2=80=99t know if it could be due to some Postgres parameter. > > > > Do you have any suggestions to solve this problem? > > >=20 > Run ANALYZE on the tables/database. >=20 > See: > https://www.postgresql.org/docs/current/sql-analyze.html >=20 >=20 > -- > Adrian Klaver > adrian.klaver@aklaver.com