From: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
---|---|
To: | Semen Yefimenko <semen(dot)yefimenko(at)gmail(dot)com> |
Cc: | Vijaykumar Jain <vijaykumarjain(dot)github(at)gmail(dot)com>, pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Very slow Query compared to Oracle / SQL - Server |
Date: | 2021-05-07 16:15:51 |
Message-ID: | 20210507161551.GT27406@telsasoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2021-05-07 16:28:42 | Re: Very slow Query compared to Oracle / SQL - Server |
Previous Message | Semen Yefimenko | 2021-05-07 15:57:19 | Re: Very slow Query compared to Oracle / SQL - Server |