Re: Windows performance - Mailing list pgsql-general
| From | Sterpu Victor |
|---|---|
| Subject | Re: Windows performance |
| Date | |
| Msg-id | emc71ccac9-4ece-4599-8750-5b03d668e93c@victor-pc Whole thread Raw |
| In response to | Re: Windows performance (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: Windows performance
Re: Windows performance |
| List | pgsql-general |
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
pgsql-general by date: