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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux