Date: Fri, 7 May 2021 11:15:51 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Semen Yefimenko <semen.yefimenko@gmail.com>
Cc: Vijaykumar Jain <vijaykumarjain.github@gmail.com>,
	pgsql-performance@lists.postgresql.org
Subject: Re: Very slow Query compared to Oracle / SQL - Server
Message-ID: <20210507161551.GT27406@telsasoft.com>
References: 
 <CAKnyMoKUUSPiH=0QaM38GYm8UqVeR47HmX+MX+UpyL0mLERBrw@mail.gmail.com>
 <CAJnEWwmJg1aUbv5h2zsuWD2i0K-Jc052TTCGAspwv2BYejH3xQ@mail.gmail.com>
 <CAKnyMo+fqJk+0ASfonf4sLghr3R66FyFV5y1tv6tUh4SHKyykg@mail.gmail.com>
 <CAM+6J97Rcg3XOLjSoC0XXyA4SW=gqSzBJQQsGXwo1r2=5bcRGg@mail.gmail.com>
 <CAKnyMoKp9Mck1A2La+mcrwto9+T72ieUOc2Ubfr5C0VhcRAPHw@mail.gmail.com>
 <CAM+6J96ybUF4=bxh1Mc9VnYXfiJyZiHM_YxpasdF86cRHQMvAA@mail.gmail.com>
 <CAKnyMoJb=+hBwOG97zd2Jbumg1BZuYpEqTPWStaFFNmfArBvQA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAKnyMoJb=+hBwOG97zd2Jbumg1BZuYpEqTPWStaFFNmfArBvQA@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210507161551.GT27406%40telsasoft.com>
Precedence: bulk

On Fri, May 07, 2021 at 05:57:19PM +0200, Semen Yefimenko wrote:
> For testing purposes I set up a separate postgres 13.2 instance on windows.
> To my surprise, it works perfectly fine. Also indexes, have about 1/4 of
> the size they had on 12.6.

In pg13, indexes are de-duplicated by default.

But I suspect the performance is better because data was reload, and the
smaller indexes are a small, additional benefit.

> This explain plan is on a SSD local postgres 13.2 instance with default
> settings and not setting random_page_cost.

>        ->  Parallel Bitmap Heap Scan on schema.logtable (cost=61.84..16243.96 rows=1845 width=2638) (actual time=0.350..2.419 rows=2362 loops=3)
>              Output: column1, .. ,column54
>              Recheck Cond: ((logtable.entrytype = 4000) OR (logtable.entrytype = 4001) OR (logtable.entrytype = 4002))
>              Filter: (logtable.tfnlogent_archivestatus <= 1)
>              Heap Blocks: exact=2095
>              Buffers: shared hit=2109

In the pg13 instance, the index *and heap* scans hit only 2109 buffers (16MB).

On your original instance, it took 300k buffers (2.4GB), mostly uncached and
read from disk.

> This will still take some time so I will update once I have this tested.
> Seems like there is something skewed going on with the development database
> so far.

I still think you should try to cluster, or at least reindex (which cluster
also does) and then analyze.  The bitmap scan is probably happening because 1)
you're reading a large number of tuples; and, 2) the index is "uncorrelated",
so a straight index scan would randomly access 300k disk pages, which is much
worse even than reading 2400MB to get just 16MB of data.

-- 
Justin