Date: Thu, 6 May 2021 15:01:03 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Semen Yefimenko <semen.yefimenko@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Very slow Query compared to Oracle / SQL - Server
Message-ID: <20210506200103.GP27406@telsasoft.com>
References: 
 <CAKnyMoKUUSPiH=0QaM38GYm8UqVeR47HmX+MX+UpyL0mLERBrw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAKnyMoKUUSPiH=0QaM38GYm8UqVeR47HmX+MX+UpyL0mLERBrw@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210506200103.GP27406%40telsasoft.com>
Precedence: bulk

On Thu, May 06, 2021 at 04:38:39PM +0200, Semen Yefimenko wrote:
> Hi there,
> 
> I've recently been involved in migrating our old system to SQL Server and
> then PostgreSQL. Everything has been working fine so far but now after
> executing our tests on Postgres, we saw a very slow running query on a
> large table in our database.
> I have tried asking on other platforms but no one has been able to give me
> a satisfying answer.

> With the help of some people in the slack and so thread, I've found a
> configuration parameter which helps performance :
> set random_page_cost = 1;

I wonder what the old query plan was...
Would you include links to your prior correspondance ?

>         ->  Parallel Bitmap Heap Scan on schema.logtable  (cost=5652.74..327147.77 rows=214503 width=2558) (actual time=1304.813..20637.462 rows=171947 loops=3)
>               Recheck Cond: ((logtable.entrytype = 4000) OR (logtable.entrytype = 4001) OR (logtable.entrytype = 4002))
>               Filter: (logtable.archivestatus <= 1)
>               Heap Blocks: exact=103962
>               Buffers: shared hit=141473 read=153489
> 
> -------------------------------------------------------------------------------------------------------------------------
> | Id  | Operation                             | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
> -------------------------------------------------------------------------------------------------------------------------
> |   0 | SELECT STATEMENT                      |                         | 6878 |  2491K|       |  2143   (1)| 00:00:01 |
> |   1 |  SORT ORDER BY                        |                         | 6878 |  2491K|  3448K|  2143   (1)| 00:00:01 |
> |   2 |   INLIST ITERATOR                     |                         | |       |       |            |          |
> |*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| logtable                | 6878 |  2491K|       |  1597   (1)| 00:00:01 |
> |*  4 |     INDEX RANGE SCAN                  | idx_entrytype           | 6878 |       |       |    23   (0)| 00:00:01 |
> -------------------------------------------------------------------------------------------------------------------------
> 
> Is there much I can analyze, any information you might need to further
> analyze this?

Oracle is apparently doing a single scan on "entrytype".

As a test, you could try forcing that, like:
begin; SET enable_bitmapscan=off ; explain (analyze) [...]; rollback;
or
begin; DROP INDEX idx_arcstatus; explain (analyze) [...]; rollback;

You could try to reduce the cost of that scan, by clustering on idx_arcstatus,
and then analyzing.  That will affect all other queries, too.  Also, the
"clustering" won't be preserved with future inserts/updates/deletes, so you may
have to do that as a periodic maintenance command.

-- 
Justin