Hi,
Using the same query, with the same database on both machine, plans and
estimates are quasi identical, but actual cost is multiplied by three on my
server compared to my workstation, for all nodes in the plan. Can you tell me
what explains the difference?
I work with PostgreSQL 15.14 on Debian Old Stable (bookworm).
My workstation is a Lenovo X250 with 8GB RAM and an Intel(R) Core(TM) i5-5300U
CPU @ 2.30GHz (4 cores)
cpu MHz : 500.000
cache size : 3072 KB
My server is a Start-3-S-SSD server from online.net with 4 GB RAM and an
Intel(R) Atom(TM) CPU C2338 @ 1.74GHz (2 cores)
cpu MHz : 1198.820
cache size : 1024 KB
The query selects ~ 18 000 rows out of ~ 100 000 in table 'tbljournal',
calcultates several window functions on the results, then joins to another
small table (10 000 rows).
Below the two plans, followed by non-standard settings in postgresql.conf (they
are identical on both machines), and the table's schema at the bottom.
##############################
Explain analyze on the workstation
##############################
2025-12-04 17:09:28.133 CET [14576] www-data@compta LOG: duration: 210.004 ms
plan:
Query Text:
WITH t1 AS NOT MATERIALIZED (
SELECT id_client, fiscal_year, numero_compte, id_entry, id_line,
date_ecriture, substring(libelle_journal FOR 24) as libelle_journal,
substring(coalesce(id_facture, ' ') FOR 14) as id_facture,
substring(coalesce(id_paiement, ' ') FOR 14) as id_paiement,
substring(coalesce(libelle, ' ') FOR 34) as libelle, debit/100::numeric as
debit, credit/100::numeric as credit, lettrage, pointage, 0 as lettrage_check,
to_char(sum(debit/100::numeric) over (PARTITION BY numero_compte),
'999G999G999G990D00') as total_debit, to_char(sum(credit/100::numeric) over
(PARTITION BY numero_compte), '999G999G999G990D00') as total_credit,
to_char(sum((credit-debit)/100::numeric) over (PARTITION BY numero_compte ORDER
BY date_ecriture, id_line), '999G999G999G990D00') as solde,
to_char(sum(debit/100::numeric) over (), '999G999G999G990D00') as
grand_total_debit, to_char(sum(credit/100::numeric) over (),
'999G999G999G990D00') as grand_total_credit, count(*) over () as lines,
coalesce(libelle_section, '') as libelle_section, row_number() over (ORDER BY
numero_compte, date_ecriture, id_line) as row_number
FROM tbljournal
WHERE id_client = $1 and fiscal_year = $2
ORDER BY numero_compte, date_ecriture, id_line
)
SELECT t1.numero_compte, regexp_replace(t2.libelle_compte, '\s',
' ', 'g') as libelle_compte, id_entry, id_line, date_ecriture,
libelle_journal, coalesce(id_facture, ' ') as id_facture,
coalesce(id_paiement, ' ') as id_paiement, coalesce(libelle, ' ') as
libelle, to_char(debit, '999G999G999G990D00') as debit, to_char(credit,
'999G999G999G990D00') as credit, lettrage_check, pointage, total_debit,
total_credit, solde, grand_total_debit, grand_total_credit, libelle_section,
lettrage, lines
FROM t1 INNER JOIN tblcompte t2 using (id_client, fiscal_year,
numero_compte)
WHERE row_number > 2000 AND row_number < 3001
ORDER BY row_number
Sort (cost=3925.35..3926.60 rows=501 width=458) (actual
time=208.061..208.142 rows=1000 loops=1)
Sort Key: t1.row_number
Sort Method: quicksort Memory: 384kB
Buffers: shared hit=3565, temp read=341 written=298
-> Hash Join (cost=352.95..3902.88 rows=501 width=458) (actual
time=137.771..206.979 rows=1000 loops=1)
Hash Cond: ((t1.id_client = t2.id_client) AND (t1.fiscal_year =
t2.fiscal_year) AND (t1.numero_compte = t2.numero_compte))
Buffers: shared hit=3565, temp read=341 written=298
-> Subquery Scan on t1 (cost=1.75..3520.49 rows=3484
width=434) (actual time=132.109..195.096 rows=1000 loops=1)
Filter: (t1.row_number > 2000)
Rows Removed by Filter: 2000
Buffers: shared hit=3480, temp read=341 written=298
-> WindowAgg (cost=1.75..3389.84 rows=10452 width=434)
(actual time=123.125..194.702 rows=3000 loops=1)
Filter: ((row_number() OVER (?)) < 3001)
Rows Removed by Filter: 15188
Buffers: shared hit=3480, temp read=341 written=298
-> WindowAgg (cost=1.75..2762.72 rows=10452
width=223) (actual time=0.122..96.685 rows=18188 loops=1)
Buffers: shared hit=3480
-> WindowAgg (cost=1.75..2475.29 rows=10452
width=159) (actual time=0.113..70.644 rows=18188 loops=1)
Run Condition: (row_number() OVER (?) <
3001)
Buffers: shared hit=3480
-> WindowAgg (cost=1.75..2266.25
rows=10452 width=151) (actual time=0.103..55.901 rows=18188 loops=1)
Buffers: shared hit=3480
-> Incremental Sort
(cost=1.75..1978.82 rows=10452 width=119) (actual time=0.089..27.708 rows=18188
loops=1)
Sort Key:
tbljournal.numero_compte, tbljournal.date_ecriture, tbljournal.id_line
Presorted Key:
tbljournal.numero_compte
Full-sort Groups: 44 Sort
Method: quicksort Average Memory: 28kB Peak Memory: 28kB
Pre-sorted Groups: 51 Sort
Method: quicksort Average Memory: 535kB Peak Memory: 844kB
Buffers: shared hit=3480
-> Index Scan using
tbljournal_client_year_compte_idx on tbljournal (cost=0.29..1523.37 rows=10452
width=119) (actual time=0.023..11.331 rows=18188 loops=1)
Index Cond:
((id_client = 2739) AND (fiscal_year = 2024))
Buffers: shared
hit=3480
-> Hash (cost=181.80..181.80 rows=9680 width=36) (actual
time=5.616..5.617 rows=9680 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 794kB
Buffers: shared hit=85
-> Seq Scan on tblcompte t2 (cost=0.00..181.80
rows=9680 width=36) (actual time=0.018..1.888 rows=9680 loops=1)
Buffers: shared hit=85
##############################
Explain analyze on the server
##############################
2025-12-04 17:33:00.870 CET [536393] www-data@compta LOG: duration: 879.686 ms
plan:
Query Text:
WITH t1 AS NOT MATERIALIZED (
SELECT id_client, fiscal_year, numero_compte, id_entry, id_line,
date_ecriture, substring(libelle_journal FOR 24) as libelle_journal,
substring(coalesce(id_facture, ' ') FOR 14) as id_facture,
substring(coalesce(id_paiement, ' ') FOR 14) as id_paiement,
substring(coalesce(libelle, ' ') FOR 34) as libelle, debit/100::numeric as
debit, credit/100::numeric as credit, lettrage, pointage, 0 as lettrage_check,
to_char(sum(debit/100::numeric) over (PARTITION BY numero_compte),
'999G999G999G990D00') as total_debit, to_char(sum(credit/100::numeric) over
(PARTITION BY numero_compte), '999G999G999G990D00') as total_credit,
to_char(sum((credit-debit)/100::numeric) over (PARTITION BY numero_compte ORDER
BY date_ecriture, id_line), '999G999G999G990D00') as solde,
to_char(sum(debit/100::numeric) over (), '999G999G999G990D00') as
grand_total_debit, to_char(sum(credit/100::numeric) over (),
'999G999G999G990D00') as grand_total_credit, count(*) over () as lines,
coalesce(libelle_section, '') as libelle_section, row_number() over (ORDER BY
numero_compte, date_ecriture, id_line) as row_number
FROM tbljournal
WHERE id_client = $1 and fiscal_year = $2
ORDER BY numero_compte, date_ecriture, id_line
)
SELECT t1.numero_compte, regexp_replace(t2.libelle_compte, '\s',
' ', 'g') as libelle_compte, id_entry, id_line, date_ecriture,
libelle_journal, coalesce(id_facture, ' ') as id_facture,
coalesce(id_paiement, ' ') as id_paiement, coalesce(libelle, ' ') as
libelle, to_char(debit, '999G999G999G990D00') as debit, to_char(credit,
'999G999G999G990D00') as credit, lettrage_check, pointage, total_debit,
total_credit, solde, grand_total_debit, grand_total_credit, libelle_section,
lettrage, lines
FROM t1 INNER JOIN tblcompte t2 using (id_client, fiscal_year,
numero_compte)
WHERE row_number > 2000 AND row_number < 3001
ORDER BY row_number
Sort (cost=3705.52..3706.69 rows=469 width=458) (actual
time=872.263..872.511 rows=1000 loops=1)
Sort Key: t1.row_number
Sort Method: quicksort Memory: 384kB
Buffers: shared hit=3577
-> Hash Join (cost=363.42..3684.71 rows=469 width=458) (actual
time=582.015..867.062 rows=1000 loops=1)
Hash Cond: ((t1.id_client = t2.id_client) AND (t1.fiscal_year =
t2.fiscal_year) AND (t1.numero_compte = t2.numero_compte))
Buffers: shared hit=3574
-> Subquery Scan on t1 (cost=1.66..3293.96 rows=3234
width=434) (actual time=564.122..819.731 rows=1000 loops=1)
Filter: (t1.row_number > 2000)
Rows Removed by Filter: 2000
Buffers: shared hit=3486
-> WindowAgg (cost=1.66..3172.67 rows=9703 width=434)
(actual time=529.975..817.859 rows=3000 loops=1)
Filter: ((row_number() OVER (?)) < 3001)
Rows Removed by Filter: 15188
Buffers: shared hit=3486
-> WindowAgg (cost=1.66..2590.49 rows=9703
width=223) (actual time=0.696..436.937 rows=18188 loops=1)
Buffers: shared hit=3486
-> WindowAgg (cost=1.66..2323.66 rows=9703
width=159) (actual time=0.652..315.506 rows=18188 loops=1)
Run Condition: (row_number() OVER (?) <
3001)
Buffers: shared hit=3486
-> WindowAgg (cost=1.66..2129.60
rows=9703 width=151) (actual time=0.547..242.007 rows=18188 loops=1)
Buffers: shared hit=3486
-> Incremental Sort
(cost=1.66..1862.77 rows=9703 width=119) (actual time=0.519..94.824 rows=18188
loops=1)
Sort Key:
tbljournal.numero_compte, tbljournal.date_ecriture, tbljournal.id_line
Presorted Key:
tbljournal.numero_compte
Full-sort Groups: 44 Sort
Method: quicksort Average Memory: 28kB Peak Memory: 28kB
Pre-sorted Groups: 51 Sort
Method: quicksort Average Memory: 535kB Peak Memory: 844
kB
Buffers: shared hit=3486
-> Index Scan using
tbljournal_client_year_compte_idx on tbljournal (cost=0.29..1446.57 rows=9703
width=119) (actual time=0.098..36.042 rows=18188 loops=1)
Index Cond:
((id_client = 2739) AND (fiscal_year = 2024))
Buffers: shared
hit=3477
-> Hash (cost=187.55..187.55 rows=9955 width=36) (actual
time=17.560..17.563 rows=9955 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 813kB
Buffers: shared hit=88
-> Seq Scan on tblcompte t2 (cost=0.00..187.55
rows=9955 width=36) (actual time=0.048..6.363 rows=9955 loops=1)
Buffers: shared hit=88
#PostreSQL Settings
name | current_setting | source
-------------------------------+--------------------+--------------------
application_name | psql | client
auto_explain.log_analyze | on | configuration file
auto_explain.log_buffers | on | configuration file
auto_explain.log_min_duration | 20ms | configuration file
client_encoding | UTF8 | client
cluster_name | 15/main | configuration file
DateStyle | ISO, DMY | configuration file
default_text_search_config | pg_catalog.english | configuration file
dynamic_shared_memory_type | posix | configuration file
lc_messages | C.UTF-8 | configuration file
lc_monetary | C.UTF-8 | configuration file
lc_numeric | fr_FR.UTF-8 | database
lc_time | fr_FR.UTF-8 | database
log_line_prefix | %m [%p] %q%u@%d | configuration file
log_timezone | Europe/Paris | configuration file
max_connections | 150 | configuration file
max_wal_size | 1GB | configuration file
min_wal_size | 80MB | configuration file
port | 5432 | configuration file
random_page_cost | 1.1 | configuration file
shared_buffers | 128MB | configuration file
ssl | off | configuration file
TimeZone | Europe/Paris | configuration file
(23 rows)
########################
Table's schema
########################
Table "public.tbljournal"
Column | Type | Collation | Nullable | Default
-----------------+---------+-----------+----------+---------------------------------------------
date_ecriture | date | | not null |
id_facture | text | | |
libelle | text | | |
debit | integer | | not null | 0
credit | integer | | not null | 0
lettrage | text | | |
id_line | integer | | not null |
nextval('tbljournal_id_line_seq'::regclass)
id_entry | integer | | not null |
id_paiement | text | | |
numero_compte | text | | not null |
fiscal_year | integer | | not null |
id_client | integer | | not null |
libelle_journal | text | | not null |
id_export | integer | | |
pointage | boolean | | not null | false
date_validation | date | | not null | 'now'::text::date
libelle_section | text | | |
Indexes:
"tbljournal_id_line" PRIMARY KEY, btree (id_line)
"tblexport_id_client_idx" btree (id_client)
"tblexport_id_export_idx" btree (id_export)
"tbljournal_client_year_compte_idx" btree (id_client, fiscal_year,
numero_compte)
"tbljournal_client_year_libelle_journal_idx" btree (id_client, fiscal_year,
libelle_journal)
"tbljournal_id_entry_idx" btree (id_entry)
Check constraints:
"tbljournal_id_entry_not_o" CHECK (id_entry > 0)
Foreign-key constraints:
"tbljournal_client_year_libelle_journal_fk" FOREIGN KEY (id_client,
fiscal_year, libelle_journal) REFERENCES tbljournal_liste(id_client,
fiscal_year, libelle_journal) ON UPDATE CASCADE
"tbljournal_client_year_libelle_section_fk" FOREIGN KEY (id_client,
fiscal_year, libelle_section) REFERENCES tblanalytics(id_client, fiscal_year,
libelle_section) ON UPDATE CASCADE
"tbljournal_client_year_numero_compte_fk" FOREIGN KEY (id_client,
fiscal_year, numero_compte) REFERENCES tblcompte(id_client, fiscal_year,
numero_compte) ON UPDATE CASCADE
"tbljournal_id_client_fkey" FOREIGN KEY (id_client) REFERENCES
compta_client(id_client)
"tbljournal_id_export_fk" FOREIGN KEY (id_export) REFERENCES
tblexport(id_export) ON UPDATE CASCADE
Triggers:
check_month_is_archived BEFORE INSERT OR DELETE ON tbljournal FOR EACH ROW
EXECUTE FUNCTION tbljournal_check_month_is_archived()
--
Bien à vous, Vincent Veyron
https://compta.libremen.com
Logiciel libre de comptabilité générale et analytique en partie double
