From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp2 ([2001:41d0:8:6d80::]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) by ms0.migadu.com with LMTPS id cBCLK41XiGBnFAAAgWs5BA (envelope-from ) for ; Tue, 27 Apr 2021 20:27:25 +0200 Received: from aspmx1.migadu.com ([2001:41d0:8:6d80::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp2 with LMTPS id 8FM6J41XiGA0OgAAB5/wlQ (envelope-from ) for ; Tue, 27 Apr 2021 18:27:25 +0000 Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by aspmx1.migadu.com (Postfix) with ESMTPS id 3234116C90 for ; Tue, 27 Apr 2021 20:27:25 +0200 (CEST) Received: from localhost ([::1]:53238 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1lbSQd-0003e0-LY for larch@yhetil.org; Tue, 27 Apr 2021 14:27:24 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:57066) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1lbSQC-0003bW-1V for guix-devel@gnu.org; Tue, 27 Apr 2021 14:26:57 -0400 Received: from mira.cbaines.net ([212.71.252.8]:35932) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1lbSQ7-00072q-1W for guix-devel@gnu.org; Tue, 27 Apr 2021 14:26:55 -0400 Received: from localhost (unknown [IPv6:2a02:8010:68c1:0:8ac0:b4c7:f5c8:7caa]) by mira.cbaines.net (Postfix) with ESMTPSA id 1557F27BC7C; Tue, 27 Apr 2021 19:26:50 +0100 (BST) Received: from capella (localhost [127.0.0.1]) by localhost (OpenSMTPD) with ESMTP id ec0be952; Tue, 27 Apr 2021 18:26:49 +0000 (UTC) References: User-agent: mu4e 1.4.15; emacs 27.1 From: Christopher Baines To: Canan Talayhan Subject: Re: [Outreachy] [Guix Data Service]: Identify the slow parts of process In-reply-to: Date: Tue, 27 Apr 2021 19:26:49 +0100 Message-ID: <87a6pjfus6.fsf@cbaines.net> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="=-=-="; micalg=pgp-sha512; protocol="application/pgp-signature" Received-SPF: pass client-ip=212.71.252.8; envelope-from=mail@cbaines.net; helo=mira.cbaines.net X-Spam_score_int: -18 X-Spam_score: -1.9 X-Spam_bar: - X-Spam_report: (-1.9 / 5.0 requ) BAYES_00=-1.9, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001 autolearn=ham autolearn_force=no X-Spam_action: no action X-BeenThere: guix-devel@gnu.org X-Mailman-Version: 2.1.23 Precedence: list List-Id: "Development of GNU Guix and the GNU System distribution." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Cc: guix-devel@gnu.org Errors-To: guix-devel-bounces+larch=yhetil.org@gnu.org Sender: "Guix-devel" X-Migadu-Flow: FLOW_IN ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=yhetil.org; s=key1; t=1619548045; h=from:from:sender:sender:reply-to:subject:subject:date:date: message-id:message-id:to:to:cc:cc:mime-version:mime-version: content-type:content-type:in-reply-to:in-reply-to: references:references:list-id:list-help:list-unsubscribe: list-subscribe:list-post; bh=RUQjCc+o/FWEMewhZAqJbuodXfEz5CAF4YMXHFdtPmY=; b=Nb5y5QcOV7wnFQ7dqZeFcd26jHZK6fTx1PxRC577Ky3bAFt5WO4CzgPNnSOVZNRvBGx9m0 62QKd4WSKW9gqq3NDZynyhnbdtXRc2VBEBFH3nP/FMAYrOEN+dtWYiy6cXh2MOLEp5h7Kj SoGK5JgZ5JfJDomZCOMr1lOYBGuNxtXEN63P7gFXKHteM1gkvdluXHqKGZtHHPRTHylioJ YGqxE/b2qcjrKrfy6aVShRFBTa9V4kTXiEPd9gcfaBlCq6XZzYAE9BmUcfMCD041/flwav pKi1fhrj+K3hk4u5Mgk7dBD6TinY/8auOtIR1NHHmc/0BuObQhjzmDzDNni9sw== ARC-Seal: i=1; s=key1; d=yhetil.org; t=1619548045; a=rsa-sha256; cv=none; b=fRBr0Fz9mG/Lw5QmZw1X/ddAstRGZ+IN+VPJb/2vZFnlrqlxGgPRDdz2Eax7+H9QoTvRDa ii5GImYXV1yXVM/cTb/emTdiiL/o9r2wGGBsQSf/oqgLMW5dPjMgYsDYqoYY4eeDyKZO2x ZBKUkYyfpX1oHMgtaizHTlXJwsV/vI/JVTONoKG5yiLvocgN77swcZl41DabvV5jHYF1hP 0NlZOXBOg7bWxp4tSc+gJq3Hn5BXPEDYfh11U18XnuBZUEXRqrL+h+P0Ct3V3jWz9Xhf/w CZbrUulv5IFTNulZ97+Ljccp2caxplAd4EbHHigZounAJXG75wcmabu0c/o9bA== ARC-Authentication-Results: i=1; aspmx1.migadu.com; dkim=none; dmarc=none; spf=pass (aspmx1.migadu.com: domain of guix-devel-bounces@gnu.org designates 209.51.188.17 as permitted sender) smtp.mailfrom=guix-devel-bounces@gnu.org X-Migadu-Spam-Score: -2.05 Authentication-Results: aspmx1.migadu.com; dkim=none; dmarc=none; spf=pass (aspmx1.migadu.com: domain of guix-devel-bounces@gnu.org designates 209.51.188.17 as permitted sender) smtp.mailfrom=guix-devel-bounces@gnu.org X-Migadu-Queue-Id: 3234116C90 X-Spam-Score: -2.05 X-Migadu-Scanner: scn0.migadu.com X-TUID: DKkDPCkQMbA3 --=-=-= Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Canan Talayhan writes: > I am writing to give you an update on the progress that I have made. Great :) > I've created a temporary table named temp_package_metadata[1] and > insert a revision that already in my local database[2]. Then as you > said I've run the slow query with EXPLAIN ANALYZE. (screenshot is > attached) I may understand the slow query's working logic. > > [1]CREATE TEMPORARY TABLE temp_package_metadata (LIKE package_metadata > INCLUDING ALL) > > [2]INSERT INTO temp_package_metadata (home_page, > location_id,license_set_id,package_description_set_id, > package_synopsis_set_id) VALUES ('https://zlib.net/',9,9,2373,1407) From=20this I'm guessing the temp_package_metadata table has only one row. My understanding is that this table would normally have as many rows as packages in the revision of Guix being processed. It might not be possible to reproduce the slowness of the query without more rows. --=-=-= Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQKlBAEBCgCPFiEEPonu50WOcg2XVOCyXiijOwuE9XcFAmCIV2lfFIAAAAAALgAo aXNzdWVyLWZwckBub3RhdGlvbnMub3BlbnBncC5maWZ0aGhvcnNlbWFuLm5ldDNF ODlFRUU3NDU4RTcyMEQ5NzU0RTBCMjVFMjhBMzNCMEI4NEY1NzcRHG1haWxAY2Jh aW5lcy5uZXQACgkQXiijOwuE9XdSKQ/+ISRmCgxbi6FLZBZjZ1vwMlc9zApwtWiB sqH26eVka3pi2KwsTbqqw1bypHgWD+PurO95bwGxXQ2Rv7LON1b0j6nqt22D8yVm vhs/smliphSJ6xp3EkW6DPcixRyoT/i3ikgvglr0gujcmBaMEKWRs7wdzJOe+NZ0 5xKBvsPWlDkbq5WDPLCMIE2OncY4Qx1oL/ACJpS4mr2roO85UWTDddel3CpC+Oxk B2nVQFnYPYVLfvqzxOM6/BS7NuW6oS1E5oXma4EVh9jwPjpSEV+8GIEabpIxz8Uu bGU+1Enjg9JVSR4z7++FGmi5ahdz2KUegGO+S6Uc7AxSiHO918PRu6LlCRMFt/HE T+EQ4AonTUZ+GugD9YkNx5iVHRidMFVJ7sUSeCJ5NEmVPmYeEH+CvrBv3+CyKKkh ABn55dJl2mIERl33245fDbPV8o2GKf++HpxT5xPUVVdGWEbfZflDg3qU/50QLEIF YLkJ9GMJ0aIqd7RLyuOuZ7htN5fUqf8He1vJQ2/1YZK+xNBUMyDGQHFzJmONu/XD 0oxGdz+fJGGRkD+Le3nZje1cFihwt1f4FrMXfeQfA0tYErewzjdgZhJP//vn3WPD 5aCO1CHRJl6D0QiVfJ0uxrAtf5MbRHSTN8PqkxbEYNMAp9Hqtc8t4hObb+Y/4ySY ++wtOvOsye4= =9mj2 -----END PGP SIGNATURE----- --=-=-=--