Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nX0td-0006FP-2m for pgsql-performance@arkaria.postgresql.org; Wed, 23 Mar 2022 13:19:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nX0tb-000160-Mb for pgsql-performance@arkaria.postgresql.org; Wed, 23 Mar 2022 13:19:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nX0tb-00015q-CE for pgsql-performance@lists.postgresql.org; Wed, 23 Mar 2022 13:19:27 +0000 Received: from mail-io1-xd2e.google.com ([2607:f8b0:4864:20::d2e]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nX0tY-0003HA-Un for pgsql-performance@lists.postgresql.org; Wed, 23 Mar 2022 13:19:26 +0000 Received: by mail-io1-xd2e.google.com with SMTP id x4so1607501iop.7 for ; Wed, 23 Mar 2022 06:19:24 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20210112.gappssmtp.com; s=20210112; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to:user-agent; bh=FNct+XRJ3r9cHSiYEk/tteJSHNTBLNTMX5FrsSUf4aw=; b=vwPBDnG/RTh2n0R7BmvD/uoMxxGX7L4Bnxck5kusqAVyjNsid8b/BEN/EZ+M4k3CL1 tzAyarnScXn8/6Hc4KrMqU0rDk1MzAcEYGtVgffiM2cNHO33NBYeY1f5yWeFqsiRweLl ioEMWvfLUqIdnPMHSOwVBfr50wWWDjQxbD5Yd2ge/AsyrbG+t+DJvyL6zLjpFbIs7znV s9cypclFOnsjOi6yJF+58Q+WB8xe0IxZiIRbi/YzwvfMfpF7YnTg+yotol30RwYUvasI lPe1IpddD+GU1kCPUSrLfTyN3ochDVQN8U6JerLgtXy+G8oRVhFwQ42uHoP0UtzTtA31 RbsA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to:user-agent; bh=FNct+XRJ3r9cHSiYEk/tteJSHNTBLNTMX5FrsSUf4aw=; b=DzQldt0U9Y1tO086cWUXbh7GmJdnpIw4D9vajaKQiDE7JdiE3sAPlv7xSYNv91+LQo r0DR3iuYGnm/yV2BY6S/x0xfGzl6WoBGShBW6TwNgWfdcNf0czjF6D+qbw/XvTMiNXdv qh57TK/Fi/CDLgWC/oIT6KJ8MZGJoqiP4fJaU35x9IdGdMeMrcmMaWRAgTeMo+SpOB+a ZZuWZ2IHXIJp4nNUBsvS/yT+zqaz86+cwxRo170ed8mxWiV42qDjc6NyUhZK+iZuNvXw NuM3A3XNAuZApm9Ug34dvBDhGDT5RHlTZRhRDxxWWs7EPFmxMt00Cc/dziP8nLnO02Lh /DUw== X-Gm-Message-State: AOAM5331w7Ei+MDRaJ7+5RYJrD56Mezm5Hpfgb9u0H7jPEz08edS0dAh YfG8isAsc8XEOziTPhklfc4yzIo3Ur8g6Q== X-Google-Smtp-Source: ABdhPJypsabbjWrV8vrf7oOsy9wnmLL6ZlXWSO8CalsD/K4bHi8QSnr7JDpac60tRBpb0jMc3KHFKw== X-Received: by 2002:a05:6638:338e:b0:321:3495:63e3 with SMTP id h14-20020a056638338e00b00321349563e3mr8556872jav.283.1648041563023; Wed, 23 Mar 2022 06:19:23 -0700 (PDT) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id p2-20020a5d9c82000000b00649b622f528sm3507053iop.18.2022.03.23.06.19.22 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 23 Mar 2022 06:19:22 -0700 (PDT) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id DA394804873; Wed, 23 Mar 2022 08:19:21 -0500 (CDT) Date: Wed, 23 Mar 2022 08:19:21 -0500 From: Justin Pryzby To: Lars Aksel Opsahl Cc: pgsql-performance@lists.postgresql.org Subject: Re: Using system tables directly takes many hours, using temp tables with no indexes takes a few seconds for geometry_columns view. Message-ID: <20220323131921.GR28503@telsasoft.com> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.9.4 (2018-02-28) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wed, Mar 23, 2022 at 09:44:09AM +0000, Lars Aksel Opsahl wrote: > Why is temp tables with no indexes much faster system tables with indexes ? I think the "temp table" way is accidentally faster due to having no statistics, not because it has no indexes. If you run ANALYZE, you may hit the same issue (or, maybe you just need to VACUUM ANALYZE your system catalogs). -- Justin