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
