Thread: Windows performance

DISCLAIMER:
Acest mesaj de posta electronica si documentele aferente sunt confidentiale. Este interzisa distribuirea, dezvaluirea sau orice alt mod de utilizare a lor. Daca nu sunteti destinatarul acestui mesaj, este interzis sa actionati in baza acestor informatii. Citirea, copierea, distribuirea, dezvaluirea sau utilizarea in alt mod a informatiei continute in acest mesaj constituie o incalcare a legii. Daca ati primit mesajul din greseala, va rugam sa il distrugeti, anuntand expeditorul de eroarea comisa. Intrucat nu poate fi garantat faptul ca posta electronica este un mod sigur si lipsit de erori de transmitere a informatiilor, este responsabilitatea dvs. sa va asigurati ca mesajul (inclusiv documentele alaturate lui) este validat si autorizat spre a fi utilizat in mediul dvs.

On 02/12/2016 07:37 AM, Sterpu Victor wrote:
> Hello
> Why is Postgres so slow on Windows compared to linux?
> Can I do something to match the performance?
> I have 2 servers:
> - one is Windows 8, CPU XEON, 8 CORES,  32G of RAM - my test query runs
> in 17 seconds
> - the other is Linux CPU XEON, 4 cores, 4 Gh of RAM - my test query runs
> in 2 seconds

Assuming the version of Postgres is the same on both machine.
In any case what are the versions on the machines?

> I run the query on the same database.

What is the query and what is the output of EXPLAIN ANALYZE?

> CPU is not used at max on the servers, RAM is fine.
> Is there a problem with Windows?My gues is that Windows is not using

I will pass on this.

> the hardware resources as it should be.
> Can I do something to fix this?

Are the settings in the postgresql.conf files the same?




> Thank you.
>

--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/02/16 15:45, Adrian Klaver wrote:
> On 02/12/2016 07:37 AM, Sterpu Victor wrote:
>> Hello
>> Why is Postgres so slow on Windows compared to linux?
>> Can I do something to match the performance?
>> I have 2 servers:
>> - one is Windows 8, CPU XEON, 8 CORES,  32G of RAM - my test query runs
>> in 17 seconds
>> - the other is Linux CPU XEON, 4 cores, 4 Gh of RAM - my test query runs
>> in 2 seconds
> Assuming the version of Postgres is the same on both machine.
> In any case what are the versions on the machines?
>
>> I run the query on the same database.
> What is the query and what is the output of EXPLAIN ANALYZE?
>
>> CPU is not used at max on the servers, RAM is fine.

But that doesn't mean the servers aren't busy. To really compare, wait
until your servers are actually quiescent - ideally no other users at
all. Then assuming that Adrian's questions about configuration below
don't yield any differences *and* your databases are actually identical
on both machines you can really do a comparative test. Remember that
your first run will load the data into cache on the respective machine
so subsequent runs will be less dependent on disk sub-system speed.

>> Is there a problem with Windows?My gues is that Windows is not using
> I will pass on this.
>
>> the hardware resources as it should be.
>> Can I do something to fix this?
> Are the settings in the postgresql.conf files the same?
>
>
>
>
>> Thank you.
>>

There are some differences that I haven't mentioned.
Postgres on Linux is PostgreSQL 9.1.4 64 bit
Postgres on Windows is PostgreSQL 9.5.0, compiled by Visual C++ build
1800, 64-bit

The query is very big but I pasted it at the end of the mail with the
EXPLAIN ANALYZE.
I runned the queries many times on every machine, the cache is not
involved.

postgresql.conf are defaults, I haven't compared them yet but I will.


"Limit  (cost=163011.25..163011.63 rows=1 width=3640) (actual
time=2811.693..2812.109 rows=20 loops=1)"
"  ->  WindowAgg  (cost=163011.25..163011.63 rows=1 width=3640) (actual
time=2811.691..2812.103 rows=20 loops=1)"
"        ->  Group  (cost=163011.25..163011.60 rows=1 width=3640)
(actual time=2711.668..2711.823 rows=20 loops=1)"
"              ->  Sort  (cost=163011.25..163011.26 rows=1 width=3640)
(actual time=2711.662..2711.685 rows=21 loops=1)"
"                    Sort Key: j1031101.validfrom, j1033386.name,
j1033387.name, j1033359.validto, j1031076.name, j1031074.id,
j1031074.siui_appid, j1031074.data_adeverinta, j1031074.is_paliativ,
j1031074.cardno, j1031074.cardno_externare, j1031074.sign_date,
j1031074.sign_date_externare, j1031074.unsigned_string,
j1031074.unsigned_string_externare, j1031074.signhash,
j1031074.signhash_externare, j1031074.signature,
j1031074.signature_externare, j1031074.send_xml,
j1031074.send_xml_externare, j1031074.received_xml,
j1031074.received_xml_externare, j1031074.error,
j1031074.error_externare, j1031074.validat, j1031074.validat_externare,
j1031074.online, j1031074.online_externare,
j1031074.serie_bilet_internare, j1031074.nr_bilet_internare,
j1031074.idpatient, j1031075.cnp, j1031075.name, j1031075.surname,
j1031074.nrfo, j1031074.greutate_nastere, j1031078.value,
j1031074.idensuredstatustype, j1031079.value, j1031074.idensuredstatus,
j1031080.code, j1031074.id_org_unit, j1031081.code,
j1031074.id_categorie_asigurat, j1031082.name,
j1031074.id_focg_tip_internare, j1031083.name,
j1031074.id_focg_criteriu_internare, j1031084.stencil_no, j1031084.id,
j1031089.value, j1031074.id_education_level, j1031074.greutate,
j1031090.nume, j1031074.id_focg_situatii_speciale, j1031091.nume,
j1031091.id, j1031092.nume, j1031074.id_formulare_europene,
j1031074.id_cnp_mama, j1031094.cnp, j1031093.nrfo, j1031074.id_focg,
j1031074.nr_card_euro, j1031074.nr_pasaport, j1031074.nr_card_national,
j1031088.id, j1031088.name, j1031074.export_drg, j1031074.drgcaseid,
j1031074.export_ecosoft, j1031074.mesaj_drg, j1031074.uid,
j1031074.mesaj_ecosoft, j1031074.id_address_domiciliu,
j1031074.id_address_resedinta, j1031095.id, j1031095.denumire,
j1031096.id, j1031096.code, j1031097.id, j1031097.name, j1031098.id,
j1031098.description, j1031099.id, j1031099.name, j1031100.id,
j1031100.code, j1031074.scrisoare_medicala_parafa,
j1031074.scrisoare_medicala_contract,
j1031074.scrisoare_medicala_tip_contract, j1031074.export_siui,
j1031074.mesaj_siui, j1031087.id, j1031087.stencil_no,
j1031074.diagnostic_trimitere_text, j1031074.greutate_externare,
j1031074.data_decesului, j1031736.id, j1031736.descriere, j1031737.id,
j1031737.descriere, j1033295.id, j1033295.stencil_no, j1033299.id,
j1033299.description, j1031074.text_ore_ventilatie,
j1031074.drg_cod_grupa, j1031074.drg_relative_value,
j1031074.data_2500g, j1031074.prematur_gr_i, j1033304.id,
j1033304.description, j1033358.id, j1033358.name,
j1031074.reinternat_transfer, j1031074.aviz_comisie,
j1031074.criteriu_urgenta1, j1031074.criteriu_urgenta2,
j1031074.criteriu_urgenta3, j1031074.criteriu_urgenta4,
j1031074.criteriu_urgenta5, j1031074.criteriu_urgenta6,
j1031074.criteriu_urgenta7, j1031074.criteriu_urgenta8,
j1031074.criteriu_urgenta9a, j1031074.criteriu_urgenta9b,
j1031074.criteriu_urgenta10, j1031074.criteriu_urgenta11,
j1031074.criteriu_urgenta12, j1031074.criteriu_urgenta13"
"                    Sort Method: external merge  Disk: 3192kB"
"                    ->  Nested Loop  (cost=87.71..163011.24 rows=1
width=3640) (actual time=204.650..2579.588 rows=3075 loops=1)"
"                          ->  Nested Loop Left Join
(cost=87.71..163002.94 rows=1 width=3640) (actual time=204.646..2573.701
rows=2674 loops=1)"
"                                Filter: (j1033360.id IS NULL)"
"                                ->  Nested Loop Left Join
(cost=87.71..162994.64 rows=1 width=3648) (actual time=204.642..2568.543
rows=2736 loops=1)"
"                                      ->  Nested Loop Left Join
(cost=87.71..162994.36 rows=1 width=3631) (actual time=204.636..2563.826
rows=2736 loops=1)"
"                                            Filter:
(((date(j1031101.validfrom) >= '2016-02-01'::date) AND
(date(j1031101.validfrom) <= '2016-02-29'::date)) OR ((j1033359.validto
IS NOT NULL) AND (date(j1033359.validto) >= '2016-02-01'::date) AND
(date(j1033359.validto) <= '2016-02-29'::date)) OR ((j1033359.validto IS
NULL) AND (date(j1031101.validfrom) <= '2016-02-01'::date)))"
"                                            ->  Nested Loop Left Join
(cost=87.71..162986.04 rows=1 width=3611) (actual time=4.294..2392.667
rows=76387 loops=1)"
"                                                  ->  Nested Loop Left
Join  (cost=87.71..162985.76 rows=1 width=3601) (actual
time=4.293..2339.441 rows=76387 loops=1)"
"                                                        ->  Nested Loop
Left Join  (cost=87.71..162985.48 rows=1 width=3594) (actual
time=4.293..2267.916 rows=76387 loops=1)"
"                                                              ->
Nested Loop Left Join  (cost=87.71..162985.20 rows=1 width=3487) (actual
time=4.291..2188.694 rows=76387 loops=1)"
"                                                                    ->
Nested Loop Left Join  (cost=87.71..162984.92 rows=1 width=3480) (actual
time=4.291..2134.138 rows=76387 loops=1)"
"
   ->  Nested Loop Left Join  (cost=87.71..162984.64 rows=1 width=3471)
