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 1oYUnx-0007CH-4u for pgsql-performance@arkaria.postgresql.org; Wed, 14 Sep 2022 16:00:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oYUnw-0004yC-1T for pgsql-performance@arkaria.postgresql.org; Wed, 14 Sep 2022 16:00:00 +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 1oYUnv-0004xv-K2 for pgsql-performance@lists.postgresql.org; Wed, 14 Sep 2022 15:59:59 +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 1oYUns-0000Nz-Vp for pgsql-performance@lists.postgresql.org; Wed, 14 Sep 2022 15:59:59 +0000 Received: by mail-io1-xd2e.google.com with SMTP id p3so2809514iof.13 for ; Wed, 14 Sep 2022 08:59:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20210112.gappssmtp.com; s=20210112; h=user-agent:in-reply-to:content-disposition:mime-version:references :message-id:subject:cc:to:from:date:from:to:cc:subject:date; bh=5sGM5n/NlRAJJIEjdRvubfg+fk2rqLzbOrDZlU5OuG4=; b=BeYjf8o1iH7i6dvIwsosqDLMW8I8i+RZ6of7B5JFFPZ6EnllfcKWMHjNWTN969kAbk v1SU3DiFYDmbIT2RAOqYx8WyIFQe7/zFAyN9loLtWq3o0jhmTxf93c6cxyC1NF2F6muj RnR4cXnKBFmnjLds7QYmhaug9lQUlHc+ZAEARbzzHbIVRDRqCH9ZrcMT/kBXZkd/ziyZ Tu3meGMLpQYiD4J3OQ263zRwtPomsvysbwscOoRFYSe86Vj0oaDHudCWSTwP2XZ5734+ Qkz0ydpaHpPpKRArC96E2KFEFrPy9AnkGV9Pdmw+zlDwTj4DX5FCwmc+AC6QeeXhkrEL /TmA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=user-agent:in-reply-to:content-disposition:mime-version:references :message-id:subject:cc:to:from:date:x-gm-message-state:from:to:cc :subject:date; bh=5sGM5n/NlRAJJIEjdRvubfg+fk2rqLzbOrDZlU5OuG4=; b=4eSp00BvicL3W+aTBIwQV9XPtpZrJP7urWPed9gKmJ1FFnKd1Zz3gNspmnVnrDL7OX nXvrGB9E3I0+qpZ2vFsr/AsoUleFDKb0q0USh6zNZw5TGGHfnK06hEr/us9rHjYgZfkR uX6R9a6n9eUNfF1K4SKUmDfZUjSOAqlyfNmGkv0ZJl9wIB/1d0AMSKxNsY4PKW081wN+ /0fyQpcf2B7QcE5iBkRvRUiPUvkSfWyRfSTDy5jb2zU6Uw8m9nR0WfiM1y1ERf+VRuZI EkODsidH+5mnsK1DomuAxIz+nLxGcINna4b6+g9dPI8kwP+aauwglyzLeoE4TYwrirlE 77Mw== X-Gm-Message-State: ACgBeo20iApyTllJX4bbjlCgPjIfEH7J60UE03Glbip6E7cZWibCiuyf TIp0mGE1sAg57AJLH3u6N5/Ypw== X-Google-Smtp-Source: AA6agR63pJRW7xecQWPWildH5QkC9xrMHE6Lg9K93hWQNSwHewFtk+cZ8PhxWIpzY+GeRWOdhTUhXQ== X-Received: by 2002:a05:6638:35a8:b0:34a:456:e416 with SMTP id v40-20020a05663835a800b0034a0456e416mr19241981jal.229.1663171194643; Wed, 14 Sep 2022 08:59:54 -0700 (PDT) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id l186-20020a6b3ec3000000b0067b75781af9sm8256202ioa.37.2022.09.14.08.59.54 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 14 Sep 2022 08:59:54 -0700 (PDT) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id AC117801328; Wed, 14 Sep 2022 10:59:53 -0500 (CDT) Date: Wed, 14 Sep 2022 10:59:53 -0500 From: Justin Pryzby To: tiaswin@gmx.de Cc: pgsql-performance@lists.postgresql.org Subject: Re: Query is sometimes fast and sometimes slow: what could be the reason? Message-ID: <20220914155953.GY31833@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, Sep 14, 2022 at 05:02:07PM +0200, tiaswin@gmx.de wrote: >
What could be the reason of a query, which is sometimes fast and sometimes slow (factor >10x)?
>
(running on a large table).
>
 
>
 
Lots of possible issues. Is it using a different query plan ? Collect a good plan and a bad one and compare, or send both. Perhaps use autoexplain to do so. Turn on logging and send as much information as you can as described here. https://wiki.postgresql.org/wiki/Slow_Query_Questions Please try to configure your mail client to send text mail (instead of or in addition to the html one). -- Justin