> From: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] > Sent: Thursday, September 28, 2017 7:35 PM > To: firebird-support@yahoogroups.com > Subject: Re: [firebird-support] Firebird performance vs PostgreSQL > > > Hi, > > without any sample you talking about nothing.. > Show some sample which can be reproduced. > Without this i can say same Firebird is faster then Oracle and what someone > can say about this – nothing > > regards, > Karol Bieniaszewski
Hello, I am only providing details for above question. Not trying to say this is better or worse or anything. - SQL statements to create necessary database tables can be found in attached *.SQL files. - Followig link includes "insert into" statements which can be used for loading necessary data (around 100.000 records) on both FirebirdSQL and PostgreSQL databases. http://s5.dosya.tc/server5/3dard7/insert_sql_statements.rar.html - FirebirdSQL database should be created using WIN1254 character set. - PostgreSQL database should be created as follows For Linux: CREATE DATABASE pgtest ENCODING 'UTF-8' LC_COLLATE 'tr_TR.UTF-8' TEMPLATE template0; For Windows: CREATE DATABASE pgtest ENCODING 'UTF-8' LC_COLLATE 'tr-TR.UTF-8' TEMPLATE template0; or CREATE DATABASE pgtest ENCODING 'UTF8' LC_COLLATE 'Turkish_Turkey.1254' LC_CTYPE = 'Turkish_Turkey.1254' TEMPLATE template0; - Query used on both database systems is as follows: SELECT RAPOR_EK.KAYNAK, RAPOR_EK.SEBEP, COALESCE(DT.IAIK_OG, 0) AS IAIK_OG, COALESCE(DT.IAIK_AG, 0) AS IAIK_AG, COALESCE(DT.IAIK_TOPLAM, 0) AS IAIK_TOPLAM, COALESCE(DT.IADK_OG, 0) AS IADK_OG, COALESCE(DT.IADK_AG, 0) AS IADK_AG, COALESCE(DT.IADK_TOPLAM, 0) AS IADK_TOPLAM, COALESCE(DT.GNLTOPLAM, 0) AS GNLTOPLAM FROM RAPOR_EK LEFT JOIN ( SELECT M.KAYNAGAGORE AS KAYNAK, M.SEBEBEGORE AS SEBEP, (sum(M.ES_IIOG)/(select sum(I.IIOG) from sabitler I where kaynak = 'CİHANBEYLİ')*60) as IAIK_OG, (sum(M.ES_IIAG)/(select sum(I.IIAG) from sabitler I where kaynak = 'CİHANBEYLİ')*60) as IAIK_AG, ((sum(M.ES_IIOG) + sum(M.ES_IIAG)) / ((select sum(I.IIOG) from sabitler I where kaynak = 'CİHANBEYLİ') + (select sum(I.IIAG) from sabitler I where kaynak = 'CİHANBEYLİ'))*60) AS IAIK_TOPLAM, (sum(M.ES_IDOG)/(select sum(I.IDOG) from sabitler I where kaynak = 'CİHANBEYLİ')*60) as IADK_OG, (sum(M.ES_IDAG)/(select sum(I.IDAG) from sabitler I where kaynak = 'CİHANBEYLİ')*60) as IADK_AG, ((sum(M.ES_IDOG) + sum(M.ES_IDAG)) / ((select sum(I.IDOG) from sabitler I where kaynak = 'CİHANBEYLİ') + (select sum(I.IDAG) from sabitler I where kaynak = 'CİHANBEYLİ'))*60) AS IADK_TOPLAM, ((sum(M.ES_IIOG) + sum(M.ES_IIAG) + (sum(M.ES_IDOG) + sum(M.ES_IDAG))) / ((select sum(I.toplam) from sabitler I where kaynak = 'CİHANBEYLİ')*60)) AS GNLTOPLAM FROM veri M WHERE M.ILCE = 'CİHANBEYLİ' AND M.BILDIRIMEGORE = 'Bildirimsiz' AND M.SUREYEGORE = 'Uzun' AND (M.baslangic >= '2015-01-01' AND bitis <= '2015-01-31 23:59:59.999') GROUP BY M.KAYNAGAGORE, M.SEBEBEGORE ) DT USING (KAYNAK, SEBEP) ORDER BY KAYNAK, SEBEP; - Query plan for both databases can be found in attached *.TXT files. - My configuration i7-4720HQ cpu, 8GB RAM, Windows 10 64bit, 512GB Sandisk Extreme Pro SSD 6GB capability on 6GB connection. - PostgreSQL version 9.6.4 64bit (installed using Enterprise DB binaries) - FirebirdSQL version 3.0.1.32609 64bit (official installation) - Both database servers have default configuration files. Nothing changed, or tweaked. - I read following figures for above SQL statement execution on a freshly created database, freshly loaded data => FirebirdSQL using flamerobin.exe to execute (I failed to find a way to execute an SQL file with stats on using isql.exe): 1.505 seconds after computer reboot, 1.379 seconds for 2nd, 1.381 seconds for 3rd. => PostgreSQL using psql.exe to execute: 0.252 seconds after computer reboot, 0.029 seconds on 2nd, 0.024 seconds on 3rd You may have additional questions, I try my best to answer within my knowledge. Regards, Ertan Küçükoğlu ---------- Field #01: RAPOR_EK.KAYNAK Alias:KAYNAK Type:STRING(20) Field #02: RAPOR_EK.SEBEP Alias:SEBEP Type:STRING(20) Field #03: .COALESCE Alias:IAIK_OG Type:NUMERIC(18,2) Field #04: .COALESCE Alias:IAIK_AG Type:NUMERIC(18,2) Field #05: .COALESCE Alias:IAIK_TOPLAM Type:NUMERIC(18,2) Field #06: .COALESCE Alias:IADK_OG Type:NUMERIC(18,2) Field #07: .COALESCE Alias:IADK_AG Type:NUMERIC(18,2) Field #08: .COALESCE Alias:IADK_TOPLAM Type:NUMERIC(18,2) Field #09: .COALESCE Alias:GNLTOPLAM Type:NUMERIC(18,2) PLAN (DT I INDEX (RDB$PRIMARY2)) PLAN (DT I INDEX (RDB$PRIMARY2)) PLAN (DT I INDEX (RDB$PRIMARY2)) PLAN (DT I INDEX (RDB$PRIMARY2)) PLAN (DT I INDEX (RDB$PRIMARY2)) PLAN (DT I INDEX (RDB$PRIMARY2)) PLAN (DT I INDEX (RDB$PRIMARY2)) PLAN (DT I INDEX (RDB$PRIMARY2)) PLAN (DT I INDEX (RDB$PRIMARY2)) PLAN JOIN (RAPOR_EK ORDER rapor_ek_pkey, SORT (DT M NATURAL)) ---------- Merge Left Join (cost=6600.19..6610.98 rows=560 width=340) Merge Cond: (((rapor_ek.kaynak)::text = (m.kaynagagore)::text) AND ((rapor_ek.sebep)::text = (m.sebebegore)::text)) -> Sort (cost=41.16..42.56 rows=560 width=116) Sort Key: rapor_ek.kaynak, rapor_ek.sebep -> Seq Scan on rapor_ek (cost=0.00..15.60 rows=560 width=116) -> Materialize (cost=6559.03..6565.45 rows=31 width=256) -> GroupAggregate (cost=6559.03..6565.06 rows=31 width=256) Group Key: m.kaynagagore, m.sebebegore InitPlan 1 (returns $0) -> Aggregate (cost=2.43..2.44 rows=1 width=8) -> Seq Scan on sabitler i (cost=0.00..2.42 rows=1 width=4) Filter: ((kaynak)::text = 'CİHANBEYLİ'::text) InitPlan 2 (returns $1) -> Aggregate (cost=2.43..2.44 rows=1 width=8) -> Seq Scan on sabitler i_1 (cost=0.00..2.42 rows=1 width=4) Filter: ((kaynak)::text = 'CİHANBEYLİ'::text) InitPlan 3 (returns $2) -> Aggregate (cost=2.43..2.44 rows=1 width=8) -> Seq Scan on sabitler i_2 (cost=0.00..2.42 rows=1 width=4) Filter: ((kaynak)::text = 'CİHANBEYLİ'::text) InitPlan 4 (returns $3) -> Aggregate (cost=2.43..2.44 rows=1 width=8) -> Seq Scan on sabitler i_3 (cost=0.00..2.42 rows=1 width=4) Filter: ((kaynak)::text = 'CİHANBEYLİ'::text) InitPlan 5 (returns $4) -> Aggregate (cost=2.43..2.44 rows=1 width=8) -> Seq Scan on sabitler i_4 (cost=0.00..2.42 rows=1 width=4) Filter: ((kaynak)::text = 'CİHANBEYLİ'::text) InitPlan 6 (returns $5) -> Aggregate (cost=2.43..2.44 rows=1 width=8) -> Seq Scan on sabitler i_5 (cost=0.00..2.42 rows=1 width=4) Filter: ((kaynak)::text = 'CİHANBEYLİ'::text) InitPlan 7 (returns $6) -> Aggregate (cost=2.43..2.44 rows=1 width=8) -> Seq Scan on sabitler i_6 (cost=0.00..2.42 rows=1 width=4) Filter: ((kaynak)::text = 'CİHANBEYLİ'::text) InitPlan 8 (returns $7) -> Aggregate (cost=2.43..2.44 rows=1 width=8) -> Seq Scan on sabitler i_7 (cost=0.00..2.42 rows=1 width=4) Filter: ((kaynak)::text = 'CİHANBEYLİ'::text) InitPlan 9 (returns $8) -> Aggregate (cost=2.43..2.44 rows=1 width=8) -> Seq Scan on sabitler i_8 (cost=0.00..2.42 rows=1 width=4) Filter: ((kaynak)::text = 'CİHANBEYLİ'::text) -> Sort (cost=6537.09..6537.27 rows=70 width=46) Sort Key: m.kaynagagore, m.sebebegore -> Seq Scan on veri m (cost=0.00..6534.94 rows=70 width=46) Filter: ((baslangic >= '2015-01-01 00:00:00'::timestamp without time zone) AND (bitis <= '2015-01-31 23:59:59.999'::timestamp without time zone) AND ((ilce)::text = 'CİHANBEYLİ'::text) AND ((bildirimegore)::text = 'Bildirimsiz'::text) AND ((sureyegore)::text = 'Uzun'::text)) [Non-text portions of this message have been removed]