(actual time=4.290..2059.880 rows=76387 loops=1)"
"
         ->  Nested Loop Left Join  (cost=87.71..162984.36 rows=1
width=3456) (actual time=4.288..1984.775 rows=76387 loops=1)"
"
               Filter: (j1031102.id IS NULL)"
"
               ->  Hash Left Join  (cost=87.71..131248.17 rows=86595
width=3456) (actual time=4.281..1824.045 rows=86597 loops=1)"
"
                     Hash Cond: (j1031101.iddepartment = j1033386.id)"
"
                     ->  Nested Loop Left Join  (cost=84.57..130054.34
rows=86595 width=3439) (actual time=4.234..1774.459 rows=86597 loops=1)"
"
                           ->  Hash Left Join  (cost=84.57..101788.93
rows=76386 width=3427) (actual time=0.863..1192.825 rows=76386 loops=1)"
"
                                 Hash Cond:
(j1031074.id_exceptie_bilet_internare = j1031100.id)"
"
                                 ->  Hash Left Join
(cost=83.43..101501.29 rows=76386 width=3425) (actual
time=0.856..1160.491 rows=76386 loops=1)"
"
                                       Hash Cond:
(j1031074.id_focg_contract_modes = j1031098.id)"
"
                                       ->  Hash Left Join
(cost=82.36..100884.02 rows=76386 width=3400) (actual
time=0.848..1126.773 rows=76386 loops=1)"
"
                                             Hash Cond:
(j1031074.id_icd10 = j1031097.id)"
"
                                             ->  Hash Left Join
(cost=39.79..100358.82 rows=76386 width=3361) (actual
time=0.404..1090.819 rows=76386 loops=1)"
"
                                                   Hash Cond:
(j1031074.id_focg_criterii_urgenta = j1031099.id)"
"
                                                   ->  Hash Left Join
(cost=38.45..99866.34 rows=76386 width=3328) (actual
time=0.393..1058.408 rows=76386 loops=1)"
"
                                                         Hash Cond:
(j1031074.id_diseasecategory = j1031096.id)"
"
                                                         ->  Hash Left
Join  (cost=36.55..99575.04 rows=76386 width=3324) (actual
time=0.370..1029.166 rows=76386 loops=1)"
"
                                                               Hash Cond:
(j1031074.id_focg_sursa_internare = j1031095.id)"
"
                                                               ->  Hash
Left Join  (cost=35.46..99004.22 rows=76386 width=3301) (actual
time=0.363..994.912 rows=76386 loops=1)"
"
                                                                     Hash
Cond: (j1031074.id_focg_internat_prin = j1031091.id)"
"
                                                                     ->
Hash Left Join  (cost=34.37..98474.03 rows=76386 width=3278) (actual
time=0.353..962.776 rows=76386 loops=1)"
"

  Hash Cond: (j1031074.id_focg_situatii_speciale = j1031090.id)"
"

  ->  Nested Loop Left Join  (cost=33.28..98184.33 rows=76386 width=3252)
(actual time=0.346..932.976 rows=76386 loops=1)"
"

        ->  Hash Left Join  (cost=33.28..59683.55 rows=76386 width=3248)
(actual time=0.345..747.876 rows=76386 loops=1)"
"

              Hash Cond: (j1031074.id_education_level = j1031089.id)"
"

              ->  Nested Loop Left Join  (cost=32.06..59247.39 rows=76386
width=3231) (actual time=0.335..716.140 rows=76386 loops=1)"
"

                    ->  Hash Left Join  (cost=32.06..31913.00 rows=76386
width=3217) (actual time=0.332..659.011 rows=76386 loops=1)"
"

                          Hash Cond: (j1031074.id_formulare_europene =
j1031092.id)"
"

                          ->  Hash Left Join  (cost=30.92..31625.41
rows=76386 width=3212) (actual time=0.318..629.432 rows=76386 loops=1)"
"

                                Hash Cond: (j1031074.idproffesion =
j1031088.id)"
"

                                ->  Hash Left Join  (cost=29.74..31045.22
rows=76386 width=3200) (actual time=0.309..595.003 rows=76386 loops=1)"
"

                                      Hash Cond:
(j1031074.idphysician_madeby = j1031087.id)"
"

                                      ->  Hash Left Join
(cost=19.71..30447.27 rows=76386 width=3193) (actual time=0.217..561.055
rows=76386 loops=1)"
"

                                            Hash Cond:
(j1031074.idphysiciancurrent = j1031084.id)"
"

                                            ->  Hash Left Join
(cost=9.68..29799.16 rows=76386 width=3186) (actual time=0.110..527.200
rows=76386 loops=1)"
"

                                                  Hash Cond:
(j1031074.id_focg_criteriu_internare = j1031083.id)"
"

                                                  ->  Hash Left Join
(cost=8.36..29112.44 rows=76386 width=3095) (actual time=0.097..493.422
rows=76386 loops=1)"
"

                                                        Hash Cond:
(j1031074.id_focg_tip_internare = j1031082.id)"
"

                                                        ->  Hash Left
Join  (cost=7.07..28423.78 rows=76386 width=3080) (actual
time=0.086..460.741 rows=76386 loops=1)"
"

                                                              Hash Cond:
(j1031074.id_categorie_asigurat = j1031081.id)"
"

                                                              ->  Hash
Left Join  (cost=5.39..27878.49 rows=76386 width=3076) (actual
time=0.073..429.960 rows=76386 loops=1)"
"

                                                                    Hash
Cond: (j1031074.id_org_unit = j1031080.id)"
"

                                                                    ->
Hash Left Join  (cost=3.29..27125.61 rows=76386 width=3070) (actual
time=0.053..397.925 rows=76386 loops=1)"
"


Hash Cond: (j1031074.idensuredstatus = j1031079.id)"
"


->  Hash Left Join  (cost=2.18..26388.14 rows=76386 width=3051) (actual
time=0.044..365.856 rows=76386 loops=1)"
"


       Hash Cond: (j1031074.idensuredstatustype = j1031078.id)"
"


       ->  Hash Left Join  (cost=1.09..25584.69 rows=76386 width=3039)
(actual time=0.035..331.999 rows=76386 loops=1)"
"


             Hash Cond: (j1031075.idsex = j1031076.id)"
"


             ->  Merge Left Join  (cost=0.00..24533.37 rows=76386
width=3034) (actual time=0.019..292.456 rows=76386 loops=1)"
"


                   Merge Cond: (j1031074.idpatient = j1031075.id)"
"


                   ->  Index Scan using fosz_simple_index33 on focg
j1031074  (cost=0.00..17598.45 rows=76386 width=3005) (actual
time=0.008..162.007 rows=76386 loops=1)"
"


                   ->  Index Scan using person_pkey on person j1031075
(cost=0.00..5779.59 rows=80203 width=37) (actual time=0.007..62.297
rows=112219 loops=1)"
"


             ->  Hash  (cost=1.04..1.04 rows=4 width=13) (actual
time=0.005..0.005 rows=4 loops=1)"
"


                   Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"


                   ->  Seq Scan on sex j1031076  (cost=0.00..1.04 rows=4
width=13) (actual time=0.002..0.002 rows=4 loops=1)"
"


       ->  Hash  (cost=1.04..1.04 rows=4 width=16) (actual
time=0.003..0.003 rows=4 loops=1)"
"


             Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"


             ->  Seq Scan on ensuredstatustype j1031078  (cost=0.00..1.04
rows=4 width=16) (actual time=0.002..0.003 rows=4 loops=1)"
"


->  Hash  (cost=1.05..1.05 rows=5 width=23) (actual time=0.003..0.003
rows=5 loops=1)"
"


       Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"


       ->  Seq Scan on ensuredstatus j1031079  (cost=0.00..1.05 rows=5
width=23) (actual time=0.002..0.002 rows=5 loops=1)"
"

                                                                    ->
Hash  (cost=1.49..1.49 rows=49 width=10) (actual time=0.015..0.015
rows=49 loops=1)"
"


Buckets: 1024  Batches: 1  Memory Usage: 3kB"
"


->  Seq Scan on org_units j1031080  (cost=0.00..1.49 rows=49 width=10)
(actual time=0.002..0.007 rows=49 loops=1)"
"

                                                              ->  Hash
(cost=1.30..1.30 rows=30 width=8) (actual time=0.009..0.009 rows=30
loops=1)"
"


Buckets: 1024  Batches: 1  Memory Usage: 2kB"
"

                                                                    ->
Seq Scan on personstate j1031081  (cost=0.00..1.30 rows=30 width=8)
(actual time=0.002..0.004 rows=30 loops=1)"
"

                                                        ->  Hash
(cost=1.13..1.13 rows=13 width=19) (actual time=0.005..0.005 rows=13
loops=1)"
"

                                                              Buckets:
1024  Batches: 1  Memory Usage: 1kB"
"

                                                              ->  Seq
Scan on focginterntype j1031082  (cost=0.00..1.13 rows=13 width=19)
(actual time=0.001..0.002 rows=13 loops=1)"
"

                                                  ->  Hash
(cost=1.14..1.14 rows=14 width=95) (actual time=0.009..0.009 rows=14
loops=1)"
"

                                                        Buckets: 1024
Batches: 1  Memory Usage: 2kB"
"

                                                        ->  Seq Scan on
focginterncrit j1031083  (cost=0.00..1.14 rows=14 width=95) (actual
time=0.001..0.003 rows=14 loops=1)"
"

                                            ->  Hash  (cost=5.57..5.57
rows=357 width=15) (actual time=0.101..0.101 rows=357 loops=1)"
"

                                                  Buckets: 1024  Batches:
1  Memory Usage: 15kB"
"

                                                  ->  Seq Scan on
physicians j1031084  (cost=0.00..5.57 rows=357 width=15) (actual
time=0.010..0.045 rows=357 loops=1)"
"

                                      ->  Hash  (cost=5.57..5.57 rows=357
width=11) (actual time=0.087..0.087 rows=357 loops=1)"
"

                                            Buckets: 1024  Batches: 1
Memory Usage: 14kB"
"

                                            ->  Seq Scan on physicians
j1031087  (cost=0.00..5.57 rows=357 width=11) (actual time=0.003..0.033
rows=357 loops=1)"
"

                                ->  Hash  (cost=1.08..1.08 rows=8
width=16) (actual time=0.004..0.004 rows=8 loops=1)"
"

                                      Buckets: 1024  Batches: 1  Memory
