Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iPoh9-0002nk-Mo for pgsql-bugs@arkaria.postgresql.org; Wed, 30 Oct 2019 14:11:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iPoh8-00032w-EE for pgsql-bugs@arkaria.postgresql.org; Wed, 30 Oct 2019 14:11:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from <9erthalion6@gmail.com>) id 1iPoh8-00032l-4b for pgsql-bugs@lists.postgresql.org; Wed, 30 Oct 2019 14:11:30 +0000 Received: from mail-wr1-x443.google.com ([2a00:1450:4864:20::443]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from <9erthalion6@gmail.com>) id 1iPoh6-0005Tf-1J for pgsql-bugs@lists.postgresql.org; Wed, 30 Oct 2019 14:11:29 +0000 Received: by mail-wr1-x443.google.com with SMTP id s1so2506309wro.0 for ; Wed, 30 Oct 2019 07:11:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=date:from:to:subject:message-id:references:mime-version :content-disposition:in-reply-to; bh=4Dpve0m8+wiUq4tJUu5hyItQAb02CzwY5DgCRgu2jA4=; b=EtS9smzpfVLrIUV3x7cSlD4qjfoc7x6f7AgfRSXwBmwWw6l/0ZKmmxpTgsPOx0/hHQ XIKOaNaygtYKvAgNoOXAQXhXgYCUyHfhk+hO3lsq5VoQ5JT2advJfAufEuvmR6SJFA1w UkcxslUb6tmLDbAwcOoFqF++SvfPlXUxQlyw7vO8kL8HEFrkv3I6gWsI2PHR6rApPpqF DR9TcLsZ8VNG/W2GT5tr9buRxToSP7WADJgQh7aArfQsQqUxJ36A2hM1KZ7loj8mzl88 Y7wYZTg6Rzgjydwgem3nDg0GOyVjRmPgsjBh3wca3zO1YPuH0A7TEan+Dg6PMvHSUyLm cSWA== 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=4Dpve0m8+wiUq4tJUu5hyItQAb02CzwY5DgCRgu2jA4=; b=pCJ9OSR6n2YqZ1jNq7ruO5M+HrjW6pB38IN/ZKoO/QtJHMA/3QQOjTQAH5y6kCLIIh MgoaMR2y8HxJ1s02X2ky8mRANaCW8tWtCTd146rmJ4ZdDDtRKvWF+xLPwJeipY1Cs6B8 ubbfFg9t0DNEWgbG2ut6/atz6zuh5XSYVfH2/nO5XAuqEVqfj01oZJ16e4XX50iMIHWn /Qwkv6qCHtPDtEylTxq4iZ/ex3jT5o5LCdwlt1t188rAEfn+xi2O1/AiXRS2zy7auVwG IQJoSSuZzSFLz+P+hx6PvDpiTufh+jdCARfLaFf86LIDnQ87j5VJWPuD2+1Rbdkneid0 QyMw== X-Gm-Message-State: APjAAAUcOKUV7oRKkEJryFSfGAHvbkduS71VNHy4t1m4s3Z276XgWlZY yGD2DOJ6Jm8vYuz0qqlP1pM= X-Google-Smtp-Source: APXvYqwI3g+nZUepG77Jp7OVFHtvrmRz4Xyu8jaIWbujnvAo/2Tj7m1W77aXpGoc/CHwskvoSVWAHQ== X-Received: by 2002:adf:dd47:: with SMTP id u7mr72800wrm.354.1572444686837; Wed, 30 Oct 2019 07:11:26 -0700 (PDT) Received: from localhost ([2a02:8109:9ac0:3777:f68c:50ff:fe28:e015]) by smtp.gmail.com with ESMTPSA id e9sm381018wrr.13.2019.10.30.07.11.25 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 30 Oct 2019 07:11:26 -0700 (PDT) Date: Wed, 30 Oct 2019 15:13:16 +0100 From: Dmitry Dolgov <9erthalion6@gmail.com> To: stepya@ukr.net, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16089: Index only scan does not happen but expected Message-ID: <20191030141316.lkzqxc5isoc4xbgz@localhost> References: <16089-89312196238e2c78@postgresql.org> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <16089-89312196238e2c78@postgresql.org> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Thank you for the report. > On Wed, Oct 30, 2019 at 12:54:31PM +0000, PG Bug reporting form wrote: > The following bug has been logged on the website: > > Bug reference: 16089 > Logged by: Stepan Yankevych > Email address: stepya@ukr.net > PostgreSQL version: 11.5 > Operating system: CentOS Linux release 7.3.1611 (Core) > Description: > > Not a real issue but rather performance leak. The issue is > reproducible on the version 11.5 and 12.0 as well. Does it mean, that on the previous versions you observed different behaviour? > The execution plan shows reading full partitions.l1_snapshot_201811 > Why do we need to read data from table. We have all needed information > in the index that is smaller. I would expect index only scan > (something like Oracle version of index fast full scan ) After a few experiments with this schema it looks like planner sometimes prefers seq scan (parallel seq scan) instead of using the index due to random read being more costly than sequential reads, even if it's necessary to read more pages. And in fact at least in my tests this was indeed faster. If you want to try out, it's possible to set random_page_cost lower and seq_page_cost higher, and planner will most likely choose a different plan, but whether it would be better or not is not clear.