date:20210506

Very slow Query compared to Oracle / SQL - Server

2021-05-06 Thread Semen Yefimenko

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.

*Postgres Version : *PostgreSQL 12.2, compiled by Visual C++ build 1914,
64-bit
No notable errors in the Server log and the Postgres Server itself.

The table structure :

CREATE TABLE logtable
(
key character varying(20) COLLATE pg_catalog."default" NOT NULL,
id integer,
column3 integer,
column4 integer,
column5 integer,
column6 integer,
column7 integer,
column8 integer,
column9 character varying(128) COLLATE pg_catalog."default",
column10   character varying(2048) COLLATE pg_catalog."default",
column11   character varying(2048) COLLATE pg_catalog."default",
column12   character varying(2048) COLLATE pg_catalog."default",
column13   character varying(2048) COLLATE pg_catalog."default",
column14   character varying(2048) COLLATE pg_catalog."default",
column15   character varying(2048) COLLATE pg_catalog."default",
column16   character varying(2048) COLLATE pg_catalog."default",
column17   character varying(2048) COLLATE pg_catalog."default",
column18   character varying(2048) COLLATE pg_catalog."default",
column19   character varying(2048) COLLATE pg_catalog."default",
column21 character varying(256) COLLATE pg_catalog."default",
column22 character varying(256) COLLATE pg_catalog."default",
column23 character varying(256) COLLATE pg_catalog."default",
column24 character varying(256) COLLATE pg_catalog."default",
column25 character varying(256) COLLATE pg_catalog."default",
column26 character varying(256) COLLATE pg_catalog."default",
column27 character varying(256) COLLATE pg_catalog."default",
column28 character varying(256) COLLATE pg_catalog."default",
column29 character varying(256) COLLATE pg_catalog."default",
column30 character varying(256) COLLATE pg_catalog."default",
column31 character varying(256) COLLATE pg_catalog."default",
column32 character varying(256) COLLATE pg_catalog."default",
column33 character varying(256) COLLATE pg_catalog."default",
column34 character varying(256) COLLATE pg_catalog."default",
column35 character varying(256) COLLATE pg_catalog."default",
entrytype integer,
column37 bigint,
column38 bigint,
column39 bigint,
column40 bigint,
column41 bigint,
column42 bigint,
column43 bigint,
column44 bigint,
column45 bigint,
column46 bigint,
column47 character varying(128) COLLATE pg_catalog."default",
timestampcol timestamp without time zone,
column49 timestamp without time zone,
column50 timestamp without time zone,
column51 timestamp without time zone,
column52 timestamp without time zone,
archivestatus integer,
column54 integer,
column55 character varying(20) COLLATE pg_catalog."default",
CONSTRAINT pkey PRIMARY KEY (key)
USING INDEX TABLESPACE tablespace
)

TABLESPACE tablespace;

ALTER TABLE schema.logtable
OWNER to user;

CREATE INDEX idx_timestampcol
ON schema.logtable USING btree
( timestampcol ASC NULLS LAST )
TABLESPACE tablespace ;

CREATE INDEX idx_test2
ON schema.logtable USING btree
( entrytype  ASC NULLS LAST)
TABLESPACE tablespace
WHERE archivestatus <= 1;

CREATE INDEX idx_arcstatus
ON schema.logtable USING btree
( archivestatus ASC NULLS LAST)
TABLESPACE tablespace;

CREATE INDEX idx_entrytype
ON schema.logtable USING btree
( entrytype ASC NULLS LAST)
TABLESPACE tablespace ;


The table contains 14.000.000 entries and has about 3.3 GB of data:
No triggers, inserts per day, probably 5-20 K per day.

SELECT relname, relpages, reltuples, relallvisible, relkind, relnatts,
relhassubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE
relname='logtable';

relname
|relpages|reltuples|relallvisible|relkind|relnatts|relhassubclass|reloptions|pg_table_size|
--||-|-|---||--|--|-|
logtable  |  405988| 14091424|   405907|r  |  54|false
|NULL  |   3326803968|


The slow running query:

SELECT column1,..., column54  where ((entrytype = 4000 or entrytype = 4001
or entrytype = 4002) and (archivestatus <= 1)) order by timestampcol desc;


This query runs in about 45-60 seconds.
The same query runs in about 289 ms Oracle and 423 ms in SQL-Server.
Now I understand that actually loading all results would take a while.
(about 520K or so rows)
But that shouldn't be exactly what happens right? There should be a
resultset iterator which can retrieve all data but doesn't from the get go.

