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@xxxxxxxxxxx>
To: "Sterpu Victor" <victor@xxxxxxxx>; "PostgreSQL General"
<pgsql-general@xxxxxxxxxxxxxx>
Sent: 2/12/2016 5:45:56 PM
Subject: Re: 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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
