From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp1 ([2001:41d0:2:4a6f::]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) by ms0.migadu.com with LMTPS id GM+AC/p9hGA0PQAAgWs5BA (envelope-from ) for ; Sat, 24 Apr 2021 22:22:18 +0200 Received: from aspmx1.migadu.com ([2001:41d0:2:4a6f::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp1 with LMTPS id qCMKB/p9hGA/OgAAbx9fmQ (envelope-from ) for ; Sat, 24 Apr 2021 20:22:18 +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 CF97427D16 for ; Sat, 24 Apr 2021 22:22:17 +0200 (CEST) Received: from localhost ([::1]:38250 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1laOnA-0006Yi-Rv for larch@yhetil.org; Sat, 24 Apr 2021 16:22:16 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:36928) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1laOmy-0006YX-8t for guix-devel@gnu.org; Sat, 24 Apr 2021 16:22:04 -0400 Received: from mira.cbaines.net ([212.71.252.8]:53236) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1laOmp-00026n-SE for guix-devel@gnu.org; Sat, 24 Apr 2021 16:21:58 -0400 Received: from localhost (unknown [IPv6:2a02:8010:68c1:0:8ac0:b4c7:f5c8:7caa]) by mira.cbaines.net (Postfix) with ESMTPSA id 54F3727BC7C; Sat, 24 Apr 2021 21:21:54 +0100 (BST) Received: from capella (localhost [127.0.0.1]) by localhost (OpenSMTPD) with ESMTP id c8dfd4ba; Sat, 24 Apr 2021 20:21:53 +0000 (UTC) References: <79d3d2e5c1386b1e162f1ba8380562720131856d.camel@telenet.be> <87tuo7xljp.fsf@cbaines.net> <87lf9ixz5j.fsf@cbaines.net> <87wnsziing.fsf@cbaines.net> <87mttuhyoq.fsf@cbaines.net> <878s5ahzke.fsf@cbaines.net> <87sg3hgq1r.fsf@cbaines.net> <87zgxngbd5.fsf@cbaines.net> User-agent: mu4e 1.4.15; emacs 27.1 From: Christopher Baines To: Canan Talayhan Subject: Re: [Outreachy] - Guix Data Service - Set a more informative page title In-reply-to: Date: Sat, 24 Apr 2021 21:21:43 +0100 Message-ID: <87wnsrfn6w.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=1619295737; 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=p7pvfQCRTrz/qOyOPhns3HcIgM87/on6QaOUS3VVdq8=; b=spQe8S/xAKz8cRJmhLquscjGyn8ZCDG9j4i1TMxtKXTvRQQkh+qarUR+k4nS5yzr1/fofC WOLNLSDbNECafY7JNrTToFzAOVKs3EfDR8SJQ2+zBwGvzLBV0V6Q7cFW6r6CvetrMCsELC HBxsh9xZKTxQ17wivcAjJElI/qAVtNXPB2JlVvrcPpWPvYEldgfARLw7wW8JEUGx1aZXXZ JTcFwwOegoskYVT1TOOIeTChgkumtFITsB5GCg5PKvpT1BpKbDkC5jfyFnVAJcmxPcJJrS 0l+7eBbRvlHwpdfsS8xnxPH7sr8nIXwTo0dJ3hwEJgha3CrQOZkdJe4lkR76Zw== ARC-Seal: i=1; s=key1; d=yhetil.org; t=1619295737; a=rsa-sha256; cv=none; b=ILMZ2qTweLJVR8TTAMo7ceVpg+KXcI0PDL0Guf9HY27NKEV+rp/tongHWdm/x4MgMqZpA9 Onb6M3jE1IDF+JjLTlNWsmWE5jU8bS3FpddZNbHgSKaQMS34K+qJKfhiUe+WS7pqsLapDW 3VX45wDUhNwboREDPMGxCpKmlc2ESfn4LW7e6eRTwQHtsReOBDZSgKHtu11JSLpQ383mV1 kkSFy35n6Oaa8Upx7o963G0nW1WG1rVhazrCbenCjq8tNyHRLMGVaBTKz+Y86yZmQ0yWoO Et25r/CE7LD6qUm4sWQ2LJBtE8kgU9DcoNWGBVrV3HczkP4evxPmm/7Mi0Z1qw== 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.04 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: CF97427D16 X-Spam-Score: -2.04 X-Migadu-Scanner: scn0.migadu.com X-TUID: 0G/ydXq7h/uc --=-=-= Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Canan Talayhan writes: > Thanks for your quick response. It helps a lot to me. But still, I > have some confusion about the reproduction steps. As I understand it, > I can reproduce the slow query just using the pure SQL queries without > touching the code for now, right? > > Please find my steps below: > > 1. CREATE TEMPORARY TABLE temp_package_metadata (LIKE package_metadata > INCLUDING ALL) > > As we expected, it creates a table but has no data. So I want to > insert some data but colon types are integer and I don't know any > meaningful data for these colons. So, the question to ask here is what data would the temporary table usually contain during loading data for a revision (when the slow query happens). I gave more specific information about the data in the temporary table in my previous email. > 2. CREATE TEMPORARY TABLE temp_package_metadata AS TABLE package_metadata > > Then I create a copy of the package_metadata. > > 3. EXPLAIN ANALYZE SELECT * FROM temp_package_metadata > This code generates 155 msec execution time, but I think it should > take more time. I don't see why that query should be slower, but that's also not that relevant since I think the actual slow query in question here is quite different. You can find the query in the code by looking at where it does the timing for "querying the temp_package_metadata". I've also got information from the PostgreSQL logs, which gives this as the query: SELECT package_metadata.id, package_metadata.home_page, package_metadata.= location_id, package_metadata.license_set_id, package_metadata.package_desc= ription_set_id, package_metadata.package_synopsis_set_id FROM package_metad= ata INNER JOIN temp_package_metadata ON (package_metadata.home_page =3D tem= p_package_metadata.home_page OR (package_metadata.home_page IS NULL AND tem= p_package_metadata.home_page IS NULL)) AND (package_metadata.location_id = =3D temp_package_metadata.location_id OR (package_metadata.location_id IS N= ULL AND temp_package_metadata.location_id IS NULL)) AND (package_metadata.l= icense_set_id =3D temp_package_metadata.license_set_id OR (package_metadata= .license_set_id IS NULL AND temp_package_metadata.license_set_id IS NULL)) = AND (package_metadata.package_description_set_id =3D temp_package_metadata.= package_description_set_id OR (package_metadata.package_description_set_id = IS NULL AND temp_package_metadata.package_description_set_id IS NULL)) AND = (package_metadata.package_synopsis_set_id =3D temp_package_metadata.package= _synopsis_set_id OR (package_metadata.package_synopsis_set_id IS NULL AND t= emp_package_metadata.package_synopsis_set_id IS NULL)) --=-=-= Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQKlBAEBCgCPFiEEPonu50WOcg2XVOCyXiijOwuE9XcFAmCEfddfFIAAAAAALgAo aXNzdWVyLWZwckBub3RhdGlvbnMub3BlbnBncC5maWZ0aGhvcnNlbWFuLm5ldDNF ODlFRUU3NDU4RTcyMEQ5NzU0RTBCMjVFMjhBMzNCMEI4NEY1NzcRHG1haWxAY2Jh aW5lcy5uZXQACgkQXiijOwuE9XcfPRAAjZfatbPUQMZ5CBZROj8ykaU9uQTU7COy z5b5T9Xs4VcfltIQjWT43bwwATnjzBSIi/+k9IgkCsDi2lxF+LEUwkvE7EqolcAQ elSAYrIhywzlGEn14gjYXO2q6JsYZxPuhbHz3kfhmsURWI4gF/akS7sDo6QsmQfT oSy3FCXshwYXtQhZbYJTtnTnnyzyzin/lt10BBYilundOWOP7VjP6hga3dKwQVln YRu3OeHlb8qBzOYe/dCK9LZ7KLIuY0VTIQMfWffvNUzY0x1tyQ2xKt3NLrincPrU 3UoLxNxF7yVcHcvIHRPKxNQ5mtjnIq3Du7OBcNbfh36QV6maEI0oGRUGrozTrPBq bfvorhcfNjDtHy+Sq9mE1cKNTQ/hJg7/WLn89w0sEg747K/c/cQKb1vJTs90vvvG 4Vk04iyIoaJuaMbj7DSEN+18tN1wGh7zknMBIZ84foG3KbtGYR4B9WkNEviDjud0 HuDhzW98QKKwVZHx9c/sFct67p1Uub2GEIpbGJfFCfblY82UrVJA2WhLY0JHaOcU N+em2ufTp1boQCK4jsIuPD30gKXdVOWdUz1iajACSrPvotYL9eLU+fploCz4Dd4a dbqfH8p3N8497AD6cf1AO+oItoYLBK3MzQ8i/MIC5NZ646G3e6uWwXCGYLbbOzZ0 qa+696kDKi4= =rPmO -----END PGP SIGNATURE----- --=-=-=--