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 1jPndy-00066K-QO for pgsql-bugs@arkaria.postgresql.org; Sat, 18 Apr 2020 13:36:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jPndx-0006yA-BJ for pgsql-bugs@arkaria.postgresql.org; Sat, 18 Apr 2020 13:36:25 +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 1jPndx-0006wh-50 for pgsql-bugs@lists.postgresql.org; Sat, 18 Apr 2020 13:36:25 +0000 Received: from mail-wm1-x342.google.com ([2a00:1450:4864:20::342]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jPndu-0006Hp-3w for pgsql-bugs@lists.postgresql.org; Sat, 18 Apr 2020 13:36:24 +0000 Received: by mail-wm1-x342.google.com with SMTP id a81so5865296wmf.5 for ; Sat, 18 Apr 2020 06:36:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:subject:message-id:references:mime-version :content-disposition:in-reply-to; bh=FcWwpGNkhikX6qhuamJDGWttKJfCAcyDIZ99tHjHuQY=; b=2NSEkQkPDzmNCQZCqPvT8dQOaYSllihVMSvxOE8xHxPFrzhkC5wN451yfpR8pN9oH+ gdWgFsy1mOATbP7wX3mRVw3E8a0M1KUidPIT+UBcUDMuNLDTqhL0oBRA4zXVdSMnGAJ5 h5u2RhRnRGtGlJeie3qmJMvW6K4NbJxpoJ5gtW+EKlItI4Mq1tuyqGVlu5YhOk6myycz CFxjl7IaAAS28tAExvgEDQjggJ2oqXyBxjVAnw+HoN0od/Dksf+DB9oeOI8l7D7+KLVa 8sEuFf9ZbvxNHgBqFxHF+3myg2ozgK4cyGRCauwN7g2ARyXs1IiGbCt0aOU4IYvUri02 cSQg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:subject:message-id:references :mime-version:content-disposition:in-reply-to; bh=FcWwpGNkhikX6qhuamJDGWttKJfCAcyDIZ99tHjHuQY=; b=WQFgMuBA6UDIXn7g6NtfTqcD8kJRCazDvCyP7iAFd8/HGaiGXAfxZMlHOYRU1ex3BZ wI5Rkf6Is09889DY20vaL7WVKbYNlUwuhQ8zuFwCiV9jcKTsgTYZLG2bdoGrAbywtEW9 6MNGCqi7DWvKrMtey4v7DcVvsdsIvikARCZRc+nDPo4MjFsE3+UVQtJKtufeCZ+7DM1d GsO62+79Q+7vOCiqaeUuQjKSZk2fl+kqj+YSLdigmwwZ4vAv+9TT93Ewfn6cAAwS5QJe RxREvaNaOnww99xOcirQ6ucg0sXYLu9MgpWpP0tdTZgpVPIw/Tx3WJPUsKuOLoMB5iqW NCdA== X-Gm-Message-State: AGi0PuZxmS7kccjXk4o7DwIppcSj+JNWV4gkyV9IGtCD3bXDAI4K43Wk M5qp5VWVshP7YiU807nJ8YZiAg== X-Google-Smtp-Source: APiQypL6FWQuASoiQng0goHdAFQDM35mybBt4Wh6DP/hSc0P1HIKLIRX2kb0c9ujMDNkmdgM4TVGbg== X-Received: by 2002:a1c:1dc3:: with SMTP id d186mr8189458wmd.90.1587216980790; Sat, 18 Apr 2020 06:36:20 -0700 (PDT) Received: from localhost (ip-86-49-253-141.net.upcbroadband.cz. [86.49.253.141]) by smtp.gmail.com with ESMTPSA id b66sm11636543wmh.12.2020.04.18.06.36.19 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Sat, 18 Apr 2020 06:36:20 -0700 (PDT) Date: Sat, 18 Apr 2020 15:36:17 +0200 From: Tomas Vondra To: kingyzf@outlook.com, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16377: select stuck when use order by and limit 1 if order-by-field has one index and result has no records Message-ID: <20200418133617.gs2yajumqf7q3m43@development> References: <16377-86af0a40aa096b0d@postgresql.org> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Disposition: inline In-Reply-To: <16377-86af0a40aa096b0d@postgresql.org> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hi, On Sat, Apr 18, 2020 at 10:04:05AM +0000, PG Bug reporting form wrote: >The following bug has been logged on the website: > >Bug reference: 16377 >Logged by: Yunfeng Wang >Email address: kingyzf@outlook.com >PostgreSQL version: 11.6 >Operating system: Centos 7.6 >Description: > >select stuck when use order by and limit 1 if order-by-field has one >index: >and possibility is high if result has no records, otherwise possibility is >low; >if no "limit 1" or no index on column pay_date, no problem; > >sql: >select * from >(select ( case when pre_pay is null then 0 else pre_pay end ) as "PRE_PAY" > >from inpatientprepay >where cancel='0' >and ehrid=4 >order by pay_date desc >) as t >limit 1 >; >other info: the inpatientprepay table has nine million records, I think >it's stuck because if no limit 1 ,speed is ok; > I think we need to see execution plans for both queries. Also, when you say "stuck" what does that mean? Does it mean it's waiting on a lock, or is it doing something but not producing results? Or what? regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services