With the help of some people in the slack and so thre

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

2021-05-06 Thread luis . roberto



- Mensagem original -
> De: "Semen Yefimenko" 
> Para: "pgsql-performance" 
> Enviadas: Quinta-feira, 6 de maio de 2021 11:38:39
> Assunto: Very slow Query compared to Oracle / SQL - Server


> SELECT column1,..., column54 where ((entrytype = 4000 or entrytype = 4001 or
> entrytype = 4002) and (archivestatus <= 1)) order by timestampcol desc;
 

The first thing I would try is rewriting the query to:

SELECT column1,..., column54 
  FROM logtable
 WHERE (entrytype in (4000,4001,4002)) 
   AND (archivestatus <= 1)) 
 ORDER BY timestampcol DESC;

Check if that makes a difference...

Luis R. Weck

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

2021-05-06 Thread Alexey M Boltenkov


On 05/06/21 19:11, [email protected] wrote:

- Mensagem original -

De: "Semen Yefimenko" 
Para: "pgsql-performance" 
Enviadas: Quinta-feira, 6 de maio de 2021 11:38:39
Assunto: Very slow Query compared to Oracle / SQL - Server



SELECT column1,..., column54 where ((entrytype = 4000 or entrytype = 4001 or
entrytype = 4002) and (archivestatus <= 1)) order by timestampcol desc;
  


The first thing I would try is rewriting the query to:

SELECT column1,..., column54
   FROM logtable
  WHERE (entrytype in (4000,4001,4002))
AND (archivestatus <= 1))
  ORDER BY timestampcol DESC;

Check if that makes a difference...

Luis R. Weck



The IN statement will probable result in just recheck condition change 
to entrytype = any('{a,b,c}'::int[]). Looks like dispersion of 
archivestatus is not enough to use index idx_arcstatus.


Please try to create partial index with condition like (archivestatus <= 
1) and rewrite select to use (archivestatus is not null and 
archivestatus <= 1).


CREATE INDEX idx_arcstatus_le1 ON schema.logtable ( archivestatus ) 
where (archivestatus <= 1) TABLESPACE tablespace;

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

2021-05-06 Thread Alexey M Boltenkov


On 05/06/21 21:15, Alexey M Boltenkov wrote:

On 05/06/21 19:11, [email protected] wrote:

- Mensagem original -

De: "Semen Yefimenko"
Para: "pgsql-performance"
Enviadas: Quinta-feira, 6 de maio de 2021 11:38:39
Assunto: Very slow Query compared to Oracle / SQL - Server
SELECT column1,..., column54 where ((entrytype = 4000 or entrytype = 4001 or
entrytype = 4002) and (archivestatus <= 1)) order by timestampcol desc;
  


The first thing I would try is rewriting the query to:

SELECT column1,..., column54
   FROM logtable
  WHERE (entrytype in (4000,4001,4002))
AND (archivestatus <= 1))
  ORDER BY timestampcol DESC;

Check if that makes a difference...

Luis R. Weck



The IN statement will probable result in just recheck condition change 
to entrytype = any('{a,b,c}'::int[]). Looks like dispersion of 
archivestatus is not enough to use index idx_arcstatus.


Please try to create partial index with condition like (archivestatus 
<= 1) and rewrite select to use (archivestatus is not null and 
archivestatus <= 1).


CREATE INDEX idx_arcstatus_le1 ON schema.logtable ( archivestatus ) 
where (archivestatus <= 1) TABLESPACE tablespace;


I'm sorry, 'archivestatus is not null' is only necessary for index 
without nulls.



CREATE INDEX idx_arcstatus_le1 ON schema.logtable ( archivestatus ) 
where (archivestatus is not null and archivestatus <= 1) TABLESPACE 
tablespace;

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

2021-05-06 Thread Semen Yefimenko

Yes, rewriting the query with an IN clause was also my first approach, but
I didn't help much.
The Query plan did change a little bit but the performance was not impacted.

CREATE INDEX idx_arcstatus_le1 ON schema.logtable ( archivestatus )
where (archivestatus <= 1)
ANALYZE  schema.logtable


This resulted in this query plan:

