Re: Very slow Query compared to Oracle / SQL - Server

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