Usage: 1kB"
"

                                      ->  Seq Scan on proffession
j1031088  (cost=0.00..1.08 rows=8 width=16) (actual time=0.001..0.001
rows=8 loops=1)"
"

                          ->  Hash  (cost=1.06..1.06 rows=6 width=9)
(actual time=0.009..0.009 rows=6 loops=1)"
"

                                Buckets: 1024  Batches: 1  Memory Usage:
1kB"
"

                                ->  Seq Scan on formulare_europene
j1031092  (cost=0.00..1.06 rows=6 width=9) (actual time=0.005..0.006
rows=6 loops=1)"
"

                    ->  Index Scan using person_pkey on person j1031094
(cost=0.00..0.35 rows=1 width=18) (actual time=0.000..0.000 rows=0
loops=76386)"
"

                          Index Cond: (id = j1031074.id_cnp_mama)"
"

              ->  Hash  (cost=1.10..1.10 rows=10 width=21) (actual
time=0.004..0.004 rows=10 loops=1)"
"

                    Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"

                    ->  Seq Scan on educationlevel j1031089
(cost=0.00..1.10 rows=10 width=21) (actual time=0.001..0.002 rows=10
loops=1)"
"

        ->  Index Scan using fosz_pkey on focg j1031093  (cost=0.00..0.49
rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=76386)"
"

              Index Cond: (id = j1031074.id_focg)"
"

  ->  Hash  (cost=1.04..1.04 rows=4 width=30) (actual time=0.002..0.002
rows=4 loops=1)"
"

        Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"

        ->  Seq Scan on focg_situatii_speciale j1031090  (cost=0.00..1.04
rows=4 width=30) (actual time=0.001..0.002 rows=4 loops=1)"
"
                                                                     ->
Hash  (cost=1.04..1.04 rows=4 width=27) (actual time=0.002..0.002 rows=4
loops=1)"
"

  Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"

  ->  Seq Scan on focg_internat_prin j1031091  (cost=0.00..1.04 rows=4
width=27) (actual time=0.001..0.001 rows=4 loops=1)"
"
                                                               ->  Hash
(cost=1.04..1.04 rows=4 width=27) (actual time=0.002..0.002 rows=4
loops=1)"
"

Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"
                                                                     ->
Seq Scan on focg_sursa_internare j1031095  (cost=0.00..1.04 rows=4
width=27) (actual time=0.001..0.001 rows=4 loops=1)"
"
                                                         ->  Hash
(cost=1.40..1.40 rows=40 width=8) (actual time=0.017..0.017 rows=40
loops=1)"
"
                                                               Buckets:
1024  Batches: 1  Memory Usage: 2kB"
"
                                                               ->  Seq
Scan on diseasecategory j1031096  (cost=0.00..1.40 rows=40 width=8)
(actual time=0.006..0.008 rows=40 loops=1)"
"
                                                   ->  Hash
(cost=1.15..1.15 rows=15 width=37) (actual time=0.006..0.006 rows=15
loops=1)"
"
                                                         Buckets: 1024
Batches: 1  Memory Usage: 2kB"
"
                                                         ->  Seq Scan on
focg_criterii_urgenta j1031099  (cost=0.00..1.15 rows=15 width=37)
(actual time=0.001..0.003 rows=15 loops=1)"
"
                                             ->  Hash  (cost=26.70..26.70
rows=1270 width=43) (actual time=0.439..0.439 rows=1270 loops=1)"
"
                                                   Buckets: 1024
Batches: 1  Memory Usage: 95kB"
"
                                                   ->  Seq Scan on icd10
j1031097  (cost=0.00..26.70 rows=1270 width=43) (actual
time=0.003..0.184 rows=1270 loops=1)"
"
                                       ->  Hash  (cost=1.03..1.03 rows=3
width=29) (actual time=0.003..0.003 rows=3 loops=1)"
"
                                             Buckets: 1024  Batches: 1
Memory Usage: 1kB"
"
                                             ->  Seq Scan on
focg_contract_modes j1031098  (cost=0.00..1.03 rows=3 width=29) (actual
time=0.001..0.001 rows=3 loops=1)"
"
                                 ->  Hash  (cost=1.06..1.06 rows=6
width=6) (actual time=0.003..0.003 rows=6 loops=1)"
"
                                       Buckets: 1024  Batches: 1  Memory
Usage: 1kB"
"
                                       ->  Seq Scan on
exceptie_bilet_internare j1031100  (cost=0.00..1.06 rows=6 width=6)
(actual time=0.001..0.001 rows=6 loops=1)"
"
                           ->  Index Scan using focgdepartment_uni on
focgdepartment j1031101  (cost=0.00..0.36 rows=1 width=16) (actual
time=0.007..0.007 rows=1 loops=76386)"
"
                                 Index Cond: (idfocg = j1031074.id)"
"
                     ->  Hash  (cost=2.51..2.51 rows=51 width=25) (actual
time=0.024..0.024 rows=51 loops=1)"
"
                           Buckets: 1024  Batches: 1  Memory Usage: 3kB"
"
                           ->  Seq Scan on department j1033386
(cost=0.00..2.51 rows=51 width=25) (actual time=0.007..0.013 rows=51
loops=1)"
"
               ->  Index Scan using focgdepartment_uni on focgdepartment
j1031102  (cost=0.00..0.35 rows=1 width=16) (actual time=0.001..0.001
rows=0 loops=86597)"
"
                     Index Cond: ((idfocg = j1031074.id) AND (validfrom <
j1031101.validfrom))"
"
         ->  Index Scan using focg_tip_externare_pkey on
focg_tip_externare j1031736  (cost=0.00..0.27 rows=1 width=19) (actual
time=0.000..0.000 rows=0 loops=76387)"
"
               Index Cond: (id = j1031074.id_focg_tip_externare)"
"
   ->  Index Scan using focg_stare_externare_pkey on focg_stare_externare
j1031737  (cost=0.00..0.27 rows=1 width=13) (actual time=0.000..0.000
rows=0 loops=76387)"
"
         Index Cond: (id = j1031074.id_focg_stare_externare)"
"                                                                    ->
Index Scan using physicians_pkey on physicians j1033295
(cost=0.00..0.27 rows=1 width=11) (actual time=0.000..0.000 rows=0
loops=76387)"
"
   Index Cond: (id = j1031074.idphysician_surgeon)"