Gather Merge  (cost=344618.96..394086.05 rows=423974 width=2549) (actual
time=7327.777..9142.358 rows=516031 loops=1)
  Output: column1, .. , column54
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=179817 read=115290
  ->  Sort  (cost=343618.94..344148.91 rows=211987 width=2549) (actual
time=7258.314..7476.733 rows=172010 loops=3)
Output: column1, .. , column54
Sort Key: logtable.timestampcol DESC
Sort Method: quicksort  Memory: 64730kB
Worker 0:  Sort Method: quicksort  Memory: 55742kB
Worker 1:  Sort Method: quicksort  Memory: 55565kB
Buffers: shared hit=179817 read=115290
Worker 0: actual time=7231.774..7458.703 rows=161723 loops=1
  Buffers: shared hit=55925 read=36265
Worker 1: actual time=7217.856..7425.754 rows=161990 loops=1
  Buffers: shared hit=56197 read=36242
->  Parallel Bitmap Heap Scan on schema.logtable
 (cost=5586.50..324864.86 rows=211987 width=2549) (actual
time=1073.266..6805.850 rows=172010 loops=3)
  Output: column1, .. , column54
  Recheck Cond: ((logtable.entrytype = 4000) OR
(logtable.entrytype = 4001) OR (logtable.entrytype = 4002))
  Filter: (logtable.archivestatus <= 1)
  Heap Blocks: exact=109146
  Buffers: shared hit=179803 read=115290
  Worker 0: actual time=1049.875..6809.231 rows=161723 loops=1
Buffers: shared hit=55918 read=36265
  Worker 1: actual time=1035.156..6788.037 rows=161990 loops=1
Buffers: shared hit=56190 read=36242
  ->  BitmapOr  (cost=5586.50..5586.50 rows=514483 width=0)
(actual time=945.179..945.179 rows=0 loops=1)
Buffers: shared hit=3 read=1329
->  Bitmap Index Scan on idx_entrytype
 (cost=0.00..738.13 rows=72893 width=0) (actual time=147.915..147.916
rows=65970 loops=1)
  Index Cond: (logtable.entrytype = 4000)
  Buffers: shared hit=1 read=171
->  Bitmap Index Scan on idx_entrytype
 (cost=0.00..2326.17 rows=229965 width=0) (actual time=473.450..473.451
rows=225040 loops=1)
  Index Cond: (logtable.entrytype = 4001)
  Buffers: shared hit=1 read=579
->  Bitmap Index Scan on idx_entrytype
 (cost=0.00..2140.61 rows=211624 width=0) (actual time=323.801..323.802
rows=225021 loops=1)
  Index Cond: (logtable.entrytype = 4002)
  Buffers: shared hit=1 read=579
Settings: random_page_cost = '1', search_path = '"$user", schema, public',
temp_buffers = '80MB', work_mem = '1GB'
Planning Time: 0.810 ms
Execution Time: 9647.406 ms


seemingly faster.
After doing a few selects, I reran ANALYZE:
Now it's even faster, probably due to cache and other mechanisms.

Gather Merge  (cost=342639.19..391676.44 rows=420290 width=2542) (actual
time=2944.803..4534.725 rows=516035 loops=1)
  Output: column1, .. , column54
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=147334 read=147776
  ->  Sort  (cost=341639.16..342164.53 rows=210145 width=2542) (actual
time=2827.256..3013.960 rows=172012 loops=3)
Output: column1, .. , column54
Sort Key: logtable.timestampcol DESC
Sort Method: quicksort  Memory: 71565kB
Worker 0:  Sort Method: quicksort  Memory: 52916kB
Worker 1:  Sort Method: quicksort  Memory: 51556kB
Buffers: shared hit=147334 read=147776
Worker 0: actual time=2771.975..2948.928 rows=153292 loops=1
  Buffers: shared hit=43227 read=43808
Worker 1: actual time=2767.752..2938.688 rows=148424 loops=1
  Buffers: shared hit=42246 read=42002
->  Parallel Bitmap Heap Scan on schema.logtable
 (cost=5537.95..323061.27 rows=210145 width=2542) (actual
time=276.401..2418.925 rows=172012 loops=3)
  Output: column1, .. , column54
  Recheck Cond: ((logtable.entrytype = 4000) OR
(logtable.entrytype = 4001) OR (logtable.entrytype = 4002))
  Filter: (logtable.archivestatus <= 1)
  Heap Blocks: exact=122495
  Buffers: shared hit=147320 read=147776
  Worker 0: actual time=227.701..2408.580 rows=153292 loops=1
Buffers: shared hit=43220 read=43808
  Worker 1: actual time=225.996..2408.705 rows=148424 loops=1
Buffers: shared hit=42239 read=42002
  ->  BitmapOr  (cost=5537.95..5537.95 rows=509918 width=0)
