From tiaswin@gmx.de Wed Sep  14 15:02:07 2022
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 <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1oYUgc-00068D-DP
	for pgsql-performance@arkaria.postgresql.org; Wed, 14 Sep 2022 15:52:26 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1oYUga-0007Ka-FV
	for pgsql-performance@arkaria.postgresql.org; Wed, 14 Sep 2022 15:52:24 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tiaswin@gmx.de>)
	id 1oYTu1-0005Hp-VG
	for pgsql-performance@lists.postgresql.org; Wed, 14 Sep 2022 15:02:13 +0000
Received: from mout.gmx.net ([212.227.17.20])
	by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tiaswin@gmx.de>)
	id 1oYTtx-0007qi-9X
	for pgsql-performance@lists.postgresql.org; Wed, 14 Sep 2022 15:02:12 +0000
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net;
	s=badeba3b8450; t=1663167727;
	bh=SmBdQtS2gZt+wAp7KNKpA5so/ZTkYPBlccsVl/z661U=;
	h=X-UI-Sender-Class:From:To:Subject:Date;
	b=F4bGvI/eLR8prSeu+PRlK8F1roLsA8efSjw1Cb4Tv+SyPvoTIUNpxT6XbLoLHWSlv
	 y/PszNVHgIpncQPikdvPckA8Bg6VT5vcbhiEiW3uvXtuy/19GxcL6iomrt7tpwwf1t
	 VlpF8N2TIPSBHEMkl9YRTMyWdN3PGcPZM90N30pw=
X-UI-Sender-Class: 01bb95c1-4bf8-414a-932a-4f6e2808ef9c
Received: from [213.187.85.4] ([213.187.85.4]) by web-mail.gmx.net
 (3c-app-gmx-bs70.server.lan [172.19.170.215]) (via HTTP); Wed, 14 Sep 2022
 17:02:07 +0200
MIME-Version: 1.0
Message-ID: 
 <trinity-e7635062-7f0a-42dc-985c-6af74dfa1787-1663167727005@3c-app-gmx-bs70>
From: tiaswin@gmx.de
To: pgsql-performance@lists.postgresql.org
Subject: Query is sometimes fast and sometimes slow: what could be the
 reason?
Content-Type: text/html; charset=UTF-8
Date: Wed, 14 Sep 2022 17:02:07 +0200
Importance: normal
Sensitivity: Normal
X-Priority: 3
X-Provags-ID: 
 V03:K1:2+Mz+YEWnV1Sq5xLdPsDT+BVqh6EmSPZTIvnuFeFlGPbzse8ZJqAl22R4F4hmsZne0gmY
 FgaZ5kJe7SnTIGHarYOHh/kaQSsmKiBzWIIQ5nbUU2KfysbnAGuNBn4xgSy8IdfPthgjh+gbQLgi
 UYCm8DpEmTUC1fJgWA4pVFOhCZGUSLgBdUrcNLXHNFFZ+aEnVsnPZDBioxiaGzciFzeiIAiW/IPE
 a3iDnQdCg4rOscOEBMmBEA7oxIYlCq53h4bhoR6Q1VFGvRW38C+pmPRDszYe5YMDiynfrPiXMguU
 JQ=
X-Spam-Flag: NO
X-UI-Out-Filterresults: notjunk:1;V03:K0:mrkEZr+FrFA=:jGWyJdWygtwnfZQOTxjR+j
 1EsQr/moAnsgq19cSzksv6xSmhTJDXJG5r2pEJ5CJNWnSpsed5GvAttqkPdncFwncu68l0fPN
 qa7+A2DBjdwjS9veXv9HsC2bstaX8IEAzxcAr1RFgVMW9TNoOPK/9//7EPmvgzWlGFyHzFUpe
 ZTaA8U7HRqFBWCx1/KjgRcGK+fT1uedWZaWOqRTU96x+JWwRWuy/TISkMaWdZJKNOEB8lFCus
 CIfVO+CWbnF6e6lZQhnuMogy2VQUKadYP8blvNkqU6Lmp72z91oa3wIsUojLrOrj4lgr/4Y4Y
 7/g2mtg9+VhH6+qfgVDe5UlOpczqP6PHy9SXJcBkhBUMq29CXmavzh0dE0dJpxM0QWx2ea4HK
 7fEdgS3wVhYoy4a5zJZLLfmYv21jncm7HlV56BV+bjRmU1zDHKrk1eL74bJBoE5aitlUC3IKc
 lRroLuxBpDoq/aV2Qh3SdwBkTudkemj1qlLbKYJb9b+LgUrx/Jb5GboQYKdY4VAhWOD0tvH+5
 NRx2YV9UCIKD7hhKsaFPLQZL0PhelrfOBRqKTaa0aYc7eTMXfkAUbq2zbBKPunRblWuD3iC2y
 YX2X1qLFnq6kffoh7pPcGj7/G9yfFW6eQYzmhIxonpRfztDkLjAgu0dL+wX+/+B+f9+OZ2yMp
 C8U2GlM6uS69k2dOKUdN/AVz7mLtmLMkWj8pOaxcwxsyqgHIBIn+V/FTXSzkIwElfEAC7y/1Y
 CG+6P661BnXoRFnHyubKUA5h/Nhm5hAqGL746KyxB0L95+dAlfkjOd6TpurrrEaSWpfqfqZRL
 NT4pCmN
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Archived-At: 
 <https://www.postgresql.org/message-id/trinity-e7635062-7f0a-42dc-985c-6af74dfa1787-1663167727005%403c-app-gmx-bs70>
Precedence: bulk

<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>What could be the reason of a query, which is sometimes fast and sometimes slow (factor &gt;10x)?</div>

<div>(running on a large table).</div>

<div>&nbsp;</div>

<div>&nbsp;</div></div></body></html>





From pryzby@telsasoft.com Wed Sep  14 15:59:53 2022
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 <pgsql-performance-owner+archive@lists.postgresql.org>)
	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 <pgsql-performance-owner+archive@lists.postgresql.org>)
	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 <pryzby@telsasoft.com>)
	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 <pryzby@telsasoft.com>)
	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 <pgsql-performance@lists.postgresql.org>;
 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 <pryzby@telsasoft.com>
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: 
 <trinity-e7635062-7f0a-42dc-985c-6af74dfa1787-1663167727005@3c-app-gmx-bs70>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <trinity-e7635062-7f0a-42dc-985c-6af74dfa1787-1663167727005@3c-app-gmx-bs70>
User-Agent: Mutt/1.9.4 (2018-02-28)
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Archived-At: 
 <https://www.postgresql.org/message-id/20220914155953.GY31833%40telsasoft.com>
Precedence: bulk

On Wed, Sep 14, 2022 at 05:02:07PM +0200, tiaswin@gmx.de wrote:
> <html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>What could be the reason of a query, which is sometimes fast and sometimes slow (factor &gt;10x)?</div>
> <div>(running on a large table).</div>
> <div>&nbsp;</div>
> <div>&nbsp;</div></div></body></html>

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