From: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
---|---|
To: | Semen Yefimenko <semen(dot)yefimenko(at)gmail(dot)com> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Very slow Query compared to Oracle / SQL - Server |
Date: | 2021-05-06 20:01:03 |
Message-ID: | 20210506200103.GP27406@telsasoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alexey M Boltenkov | 2021-05-06 20:02:07 | Re: Very slow Query compared to Oracle / SQL - Server |
Previous Message | Alexey M Boltenkov | 2021-05-06 19:58:30 | Re: Very slow Query compared to Oracle / SQL - Server |