(actual time=203.940..203.941 rows=0 loops=1)
Buffers: shared hit=1332

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

2021-05-06 Thread Andreas Joseph Krogh


På torsdag 06. mai 2021 kl. 20:59:34, skrev Semen Yefimenko <
[email protected] >: 
Yes, rewriting the query with an IN clause was also my first approach, but I 
didn't help much.
 The Query plan did change a little bit but the performance was not impacted.
CREATE INDEX idx_arcstatus_le1 ON schema.logtable ( archivestatus ) where 
(archivestatus <= 1) 
ANALYZE schema.logtable 

 This resulted in this query plan:
 [...] 

I assume (4000,4001,4002) are just example-values and that they might be 
anything? Else you can just include them in your partial-index. 



--
 Andreas Joseph Krogh

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

2021-05-06 Thread Vijaykumar Jain

I am not sure, if the goal is just for the specific set of predicates or
performance in general.

Also from the explain plan, it seems there is still a significant  amount
of buffers read vs hit.
That would constitute i/o and may add to slow result.

What is the size of the table and the index ?
Is it possible to increase shared buffers ?
coz it seems, you would end up reading a ton of rows and columns which
would benefit from having the pages in cache.
although the cache needs to be warmed  by a query or via external extension
:)

Can you try tuning by increasing the shared_buffers slowly in steps of
500MB, and running explain analyze against the query.

If the Buffers read are reduced, i guess that would help speed up the query.
FYI, increasing shared_buffers requires a server restart.

As Always,
Ignore if this does not work :)

Thanks,
Vijay

On Fri, 7 May 2021 at 00:56, Andreas Joseph Krogh 
wrote:

> På torsdag 06. mai 2021 kl. 20:59:34, skrev Semen Yefimenko <
> [email protected]>:
>
> Yes, rewriting the query with an IN clause was also my first approach, but
> I didn't help much.
> The Query plan did change a little bit but the performance was not
> impacted.
>
>
> CREATE INDEX idx_arcstatus_le1 ON schema.logtable ( archivestatus )
> where (archivestatus <= 1)
> ANALYZE  schema.logtable
>
>
> This resulted in this query plan:
> [...]
>
>
> I assume (4000,4001,4002) are just example-values and that they might be
> anything? Else you can just include them in your partial-index.
>
> --
> Andreas Joseph Krogh
>

-- 
Thanks,
Vijay
Mumbai, India

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

2021-05-06 Thread Alexey M Boltenkov

Have you try of excluding not null from index? Can you give dispersion of archivestatus?06.05.2021, 21:59, "Semen Yefimenko" :Yes, rewriting the query with an IN clause was also my first approach, but I didn't help much. The Query plan did change a little bit but the performance was not impacted.CREATE INDEX idx_arcstatus_le1 ON schema.logtable ( archivestatus ) where (archivestatus <= 1)ANALYZE 