"                                                              ->  Index
Scan using hospital_release_statuses_pkey on hospital_release_statuses
j1033299  (cost=0.00..0.27 rows=1 width=111) (actual time=0.000..0.000
rows=0 loops=76387)"
"
Index Cond: (id = j1031074.id_hospital_release_statuses)"
"                                                        ->  Index Scan
using citizenship_pkey on citizenship j1033304  (cost=0.00..0.27 rows=1
width=11) (actual time=0.000..0.000 rows=0 loops=76387)"
"                                                              Index
Cond: (id = j1031074.id_citizenship)"
"                                                  ->  Index Scan using
drg_tip_cetatenie_pkey on drg_tip_cetatenie j1033358  (cost=0.00..0.27
rows=1 width=14) (actual time=0.000..0.000 rows=0 loops=76387)"
"                                                        Index Cond: (id
= j1031074.id_drg_tip_cetatenie)"
"                                            ->  Index Scan using
focgdepartment_uni on focgdepartment j1033359  (cost=0.00..8.29 rows=1
width=24) (actual time=0.001..0.001 rows=1 loops=76387)"
"                                                  Index Cond: (idfocg =
j1031074.id)"
"                                      ->  Index Scan using
department_pkey on department j1033387  (cost=0.00..0.27 rows=1
width=25) (actual time=0.001..0.001 rows=1 loops=2736)"
"                                            Index Cond: (id =
j1033359.iddepartment)"
"                                ->  Index Scan using focgdepartment_uni
on focgdepartment j1033360  (cost=0.00..8.29 rows=1 width=16) (actual
time=0.001..0.001 rows=0 loops=2736)"
"                                      Index Cond: ((idfocg =
j1031074.id) AND (validfrom > j1033359.validfrom))"
"                          ->  Index Scan using focgdepartment_uni on
focgdepartment j1033407  (cost=0.00..8.29 rows=1 width=4) (actual
time=0.001..0.001 rows=1 loops=2674)"
"                                Index Cond: (idfocg = j1031074.id)"
"Total runtime: 2826.195 ms"

