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