schema.logtableThis resulted in this query plan:Gather Merge  (cost=344618.96..394086.05 rows=423974 width=2549) (actual time=7327.777..9142.358 rows=516031 loops=1)  Output: column1, .. , column54  Workers Planned: 2  Workers Launched: 2  Buffers: shared hit=179817 read=115290  ->  Sort  (cost=343618.94..344148.91 rows=211987 width=2549) (actual time=7258.314..7476.733 rows=172010 loops=3)        Output: column1, .. , column54        Sort Key: logtable.timestampcol DESC        Sort Method: quicksort  Memory: 64730kB        Worker 0:  Sort Method: quicksort  Memory: 55742kB        Worker 1:  Sort Method: quicksort  Memory: 55565kB        Buffers: shared hit=179817 read=115290        Worker 0: actual time=7231.774..7458.703 rows=161723 loops=1          Buffers: shared hit=55925 read=36265        Worker 1: actual time=7217.856..7425.754 rows=161990 loops=1          Buffers: shared hit=56197 read=36242        ->  Parallel Bitmap Heap Scan on schema.logtable  (cost=5586.50..324864.86 rows=211987 width=2549) (actual time=1073.266..6805.850 rows=172010 loops=3)              Output: column1, .. , column54              Recheck Cond: ((logtable.entrytype = 4000) OR (logtable.entrytype = 4001) OR (logtable.entrytype = 4002))              Filter: (logtable.archivestatus <= 1)              Heap Blocks: exact=109146              Buffers: shared hit=179803 read=115290              Worker 0: actual time=1049.875..6809.231 rows=161723 loops=1                Buffers: shared hit=55918 read=36265              Worker 1: actual time=1035.156..6788.037 rows=161990 loops=1                Buffers: shared hit=56190 read=36242              ->  BitmapOr  (cost=5586.50..5586.50 rows=514483 width=0) (actual time=945.179..945.179 rows=0 loops=1)                    Buffers: shared hit=3 read=1329                    ->  Bitmap Index Scan on idx_entrytype  (cost=0.00..738.13 rows=72893 width=0) (actual time=147.915..147.916 rows=65970 loops=1)                          Index Cond: (logtable.entrytype = 4000)                          Buffers: shared hit=1 read=171                    ->  Bitmap Index Scan on idx_entrytype  (cost=0.00..2326.17 rows=229965 width=0) (actual time=473.450..473.451 rows=225040 loops=1)                          Index Cond: (logtable.entrytype = 4001)                          Buffers: shared hit=1 read=579                    ->  Bitmap Index Scan on idx_entrytype  (cost=0.00..2140.61 rows=211624 width=0) (actual time=323.801..323.802 rows=225021 loops=1)                          Index Cond: (logtable.entrytype = 4002)                          Buffers: shared hit=1 read=579Settings: random_page_cost = '1', search_path = '"$user", schema, public', temp_buffers = '80MB', work_mem = '1GB'Planning Time: 0.810 msExecution Time: 9647.406 msseemingly faster.After doing a few selects, I reran ANALYZE:Now it's even faster, probably due to cache and other mechanisms.Gather Merge  (cost=342639.19..391676.44 rows=420290 width=2542) (actual time=2944.803..4534.725 rows=516035 loops=1)  Output: column1, .. , column54  Workers Planned: 2  Workers Launched: 2  Buffers: shared hit=147334 read=147776  ->  Sort  (cost=341639.16..342164.53 rows=210145 width=2542) (actual time=2827.256..3013.960 rows=172012 loops=3)        Output: column1, .. , column54        Sort Key: logtable.timestampcol DESC        Sort Method: quicksort  Memory: 71565kB        Worker 0:  Sort Method: quicksort  Memory: 52916kB        Worker 1:  Sort Method: quicksort  Memory: 51556kB        Buffers: shared hit=147334 read=147776        Worker 0: actual time=2771.975..2948.928 rows=153292 loops=1          Buffers: shared hit=43227 read=43808        Worker 1: actual time=2767.752..2938.688 rows=148424 loops=1          Buffers: shared hit=42246 read=42002        ->  Parallel Bitmap Heap Scan on schema.logtable  (cost=5537.95..323061.27 rows=210145 width=2542) (actual time=276.401..2418.925 rows=172012 loops=3)              Output: column1, .. , column54              Recheck Cond: ((logtable.entrytype = 4000) OR (logtable.entrytype = 4001) OR (logtable.entrytype = 4002))              Filter: (logtable.archivestatus <= 1)              Heap Blocks: exact=122495              Buffers: shared hit=147320 read=147776              Worker 0: actual time=227.701..2408.580 rows=153292 loops=1                Buffers: shared hit=43220 read=43808              Worker 1: actual time=225.996..2408.705 rows=148424 loops=1                Buffers: shared hit=42239 read=42002              ->  BitmapOr  (cost=5537.95..5537.95 rows=509918 width=0) (actual time=203.940..203.941 rows=0 loops=1)

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

2021-05-06 Thread Justin Pryzby

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

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

2021-05-06 Thread Alexey M Boltenkov


On 05/06/21 22:58, Alexey M Boltenkov wrote:
Have you try of excluding not null from index? Can you give dispersion 
of archivestatus?



06.05.2021, 21:59, "Semen Yefimenko" :

Yes, rewriting the query with an IN clause was also my first
approach, but I didn't help much.
The Query plan did change a little bit but the performance was not
impacted.

CREATE INDEX idx_arcstatus_le1 ON schema.logtable (
archivestatus ) where (archivestatus <= 1)
ANALYZE schema.logtable


This resulted in this query plan:

Gather Merge  (cost=344618.96..394086.05 rows=423974
width=2549) (actual time=7327.777..9142.358 rows=516031 loops=1)
  Output: column1, .. , column54
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=179817 read=115290
  ->  Sort  (cost=343618.94..344148.91 rows=211987 width=2549)
(actual time=7258.314..7476.733 rows=172010 loops=3)
        Output: column1, .. , column54
        Sort Key: logtable.timestampcol DESC
        Sort Method: quicksort  Memory: 64730kB
        Worker 0:  Sort Method: quicksort  Memory: 55742kB
        Worker 1:  Sort Method: quicksort  Memory: 55565kB
        Buffers: shared hit=179817 read=115290
        Worker 0: actual time=7231.774..7458.703 rows=161723
loops=1
          Buffers: shared hit=55925 read=36265
        Worker 1: actual time=7217.856..7425.754 rows=161990
loops=1
          Buffers: shared hit=56197 read=36242
        ->  Parallel Bitmap Heap Scan on schema.logtable
 (cost=5586.50..324864.86 rows=211987 width=2549) (actual
time=1073.266..6805.850 rows=172010 loops=3)
              Output: column1, .. , column54
              Recheck Cond: ((logtable.entrytype = 4000) OR
(logtable.entrytype = 4001) OR (logtable.entrytype = 4002))
              Filter: (logtable.archivestatus <= 1)
              Heap Blocks: exact=109146
              Buffers: shared hit=179803 read=115290
              Worker 0: actual time=1049.875..6809.231
rows=161723 loops=1
                Buffers: shared hit=55918 read=36265
              Worker 1: actual time=1035.156..6788.037
rows=161990 loops=1
                Buffers: shared hit=56190 read=36242
              ->  BitmapOr  (cost=5586.50..5586.50 rows=514483
width=0) (actual time=945.179..945.179 rows=0 loops=1)
                    Buffers: shared hit=3 read=1329
                    ->  Bitmap Index Scan on idx_entrytype
 (cost=0.00..738.13 rows=72893 width=0) (actual
time=147.915..147.916 rows=65970 loops=1)
                          Index Cond: (logtable.entrytype = 4000)
Buffers: shared hit=1 read=171
                    ->  Bitmap Index Scan on idx_entrytype
 (cost=0.00..2326.17 rows=229965 width=0) (actual
time=473.450..473.451 rows=225040 loops=1)
                          Index Cond: (logtable.entrytype = 4001)
Buffers: shared hit=1 read=579
                    ->  Bitmap Index Scan on idx_entrytype
 (cost=0.00..2140.61 rows=211624 width=0) (actual
time=323.801..323.802 rows=225021 loops=1)
                          Index Cond: (logtable.entrytype = 4002)
Buffers: shared hit=1 read=579
Settings: random_page_cost = '1', search_path = '"$user",
schema, public', temp_buffers = '80MB', work_mem = '1GB'
Planning Time: 0.810 ms
Execution Time: 9647.406 ms


seemingly faster.
After doing a few selects, I reran ANALYZE:
Now it's even faster, probably due to cache and other mechanisms.

Gather Merge  (cost=342639.19..391676.44 rows=420290
width=2542) (actual time=2944.803..4534.725 rows=516035 loops=1)
  Output: column1, .. , column54
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=147334 read=147776
  ->  Sort  (cost=341639.16..342164.53 rows=210145 width=2542)
(actual time=2827.256..3013.960 rows=172012 loops=3)
        Output: column1, .. , column54
        Sort Key: logtable.timestampcol DESC
        Sort Method: quicksort  Memory: 71565kB
        Worker 0:  Sort Method: quicksort  Memory: 52916kB
        Worker 1:  Sort Method: quicksort  Memory: 51556kB
        Buffers: shared hit=147334 read=147776
        Worker 0: actual time=2771.975..2948.928 rows=153292
loops=1
          Buffers: shared hit=43227 read=43808
        Worker 1: actual time=2767.752..2938.688 rows=148424
loops=1
          Buffers: shared hit=42246 read=42002
        ->  Parallel Bitmap Heap Scan on schema.logtable
 (cost=5537.95..323061.27 rows=210145 width=2542

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

2021-05-06 Thread Alexey M Boltenkov


On 05/06/21 23:02, Alexey M Boltenkov wrote:

On 05/06/21 22:58, Alexey M Boltenkov wrote:
Have you try of excluding not null from index? Can you give 
dispersion of archivestatus?



06.05.2021, 21:59, "Semen Yefimenko" :

Yes, rewriting the query with an IN clause was also my first
approach, but I didn't help much.
The Query plan did change a little bit but the performance was
not impacted.

CREATE INDEX idx_arcstatus_le1 ON schema.logtable (
archivestatus ) where (archivestatus <= 1)
ANALYZE  schema.logtable


This resulted in this query plan:

Gather Merge  (cost=344618.96..394086.05 rows=423974
width=2549) (actual time=7327.777..9142.358 rows=516031 loops=1)
  Output: column1, .. , column54
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=179817 read=115290
  ->  Sort  (cost=343618.94..344148.91 rows=211987
width=2549) (actual time=7258.314..7476.733 rows=172010 loops=3)
        Output: column1, .. , column54
        Sort Key: logtable.timestampcol DESC
        Sort Method: quicksort  Memory: 64730kB
        Worker 0:  Sort Method: quicksort  Memory: 55742kB
        Worker 1:  Sort Method: quicksort  Memory: 55565kB
        Buffers: shared hit=179817 read=115290
        Worker 0: actual time=7231.774..7458.703 rows=161723
loops=1
          Buffers: shared hit=55925 read=36265
        Worker 1: actual time=7217.856..7425.754 rows=161990
loops=1
          Buffers: shared hit=56197 read=36242
        ->  Parallel Bitmap Heap Scan on schema.logtable
 (cost=5586.50..324864.86 rows=211987 width=2549) (actual
time=1073.266..6805.850 rows=172010 loops=3)
              Output: column1, .. , column54
              Recheck Cond: ((logtable.entrytype = 4000) OR
(logtable.entrytype = 4001) OR (logtable.entrytype = 4002))
              Filter: (logtable.archivestatus <= 1)
              Heap Blocks: exact=109146
              Buffers: shared hit=179803 read=115290
              Worker 0: actual time=1049.875..6809.231
rows=161723 loops=1
                Buffers: shared hit=55918 read=36265
              Worker 1: actual time=1035.156..6788.037
rows=161990 loops=1
                Buffers: shared hit=56190 read=36242
              ->  BitmapOr  (cost=5586.50..5586.50
rows=514483 width=0) (actual time=945.179..945.179 rows=0
loops=1)
                    Buffers: shared hit=3 read=1329
                    ->  Bitmap Index Scan on idx_entrytype
 (cost=0.00..738.13 rows=72893 width=0) (actual
time=147.915..147.916 rows=65970 loops=1)
Index Cond: (logtable.entrytype = 4000)
Buffers: shared hit=1 read=171
                    ->  Bitmap Index Scan on idx_entrytype
 (cost=0.00..2326.17 rows=229965 width=0) (actual
time=473.450..473.451 rows=225040 loops=1)
Index Cond: (logtable.entrytype = 4001)
Buffers: shared hit=1 read=579
                    ->  Bitmap Index Scan on idx_entrytype
 (cost=0.00..2140.61 rows=211624 width=0) (actual
time=323.801..323.802 rows=225021 loops=1)
Index Cond: (logtable.entrytype = 4002)
Buffers: shared hit=1 read=579
Settings: random_page_cost = '1', search_path = '"$user",
schema, public', temp_buffers = '80MB', work_mem = '1GB'
Planning Time: 0.810 ms
Execution Time: 9647.406 ms


seemingly faster.
After doing a few selects, I reran ANALYZE:
Now it's even faster, probably due to cache and other mechanisms.

Gather Merge  (cost=342639.19..391676.44 rows=420290
width=2542) (actual time=2944.803..4534.725 rows=516035 loops=1)
  Output: column1, .. , column54
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=147334 read=147776
  ->  Sort  (cost=341639.16..342164.53 rows=210145
width=2542) (actual time=2827.256..3013.960 rows=172012 loops=3)
        Output: column1, .. , column54
        Sort Key: logtable.timestampcol DESC
        Sort Method: quicksort  Memory: 71565kB
        Worker 0:  Sort Method: quicksort  Memory: 52916kB
        Worker 1:  Sort Method: quicksort  Memory: 51556kB
        Buffers: shared hit=147334 read=147776
        Worker 0: actual time=2771.975..2948.928 rows=153292
loops=1
          Buffers: shared hit=43227 read=43808
        Worker 1: actual time=2767.752..2938.688 rows=148424
loops=1
          Buffers: shared hit=42246 read=42002
        ->  Parallel Bitmap Heap Scan on schema.logtable
 (cost=5537.95..323061.27 rows=210145 width=2542) (actual
time=

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

2021-05-06 Thread Imre Samu

*> Postgres Version : *PostgreSQL 12.2,
>  ... ON ... USING btree

IMHO:
The next minor (bugix&security) release is near ( expected ~ May 13th, 2021
)   https://www.postgresql.org/developer/roadmap/
so you can update your PostgreSQL to 12.7  ( + full Reindexing
recommended ! )

You can find a lot of B-tree index-related fixes.
https://www.postgresql.org/docs/12/release-12-3.html  Release date:
2020-05-14
 - Fix possible undercounting of deleted B-tree index pages in VACUUM
VERBOSE output
-  Fix wrong bookkeeping for oldest deleted page in a B-tree index
-  Ensure INCLUDE'd columns are always removed from B-tree pivot tuples
https://www.postgresql.org/docs/12/release-12-4.html
  - Avoid repeated marking of dead btree index entries as dead
https://www.postgresql.org/docs/12/release-12-5.html
  - Fix failure of parallel B-tree index scans when the index condition is
unsatisfiable
https://www.postgresql.org/docs/12/release-12-6.html Release date:
2021-02-11


> COLLATE pg_catalog."default"

You can test the "C" Collation  in some columns  (keys ? ) ;  in theory, it
should be faster :
"The drawback of using locales other than C or POSIX in PostgreSQL is its
performance impact. It slows character handling and prevents ordinary
indexes from being used by LIKE. For this reason use locales only if you
actually need them."
https://www.postgresql.org/docs/12/locale.html
https://www.postgresql.org/message-id/flat/CAF6DVKNU0vb4ZeQQ-%3Dagg69QJU3wdjPnMYYrPYY7CKc6iOU7eQ%40mail.gmail.com

Best,
 Imre


Semen Yefimenko  ezt írta (időpont: 2021. máj.
6., Cs, 16:38):

> 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.
>
> *Postgres Version : *PostgreSQL 12.2, compiled by Visual C++ build 1914,
> 64-bit
> No notable errors in the Server log and the Postgres Server itself.
>
> The table structure :
>
> CREATE TABLE logtable
> (
> key character varying(20) COLLATE pg_catalog."default" NOT NULL,
> id integer,
> column3 integer,
> column4 integer,
> column5 integer,
> column6 integer,
> column7 integer,
> column8 integer,
> column9 character varying(128) COLLATE pg_catalog."default",
> column10   character varying(2048) COLLATE pg_catalog."default",
> column11   character varying(2048) COLLATE pg_catalog."default",
> column12   character varying(2048) COLLATE pg_catalog."default",
> column13   character varying(2048) COLLATE pg_catalog."default",
> column14   character varying(2048) COLLATE pg_catalog."default",
> column15   character varying(2048) COLLATE pg_catalog."default",
> column16   character varying(2048) COLLATE pg_catalog."default",
> column17   character varying(2048) COLLATE pg_catalog."default",
> column18   character varying(2048) COLLATE pg_catalog."default",
> column19   character varying(2048) COLLATE pg_catalog."default",
> column21 character varying(256) COLLATE pg_catalog."default",
> column22 character varying(256) COLLATE pg_catalog."default",
> column23 character varying(256) COLLATE pg_catalog."default",
> column24 character varying(256) COLLATE pg_catalog."default",
> column25 character varying(256) COLLATE pg_catalog."default",
> column26 character varying(256) COLLATE pg_catalog."default",
> column27 character varying(256) COLLATE pg_catalog."default",
> column28 character varying(256) COLLATE pg_catalog."default",
> column29 character varying(256) COLLATE pg_catalog."default",
> column30 character varying(256) COLLATE pg_catalog."default",
> column31 character varying(256) COLLATE pg_catalog."default",
> column32 character varying(256) COLLATE pg_catalog."default",
> column33 character varying(256) COLLATE pg_catalog."default",
> column34 character varying(256) COLLATE pg_catalog."default",
> column35 character varying(256) COLLATE pg_catalog."default",
> entrytype integer,
> column37 bigint,
> column38 bigint,
> column39 bigint,
> column40 bigint,
> column41 bigint,
> column42 bigint,
> column43 bigint,
> column44 bigint,
> column45 bigint,
> column46 bigint,
> column47 character varying(128) COLLATE pg_catalog."default",
> timestampcol timestamp without time zone,
> column49 timestamp without time zone,
> column50 timestamp without time zone,
> column51 timestamp without time zone,
> column52 timestamp without time zone,
> archivestatus integer,
> column54 integer,
> column55 character varying(20) COLLATE pg_catalog."default",
> CONSTRAINT pkey PRIMARY KEY (key)
> USING INDEX TABLESPACE tablespace
> )
>
> TABLESPACE tablespace;
>
> ALTER TABLE schema.logtable
> O

Very slow Query compared to Oracle / SQL - Server

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

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

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

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

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

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

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

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

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

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

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

12 matches

Site Navigation

Mail list logo

Footer information