SELECT
row_number() OVER (ORDER BY J1031101.validfrom DESC)  AS  nrcrt ,
J1033386.name  AS  sectie_internare ,
J1033387.name  AS  sectie_externare ,
TO_CHAR(J1031101.validfrom , 'YYYY-MM-DD HH24:MI')  AS  validfrom ,
TO_CHAR(J1033359.validto , 'YYYY-MM-DD HH24:MI')  AS  validto ,
CASE WHEN(CHAR_LENGTH(J1031074.signature) > 10)  THEN(1)  ELSE(0) END
AS  semnat_internare ,
CASE WHEN(CHAR_LENGTH(J1031074.signature_externare) > 10)  THEN(1)
ELSE(0) END  AS  semnat_externare ,
J1031076.name  AS  sex ,
J1031074.id  AS  id ,
J1031074.siui_appid  AS  siui_appid ,
J1031074.data_adeverinta  AS  data_adeverinta ,
J1031074.is_paliativ  AS  text_paliativ ,
J1031074.cardno  AS  cardno ,
J1031074.cardno_externare  AS  cardno_externare ,
J1031074.sign_date  AS  sign_date ,
J1031074.sign_date_externare  AS  sign_date_externare ,
J1031074.unsigned_string  AS  unsigned_string ,
J1031074.unsigned_string_externare  AS  unsigned_string_externare ,
J1031074.signhash  AS  signhash ,
J1031074.signhash_externare  AS  signhash_externare ,
J1031074.signature  AS  signature ,
J1031074.signature_externare  AS  signature_externare ,
J1031074.send_xml  AS  send_xml ,
J1031074.send_xml_externare  AS  send_xml_externare ,
J1031074.received_xml  AS  received_xml ,
J1031074.received_xml_externare  AS  received_xml_externare ,
J1031074.error  AS  error ,
J1031074.error_externare  AS  error_externare ,
J1031074.validat  AS  validat ,
J1031074.validat_externare  AS  validat_externare ,
J1031074.online  AS  online ,
J1031074.online_externare  AS  online_externare ,
J1031074.serie_bilet_internare  AS  text_serie_bilet_internare ,
J1031074.nr_bilet_internare  AS  text_numar_bilet_internare ,
J1031074.idpatient  AS  _popup_cnp_pacient ,
J1031075.cnp  AS  popup_cnp_pacient ,
J1031075.name  AS  text_nume_pacient ,
J1031075.surname  AS  text_prenume_pacient ,
J1031074.nrfo  AS  text_numar_fosz ,
J1031074.greutate_nastere  AS  text_greutate_nastere ,
J1031078.value  AS  popup_tip_asigurare ,
J1031074.idensuredstatustype  AS  _popup_tip_asigurare ,
J1031079.value  AS  popup_statut_asigurat ,
J1031074.idensuredstatus  AS  _popup_statut_asigurat ,
J1031080.code  AS  popup_cas_asigurat ,
J1031074.id_org_unit  AS  _popup_cas_asigurat ,
J1031081.code  AS  popup_categorie_asigurare ,
J1031074.id_categorie_asigurat  AS  _popup_categorie_asigurare ,
J1031082.name  AS  popup_tip_internare ,
J1031074.id_focg_tip_internare  AS  _popup_tip_internare ,
J1031083.name  AS  popup_criteriu_internare ,
J1031074.id_focg_criteriu_internare  AS  _popup_criteriu_internare ,
J1031084.stencil_no  AS  popup_medic_curant ,
J1031084.id  AS  _popup_medic_curant ,
J1031089.value  AS  popup_nivel_instruire ,
J1031074.id_education_level  AS  _popup_nivel_instruire ,
J1031074.greutate  AS  text_greutate_internare ,
J1031090.nume  AS  popup_situatii_speciale ,
J1031074.id_focg_situatii_speciale  AS  _popup_situatii_speciale ,
J1031091.nume  AS  popup_internat_prin ,
J1031091.id  AS  _popup_internat_prin ,
J1031092.nume  AS  popup_formulare_europene ,
J1031074.id_formulare_europene  AS  _popup_formulare_europene ,
J1031074.id_cnp_mama  AS  _popup_cnp_mama ,
J1031094.cnp  AS  popup_cnp_mama ,
J1031093.nrfo  AS  popup_fo_mama ,
J1031074.id_focg  AS  _popup_fo_mama ,
J1031074.nr_card_euro  AS  text_nr_card_european ,
J1031074.nr_pasaport  AS  text_nr_pasaport ,
J1031074.nr_card_national  AS  text_nr_card_national ,
J1031088.id  AS  _popup_ocupatia ,
J1031088.name  AS  popup_ocupatia ,
J1031074.export_drg  AS  export_drg ,
J1031074.drgcaseid  AS  drgcaseid ,
J1031074.export_ecosoft  AS  export_ecosoft ,
J1031074.mesaj_drg  AS  mesaj_drg ,
J1031074.uid  AS  uid ,
J1031074.mesaj_ecosoft  AS  mesaj_ecosoft ,
J1031074.id_address_domiciliu  AS  text_id_address_domiciliu ,
J1031074.id_address_domiciliu  AS  _text_id_address_domiciliu ,
J1031074.id_address_resedinta  AS  _text_id_address_resedinta ,
J1031074.id_address_resedinta  AS  text_id_address_resedinta ,
'0'  AS  marcator ,
J1031095.id  AS  _popup_sursa_internare ,
J1031095.denumire  AS  popup_sursa_internare ,
J1031096.id  AS  _popup_diseasecategory ,
J1031096.code  AS  popup_diseasecategory ,
J1031097.id  AS  _popup_diagnostic_internare_icd10 ,
J1031097.name  AS  popup_diagnostic_internare_icd10 ,
J1031098.id  AS  _popup_mod_contract ,
J1031098.description  AS  popup_mod_contract ,
J1031099.id  AS  _popup_criteriu_urgenta ,
J1031099.name  AS  popup_criteriu_urgenta ,
J1031100.id  AS  _popup_exceptie_bi ,
J1031100.code  AS  popup_exceptie_bi ,
J1031074.scrisoare_medicala_parafa  AS  text_parafa_scrisoare_medicala ,
J1031074.scrisoare_medicala_contract  AS
text_contract_scrisoare_medicala ,
J1031074.scrisoare_medicala_tip_contract  AS
text_tip_contract_scrisoare_medicala ,
J1031074.export_siui  AS  export_siui ,
J1031074.mesaj_siui  AS  mesaj_siui ,
J1031087.id  AS  _popup_intocmit ,
J1031087.stencil_no  AS  popup_intocmit ,
J1031074.diagnostic_trimitere_text  AS  text_diagnostic_trimitere ,
J1031074.greutate_externare  AS  text_greutate_externare ,
J1031074.data_decesului  AS  text_data_deces ,
J1031736.id  AS  _popup_tip_externare ,
J1031736.descriere  AS  popup_tip_externare ,
J1031737.id  AS  _popup_stare_externare ,
J1031737.descriere  AS  popup_stare_externare ,
J1033295.id  AS  _popup_chirurg ,
J1033295.stencil_no  AS  popup_chirurg ,
J1033299.id  AS  _text_caz_externare ,
J1033299.description  AS  text_caz_externare ,
J1031074.text_ore_ventilatie  AS  text_ore_ventilatie ,
J1031074.drg_cod_grupa  AS  text_drg_cod_grupa ,
J1031074.drg_relative_value  AS  text_drg_relative_value ,
J1031074.data_2500g  AS  text_data_2500g ,
J1031074.prematur_gr_i  AS  text_prematur_gr_i ,
J1033304.id  AS  _popup_cetatenie ,
J1033304.description  AS  popup_cetatenie ,
J1033358.id  AS  _popup_tip_cetatenie ,
J1033358.name  AS  popup_tip_cetatenie ,
J1031074.reinternat_transfer  AS  text_reinternat_transfer ,
J1031074.aviz_comisie  AS  text_aviz_comisie_reinternare ,
J1031074.criteriu_urgenta1  AS  criteriu_urgenta1 ,
J1031074.criteriu_urgenta2  AS  criteriu_urgenta2 ,
J1031074.criteriu_urgenta3  AS  criteriu_urgenta3 ,
J1031074.criteriu_urgenta4  AS  criteriu_urgenta4 ,
J1031074.criteriu_urgenta5  AS  criteriu_urgenta5 ,
J1031074.criteriu_urgenta6  AS  criteriu_urgenta6 ,
J1031074.criteriu_urgenta7  AS  criteriu_urgenta7 ,
J1031074.criteriu_urgenta8  AS  criteriu_urgenta8 ,
J1031074.criteriu_urgenta9a  AS  criteriu_urgenta9a ,
J1031074.criteriu_urgenta9b  AS  criteriu_urgenta9b ,
J1031074.criteriu_urgenta10  AS  criteriu_urgenta10 ,
J1031074.criteriu_urgenta11  AS  criteriu_urgenta11 ,
J1031074.criteriu_urgenta12  AS  criteriu_urgenta12 ,
J1031074.criteriu_urgenta13  AS  criteriu_urgenta13
    FROM focg AS J1031074
    LEFT JOIN person AS J1031075 ON ( J1031075.id = J1031074.idpatient)
    LEFT JOIN sex AS J1031076 ON ( J1031076.id = J1031075.idsex)
    LEFT JOIN focg_serials AS J1031077 ON ( J1031077.id =
J1031074.id_focg_serials)
    LEFT JOIN ensuredstatustype AS J1031078 ON ( J1031078.id =
J1031074.idensuredstatustype)
    LEFT JOIN ensuredstatus AS J1031079 ON ( J1031079.id =
J1031074.idensuredstatus)
    LEFT JOIN org_units AS J1031080 ON ( J1031080.id =
J1031074.id_org_unit)
    LEFT JOIN personstate AS J1031081 ON ( J1031081.id =
J1031074.id_categorie_asigurat)
    LEFT JOIN focginterntype AS J1031082 ON ( J1031082.id =
J1031074.id_focg_tip_internare)
    LEFT JOIN focginterncrit AS J1031083 ON ( J1031083.id =
J1031074.id_focg_criteriu_internare)
    LEFT JOIN physicians AS J1031084 ON ( J1031084.id =
J1031074.idphysiciancurrent)
    LEFT JOIN personnel AS J1031085 ON ( J1031085.id =
J1031084.id_personnel)
    LEFT JOIN person AS J1031086 ON ( J1031086.id = J1031085.idperson)
    LEFT JOIN physicians AS J1031087 ON ( J1031087.id =
J1031074.idphysician_madeby)
    LEFT JOIN proffession AS J1031088 ON ( J1031088.id =
J1031074.idproffesion)
    LEFT JOIN educationlevel AS J1031089 ON ( J1031089.id =
J1031074.id_education_level)
    LEFT JOIN focg_situatii_speciale AS J1031090 ON ( J1031090.id =
J1031074.id_focg_situatii_speciale)
    LEFT JOIN focg_internat_prin AS J1031091 ON ( J1031091.id =
J1031074.id_focg_internat_prin)
    LEFT JOIN formulare_europene AS J1031092 ON ( J1031092.id =
J1031074.id_formulare_europene)
    LEFT JOIN focg AS J1031093 ON ( J1031093.id = J1031074.id_focg)
    LEFT JOIN person AS J1031094 ON ( J1031094.id = J1031074.id_cnp_mama)
    LEFT JOIN focg_sursa_internare AS J1031095 ON ( J1031095.id =
J1031074.id_focg_sursa_internare)
    LEFT JOIN diseasecategory AS J1031096 ON ( J1031096.id =
J1031074.id_diseasecategory)
    LEFT JOIN icd10 AS J1031097 ON ( J1031097.id = J1031074.id_icd10)
    LEFT JOIN focg_contract_modes AS J1031098 ON ( J1031098.id =
J1031074.id_focg_contract_modes)
    LEFT JOIN focg_criterii_urgenta AS J1031099 ON ( J1031099.id =
J1031074.id_focg_criterii_urgenta)
    LEFT JOIN exceptie_bilet_internare AS J1031100 ON ( J1031100.id =
J1031074.id_exceptie_bilet_internare)
    LEFT JOIN focgdepartment AS J1031101 ON ( J1031101.idfocg =
J1031074.id)
    LEFT JOIN department AS J1033386 ON ( J1033386.id =
J1031101.iddepartment)
    LEFT JOIN focgdepartment AS J1031102 ON ( J1031102.idfocg =
J1031074.id AND  J1031102.validfrom < J1031101.validfrom)
    LEFT JOIN focg_tip_externare AS J1031736 ON ( J1031736.id =
J1031074.id_focg_tip_externare)
    LEFT JOIN focg_stare_externare AS J1031737 ON ( J1031737.id =
J1031074.id_focg_stare_externare)
    LEFT JOIN physicians AS J1033295 ON ( J1033295.id =
J1031074.idphysician_surgeon)
    LEFT JOIN hospital_release_statuses AS J1033299 ON ( J1033299.id =
J1031074.id_hospital_release_statuses)
    LEFT JOIN citizenship AS J1033304 ON ( J1033304.id =
J1031074.id_citizenship)
    LEFT JOIN drg_tip_cetatenie AS J1033358 ON ( J1033358.id =
J1031074.id_drg_tip_cetatenie)
    LEFT JOIN focgdepartment AS J1033359 ON ( J1033359.idfocg =
J1031074.id)
    LEFT JOIN department AS J1033387 ON ( J1033387.id =
J1033359.iddepartment)
    LEFT JOIN focgdepartment AS J1033360 ON ( J1033360.idfocg =
J1031074.id AND  J1033360.validfrom > J1033359.validfrom)
     JOIN focgdepartment AS J1033407 ON ( J1033407.idfocg = J1031074.id)
  WHERE   J1031102.id IS NULL   AND    J1033360.id IS NULL   AND    (
(   DATE(J1031101.validfrom)>= DATE('2016-02-01')   AND
DATE(J1031101.validfrom)<= DATE('2016-02-29 00:00:00.0')   )    OR    (
   J1033359.validto IS NOT NULL   AND   DATE(J1033359.validto)>=
DATE('2016-02-01')   AND   DATE(J1033359.validto)<= DATE('2016-02-29
00:00:00.0')   )    OR    (    J1033359.validto IS NULL   AND
DATE(J1031101.validfrom)<= DATE('2016-02-01')   )    )
  GROUP BY J1033386.name, J1033387.name, J1031101.validfrom,
J1033359.validto, J1031076.name, J1031074.id, J1031074.siui_appid,
J1031074.data_adeverinta, J1031074.is_paliativ, J1031074.cardno,
J1031074.cardno_externare, J1031074.sign_date,
J1031074.sign_date_externare, J1031074.unsigned_string,
J1031074.unsigned_string_externare, J1031074.signhash,
J1031074.signhash_externare, J1031074.signature,
J1031074.signature_externare, J1031074.send_xml,
J1031074.send_xml_externare, J1031074.received_xml,
J1031074.received_xml_externare, J1031074.error,
J1031074.error_externare, J1031074.validat, J1031074.validat_externare,
J1031074.online, J1031074.online_externare,
J1031074.serie_bilet_internare, J1031074.nr_bilet_internare,
J1031074.idpatient, J1031075.cnp, J1031075.name, J1031075.surname,
J1031074.nrfo, J1031074.greutate_nastere, J1031078.value,
J1031074.idensuredstatustype, J1031079.value, J1031074.idensuredstatus,
J1031080.code, J1031074.id_org_unit, J1031081.code,
J1031074.id_categorie_asigurat, J1031082.name,
J1031074.id_focg_tip_internare, J1031083.name,
J1031074.id_focg_criteriu_internare, J1031084.stencil_no, J1031084.id,
J1031089.value, J1031074.id_education_level, J1031074.greutate,
J1031090.nume, J1031074.id_focg_situatii_speciale, J1031091.nume,
J1031091.id, J1031092.nume, J1031074.id_formulare_europene,
J1031074.id_cnp_mama, J1031094.cnp, J1031093.nrfo, J1031074.id_focg,
J1031074.nr_card_euro, J1031074.nr_pasaport, J1031074.nr_card_national,
J1031088.id, J1031088.name, J1031074.export_drg, J1031074.drgcaseid,
J1031074.export_ecosoft, J1031074.mesaj_drg, J1031074.uid,
J1031074.mesaj_ecosoft, J1031074.id_address_domiciliu,
J1031074.id_address_domiciliu, J1031074.id_address_resedinta,
J1031074.id_address_resedinta, J1031095.id, J1031095.denumire,
J1031096.id, J1031096.code, J1031097.id, J1031097.name, J1031098.id,
J1031098.description, J1031099.id, J1031099.name, J1031100.id,
J1031100.code, J1031074.scrisoare_medicala_parafa,
J1031074.scrisoare_medicala_contract,
J1031074.scrisoare_medicala_tip_contract, J1031074.export_siui,
J1031074.mesaj_siui, J1031087.id, J1031087.stencil_no,
J1031074.diagnostic_trimitere_text, J1031074.greutate_externare,
J1031074.data_decesului, J1031736.id, J1031736.descriere, J1031737.id,
J1031737.descriere, J1033295.id, J1033295.stencil_no, J1033299.id,
J1033299.description, J1031074.text_ore_ventilatie,
J1031074.drg_cod_grupa, J1031074.drg_relative_value,
J1031074.data_2500g, J1031074.prematur_gr_i, J1033304.id,
J1033304.description, J1033358.id, J1033358.name,
J1031074.reinternat_transfer, J1031074.aviz_comisie,
J1031074.criteriu_urgenta1, J1031074.criteriu_urgenta2,
J1031074.criteriu_urgenta3, J1031074.criteriu_urgenta4,
J1031074.criteriu_urgenta5, J1031074.criteriu_urgenta6,
J1031074.criteriu_urgenta7, J1031074.criteriu_urgenta8,
J1031074.criteriu_urgenta9a, J1031074.criteriu_urgenta9b,
J1031074.criteriu_urgenta10, J1031074.criteriu_urgenta11,
J1031074.criteriu_urgenta12, J1031074.criteriu_urgenta13
    ORDER BY J1031101.validfrom DESC
  LIMIT  20 OFFSET 0



------ Original Message ------
From: "Adrian Klaver" <adrian.klaver@aklaver.com>
To: "Sterpu Victor" <victor@caido.ro>; "PostgreSQL General"
<pgsql-general@postgresql.org>
Sent: 2/12/2016 5:45:56 PM
Subject: Re: [GENERAL] Windows performance

>On 02/12/2016 07:37 AM, Sterpu Victor wrote:
>>Hello
>>Why is Postgres so slow on Windows compared to linux?
>>Can I do something to match the performance?
>>I have 2 servers:
>>- one is Windows 8, CPU XEON, 8 CORES, 32G of RAM - my test query runs
>>in 17 seconds
>>- the other is Linux CPU XEON, 4 cores, 4 Gh of RAM - my test query
>>runs
>>in 2 seconds
>
>Assuming the version of Postgres is the same on both machine.
>In any case what are the versions on the machines?
>
>>I run the query on the same database.
>
>What is the query and what is the output of EXPLAIN ANALYZE?
>
>>CPU is not used at max on the servers, RAM is fine.
>>Is there a problem with Windows?My gues is that Windows is not using
>
>I will pass on this.
>
>>the hardware resources as it should be.
>>Can I do something to fix this?
>
>Are the settings in the postgresql.conf files the same?
>
>
>
>
>>Thank you.
>>
>
>-- Adrian Klaver
>adrian.klaver@aklaver.com

On 2/12/2016 8:09 AM, Sterpu Victor wrote:
> The query is very big but I pasted it at the end of the mail with the
> EXPLAIN ANALYZE.

I ran that explain through Depesz's 'explain' analyzer.
http://explain.depesz.com/s/c7ts

there's a lot(!) of small steps in that query.   I'm going to hazard a
guess that the issue is related to semaphore overhead or something like
that.

certainly, ti would be good to benchmark both OS's on the same version
of postgres, comparing 9.1 to 9.5 isn't fair.

--
john r pierce, recycling bits in santa cruz

On Fri, Feb 12, 2016 at 8:09 AM, Sterpu Victor <victor@caido.ro> wrote:
> There are some differences that I haven't mentioned.
> Postgres on Linux is PostgreSQL 9.1.4 64 bit
> Postgres on Windows is PostgreSQL 9.5.0, compiled by Visual C++ build 1800,
> 64-bit
>
> The query is very big but I pasted it at the end of the mail with the
> EXPLAIN ANALYZE.
> I runned the queries many times on every machine, the cache is not involved.
>
> postgresql.conf are defaults, I haven't compared them yet but I will.
>
>
> "Limit  (cost=163011.25..163011.63 rows=1 width=3640) (actual
> time=2811.693..2812.109 rows=20 loops=1)"

