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 1qDSXn-00087y-Ra for pgsql-general@arkaria.postgresql.org; Sun, 25 Jun 2023 16:24:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qDSXm-0001lz-Ob for pgsql-general@arkaria.postgresql.org; Sun, 25 Jun 2023 16:24:54 +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 ) id 1qDSXm-0001em-BJ for pgsql-general@lists.postgresql.org; Sun, 25 Jun 2023 16:24:54 +0000 Received: from mail-yw1-x1135.google.com ([2607:f8b0:4864:20::1135]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qDSXk-000NcQ-0M for pgsql-general@lists.postgresql.org; Sun, 25 Jun 2023 16:24:53 +0000 Received: by mail-yw1-x1135.google.com with SMTP id 00721157ae682-56ffd7d7fedso26093807b3.2 for ; Sun, 25 Jun 2023 09:24:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1687710291; x=1690302291; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=f6Z87+96F1laF6kJaryMDzbpuF5i3wnmGosa/iF9tSY=; b=fDkPIBdIBaa+aAGYfgA8mj2ycFM75HssUxJWypawVBG9V6+k9MnwHpOlUu9y93tnp4 eXnzDOHI3EfChUpU5lwh0N5CYRaiI14tJ+CQgkjqI2wpn7jTm2IIkzq+fwLqfpH28VtL RULj6lkN7pIyhOle5w0HE5Mnyycu+sigYGNf2L7CpU/gbaya4C4Ho/wUSCFJf5vqlqk2 xmOQsoqFBwmMhNnes9hxaNa23ZKDSG7Ui7XJl4EfRg6q1f4AdnY38thpW2x6Sc0vDTla oGXqhemUV+uRy5h8oZLu7F7gyl53gSHtrl/QlqrWV456jlVuEzjZkYOymk5fi1p6OeRK /L1w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1687710291; x=1690302291; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=f6Z87+96F1laF6kJaryMDzbpuF5i3wnmGosa/iF9tSY=; b=FZBO7RW2gt2gLmLgdhSlAB0eTpSxM5JsEem54xAPBkPKkMUInnR5mszpRHbZt22XSz +iFj+93GosZ0apZAVSoQW3vo9e/NJTmo01BcyxKW+iy86Z4vKdUabRuwRNa61unhrlr1 IS2NbdQE3l6TSJeTvJ4Gvpshw3qIwbFkXTZipwoTWMDwpO4jKXc/s7vZpE/0Mh1Lz8n+ rbR2p/7AtnrZtECm+zxRkW+JhLmfUd9R88MvScqU+33DTeHo+P4Xdq2mWArp2jh2zNnr CSb0TU8tS+ge9P14mXIXJ/whjNnc2tX52jt1JnCOzzlvffYY40qO3odZIV5NVmfgujbG oOQw== X-Gm-Message-State: AC+VfDz2A09+ccAjFhYTkyiK9lhOUbyCh3nkfDBa9C1zBSF8+1i5BmI4 CcUUgYazwg2rtuommivcX5Cjiw9dXY0= X-Google-Smtp-Source: ACHHUZ5I+7x4hPq6e7tNY8rLCZglZkmW18lQlxEUZhW5PIUsNOj9Rq2o1Rt0HsOAAimxPqNFLxzeUw== X-Received: by 2002:a81:7204:0:b0:568:be91:c2b1 with SMTP id n4-20020a817204000000b00568be91c2b1mr30230234ywc.9.1687710290839; Sun, 25 Jun 2023 09:24:50 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:5ca2:5a70:4fc:93a5? ([2600:1700:bd2c:b810:5ca2:5a70:4fc:93a5]) by smtp.googlemail.com with ESMTPSA id x7-20020a0dd507000000b0056d1a2455c1sm881412ywd.38.2023.06.25.09.24.50 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sun, 25 Jun 2023 09:24:50 -0700 (PDT) Message-ID: <20ce23c6-3c35-e5ce-b5bd-edb3371786a2@gmail.com> Date: Sun, 25 Jun 2023 11:24:49 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: bug or lacking doc hint Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 6/25/23 10:01, Marc Millas wrote: > Hi, > > I have had a perf (++) pb with a join plan  cf the pb with join plan thread. > I did simplify the thing up to when its a simple join between a 15M lines > table and a 30k lines table. > if I put in the on part something like table1.a=table2.b, Postgres does > the job in around 1 seconde. > > if in the on part of the join I write table1.a=table2.b OR > substr(table1.c,x,x) =table2.d That left side SUBSTR() can't be good for the query optimizer.  (Or I'm old and still using habits picked up 25 years ago on a different RDBMS.) Maybe a function index on table1 would help. -- Born in Arizona, moved to Babylonia.