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

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

 



I've done some testing on different versions of postgres.
Unfortunately after the weekend, the problem vanished. 
The systems are running as usual and the query finishes in 500 MS.
It must have been an issue with the VMs or the DISKs.
Either way, thank you for your support. 

Here are btw. some testing results. 

----------------------------------------------------Linux PG_13.2 (docker instance)
Gather  (cost=1000.00..573832.18 rows=486255 width=2567) (actual time=232.444..23682.816 rows=516517 loops=1)
  Output: column1, .. , column54
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=15883 read=390758
  ->  Parallel Seq Scan on schema.logtable  (cost=0.00..524206.67 rows=202606 width=2567) (actual time=256.462..23522.715 rows=172172 loops=3)
        Output: column1, .. , column54
        Filter: ((logtable.archivestatus <= 1) AND ((logtable.entrytype = 4000) OR (logtable.entrytype = 4001) OR (logtable.entrytype = 4002)))
        Rows Removed by Filter: 4533459
        Buffers: shared hit=15883 read=390758
        Worker 0:  actual time=266.613..23529.215 rows=171917 loops=1
          JIT:
            Functions: 2
            Options: Inlining true, Optimization true, Expressions true, Deforming true
            Timing: Generation 0.805 ms, Inlining 52.127 ms, Optimization 150.748 ms, Emission 63.482 ms, Total 267.162 ms
          Buffers: shared hit=5354 read=130007
        Worker 1:  actual time=270.921..23527.953 rows=172273 loops=1
          JIT:
            Functions: 2
            Options: Inlining true, Optimization true, Expressions true, Deforming true
            Timing: Generation 1.217 ms, Inlining 49.556 ms, Optimization 154.765 ms, Emission 65.153 ms, Total 270.690 ms
          Buffers: shared hit=5162 read=130108
Planning Time: 0.356 ms
JIT:
  Functions: 6
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 3.578 ms, Inlining 106.136 ms, Optimization 443.580 ms, Emission 217.728 ms, Total 771.021 ms
Execution Time: 23736.150 ms
-----------Query Takes 245 MS
-----------IndexSize: average 80 MB
----------------------------------------------------Windows PG_12.6 (local instance)
Gather  (cost=1000.00..575262.60 rows=499935 width=2526) (actual time=2.155..2555.388 rows=516517 loops=1)
  Output: column1, .. , column54
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=128 read=406517
  ->  Parallel Seq Scan on schema.logtable  (cost=0.00..524269.10 rows=208306 width=2526) (actual time=0.651..2469.220 rows=172172 loops=3)
        Output: column1, .. , column54
        Filter: ((logtable.archivestatus <= 1) AND ((logtable.entrytype = 4000) OR (logtable.entrytype = 4001) OR (logtable.entrytype = 4002)))
        Rows Removed by Filter: 4533459
        Buffers: shared hit=128 read=406517
        Worker 0: actual time=0.637..2478.110 rows=172580 loops=1
          Buffers: shared hit=41 read=135683
        Worker 1: actual time=0.084..2474.863 rows=173408 loops=1
          Buffers: shared hit=42 read=135837
Planning Time: 0.201 ms
Execution Time: 2572.065 ms
-----------Query Takes 18 MS
-----------IndexSize: average 300 MB
----------------------------------------------------Windows PG_13.2 (local instance)
Gather  (cost=1000.00..575680.37 rows=503383 width=2531) (actual time=1.045..2586.700 rows=516517 loops=1)
  Output: column1, .. , column54
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=8620 read=398025
  ->  Parallel Seq Scan on schema.logtable  (cost=0.00..524342.07 rows=209743 width=2531) (actual time=0.346..2485.163 rows=172172 loops=3)
        Output: column1, .. , column54
        Filter: ((logtable.archivestatus <= 1) AND ((logtable.entrytype = 4000) OR (logtable.entrytype = 4001) OR (logtable.entrytype = 4002)))
        Rows Removed by Filter: 4533459
        Buffers: shared hit=8620 read=398025
        Worker 0: actual time=0.155..2487.411 rows=174277 loops=1
          Buffers: shared hit=2954 read=133173
        Worker 1: actual time=0.746..2492.533 rows=173762 loops=1
          Buffers: shared hit=2813 read=133935
Planning Time: 0.154 ms
Execution Time: 2604.983 ms
-----------Query Takes 18 MS
-----------IndexSize: average 80 MB
----------------------------------------------------Windows PG_12.6 (remote instance)
Bitmap Heap Scan on schema.logtable  (cost=10326.36..449509.96 rows=530847 width=2540) (actual time=406.235..6770.263 rows=516517 loops=1)
  Output: column1, .. , column54
  Recheck Cond: ((logtable.entrytype = 4000) OR (logtable.entrytype = 4001) OR (logtable.entrytype = 4002))
  Filter: (logtable.archivestatus <= 1)
  Heap Blocks: exact=294098
  Buffers: shared hit=3632 read=291886
  ->  BitmapOr  (cost=10326.36..10326.36 rows=536922 width=0) (actual time=212.117..212.124 rows=0 loops=1)
        Buffers: shared hit=1420
        ->  Bitmap Index Scan on idx_entrytype  (cost=0.00..1196.37 rows=64525 width=0) (actual time=30.677..30.678 rows=65970 loops=1)
              Index Cond: (logtable.entrytype = 4000)
              Buffers: shared hit=183
        ->  Bitmap Index Scan on idx_entrytype  (cost=0.00..4605.07 rows=249151 width=0) (actual time=110.538..110.539 rows=225283 loops=1)
              Index Cond: (logtable.entrytype = 4001)
              Buffers: shared hit=619
        ->  Bitmap Index Scan on idx_entrytype  (cost=0.00..4126.79 rows=223247 width=0) (actual time=70.887..70.888 rows=225264 loops=1)
              Index Cond: (logtable.entrytype = 4002)
              Buffers: shared hit=618
Settings: temp_buffers = '80MB', work_mem = '1GB'
Planning Time: 0.409 ms
Execution Time: 7259.515 ms
-----------Query Takes 570 MS
-----------IndexSize: average 300 MB




Am Sa., 8. Mai 2021 um 14:06 Uhr schrieb Semen Yefimenko <semen.yefimenko@xxxxxxxxx>:
Unless I'm overlooking something obvious one result has 500 000 rows the other 7 000.
You are right, it wasn't. I have 2 datasets, one containing 12 mil entries and the other 14 mil entries. I accidentally used the one with 12 mil entries in that table which actually only contains 7000~ entries for that sql query.
For now I have tested the 12.6 Postgres with default values and it finished in 12 seconds. I'll do some thorough testing and let you know once I finish, sorry for the confusion. 

Am Sa., 8. Mai 2021 um 00:17 Uhr schrieb didier <did447@xxxxxxxxx>:
Are you sure you're using the same data det ?

Unless I'm overlooking something obvious one result has 500 000 rows the other 7 000.




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

  Powered by Linux