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
Date: Thu, 6 May 2021 15:01:03 -0500
Message-ID: <20210506200103.GP27406@telsasoft.com> (raw)
In-Reply-To: <CAKnyMoKUUSPiH=0QaM38GYm8UqVeR47HmX+MX+UpyL0mLERBrw@mail.gmail.com>
References: <CAKnyMoKUUSPiH=0QaM38GYm8UqVeR47HmX+MX+UpyL0mLERBrw@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, semen.yefimenko@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Very slow Query compared to Oracle / SQL - Server
In-Reply-To: <20210506200103.GP27406@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox