> 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]