This seems to be the `explain analyze` for the fast one.  Can you also
post it for the slow one?

Also, for version 9.5 can you turn on track_io_timing and post the
output of EXPLAIN (analyze, buffers)?  Please do that separately from
the regular EXPLAIN ANALYZE though, to make it one of them easier to
compare to the one from version 9.1.

On Fri, 12 Feb 2016 15:37:37 +0000, "Sterpu Victor" <victor@caido.ro>
wrote:

>Why is Postgres so slow on Windows compared to linux?
>Can I do something to match the performance?
>I have 2 servers:
>- one is Windows 8, CPU XEON, 8 CORES,  32G of RAM - my test query runs
>in 17 seconds
>- the other is Linux CPU XEON, 4 cores, 4 Gh of RAM - my test query runs
>in 2 seconds
>I run the query on the same database.
>
>CPU is not used at max on the servers, RAM is fine.
>Is there a problem with Windows? My gues is that Windows is not using
>the hardware resources as it should be.
>Can I do something to fix this?
>
>Thank you.

Windows and Linux have very different cache, memory management, and
processor affinity behavior.

Additionally, Windows has suboptimal support for many POSIX features.
John Pierce mentioned the possibility of semaphores (locks) being the
problem.  If Posgresql uses POSIX locks on Window, that could
significantly impact performace (vs using Windows native locks).

In Linux the distinction between a "workstation" and a "server" is
largely a matter of system configuration.  Windows "desktop" and
"server" editions are different code bases: there are no magic
settings that can make one equivalent to the other.

Windows in general needs more RAM than Linux, but given comparable
resources, Windows server editions will have comparable performance.

Windows desktop editions are designed around the expectation that
there is a single interactive user.  They are not meant to be used as
servers.


That all said, I think 17 seconds vs 2 is far too much difference.
Assuming that Windows itself is running properly [e.g., not
accidentally using PIO mode for disk I/O or something similarly
stupid], it suggests that the servers are not configured the same, or
that the usage statistics for the Windows version are wildly incorrect
and that is throwing off query planning.

Did you analyze your database after moving it to Windows?

Another possibility is that the Windows tablespace is highly
fragmented.  Moderate levels of fragmentation don't really matter for
either Windows or Linux, but high file fragmentation favors Linux.


For best performance Postgresql really should to be tuned differently
for Windows than for Linux.  Unfortunately, although I am aware of
many of the differences between the operating systems, I'm not any
kind of expert at tuning Postgresql.

Hope this helps,
George

On 2/12/2016 2:28 PM, George Neuner wrote:
> In Linux the distinction between a "workstation" and a "server" is
> largely a matter of system configuration.  Windows "desktop" and
> "server" editions are different code bases: there are no magic
> settings that can make one equivalent to the other.


thats not actually true, the kernels are built from the same code base,
but there are internal settings that change the behavior defaults in the
scheduler, like prioritizing services vs the desktop.   these settings
have been obfuscated, at one time you could tweak them in the registry.






--
john r pierce, recycling bits in santa cruz

On 02/12/2016 11:22 AM, John R Pierce wrote:
> On 2/12/2016 8:09 AM, Sterpu Victor wrote:
>> The query is very big but I pasted it at the end of the mail with the
>> EXPLAIN ANALYZE.
>
> I ran that explain through Depesz's 'explain' analyzer.
> http://explain.depesz.com/s/c7ts
>
> there's a lot(!) of small steps in that query.   I'm going to hazard a
> guess that the issue is related to semaphore overhead or something like
> that.
>
> certainly, ti would be good to benchmark both OS's on the same version
> of postgres, comparing 9.1 to 9.5 isn't fair.

Something else that just dawned on me, the databases are not the same
version, is the data being queried the same in content and size?



--
Adrian Klaver
adrian.klaver@aklaver.com

On Fri, 12 Feb 2016 14:43:55 -0800, John R Pierce
<pierce@hogranch.com> wrote:

>On 2/12/2016 2:28 PM, George Neuner wrote:
>> In Linux the distinction between a "workstation" and a "server" is
>> largely a matter of system configuration.  Windows "desktop" and
>> "server" editions are different code bases: there are no magic
>> settings that can make one equivalent to the other.
>
>thats not actually true, the kernels are built from the same code base,

Technicality: the "code base" may be the same but the _code_ is not.

Corresponding[*] desktop and server editions install different code
for a number of key modules.  This is easily verified by comparing the
installations.

[*] server 2008  <>  windows 7
     server 2012  <>  windows 8

Haven't seen server 2016 yet.

>but there are internal settings that change the behavior defaults in the
>scheduler, like prioritizing services vs the desktop.   these settings
>have been obfuscated, at one time you could tweak them in the registry.

You still can tweak a great many things IFF you know how.  But it
isn't (and never was) possible to tweak a desktop into a server.

George