Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vRELt-00A90k-0e for pgsql-performance@arkaria.postgresql.org; Thu, 04 Dec 2025 18:46:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vRELr-0048ZT-2A for pgsql-performance@arkaria.postgresql.org; Thu, 04 Dec 2025 18:46:51 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vRELr-0048ZI-0g for pgsql-performance@lists.postgresql.org; Thu, 04 Dec 2025 18:46:51 +0000 Received: from smtp-79.smtpout.orange.fr ([80.12.242.79] helo=smtp.smtpout.orange.fr) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vRELn-0039MZ-27 for pgsql-performance@lists.postgresql.org; Thu, 04 Dec 2025 18:46:51 +0000 Received: from X250.home ([IPv6:2a01:cb14:316:8c00:506c:d588:c52a:af6e]) by smtp.orange.fr with ESMTPSA id RELkv0NZnnm0CRELkvf58Q; Thu, 04 Dec 2025 19:46:45 +0100 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=wanadoo.fr; s=t20230301; t=1764874005; bh=691/e5lYBuAM24rKV6YO3cwzk0weuOi3GyBJITx7xFA=; h=Date:From:To:Subject:Message-Id:Mime-Version; b=MNLgeTE/KiOKdGxPU34HDd4WYjNBw1cICOKdYEzkDLutZ5V3+fWNyYYPKEwq2UmpU /jeLGrCCq6U0O34rEz+DUGP1zB/mr17WzdVfJFeJNBNahV4PHPGvvCzI1+mB7XcqV9 XEulpfwyNGN7dousTXsbyXqEyCQbRoCrchEUp4yCoF1g/DSKVlYxg2JCAhuA1wF0LW UQJpxt0pgZzryL46LP6bpEQhKnxGd3OqxKPZJwp0mkoHtqdB+FEEJiNZD5XkNzOwRC wDKZaXx1PQ0sT5amZfTEPT2LsIdCdDQ2S6i7dyyUdZGYPpdNmiPvhOepEZBjcnj2ZD eUXS8vHqlyfCg== X-ME-Helo: X250.home X-ME-Auth: dnYubGlzdHNAd2FuYWRvby5mcg== X-ME-Date: Thu, 04 Dec 2025 19:46:45 +0100 X-ME-IP: 2a01:cb14:316:8c00:506c:d588:c52a:af6e Date: Thu, 4 Dec 2025 19:46:44 +0100 From: Vincent Veyron To: pgsql-performance@lists.postgresql.org Subject: Why is my query 3 times faster on my workstation than on my server? Message-Id: <20251204194644.12e3a51a77da38dc072bc262@wanadoo.fr> X-Mailer: Sylpheed 3.8.0beta1 (GTK+ 2.24.33; x86_64-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, Using the same query, with the same database on both machine, plans and est= imates are quasi identical, but actual cost is multiplied by three on my se= rver compared to my workstation, for all nodes in the plan. Can you tell me= what explains the difference?=20 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-53= 00U 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 In= tel(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', cal= cultates several window functions on the results, then joins to another sma= ll 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:=20 WITH t1 AS NOT MATERIALIZED ( SELECT id_client, fiscal_year, numero_compte, id_entry, id_line, date_ecri= ture, substring(libelle_journal FOR 24) as libelle_journal, substring(coale= sce(id_facture, ' ') FOR 14) as id_facture, substring(coalesce(id_paie= ment, ' ') 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::num= eric) over (PARTITION BY numero_compte), '999G999G999G990D00') as total_deb= it, to_char(sum(credit/100::numeric) over (PARTITION BY numero_compte), '99= 9G999G999G990D00') as total_credit, to_char(sum((credit-debit)/100::numeric= ) over (PARTITION BY numero_compte ORDER BY date_ecriture, id_line), '999G9= 99G999G990D00') as solde, to_char(sum(debit/100::numeric) over (), '999G999= G999G990D00') 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 =3D $1 and fiscal_year =3D $2 =20 ORDER BY numero_compte, date_ecriture, id_line=20 )=20 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 cr= edit, lettrage_check, pointage, total_debit, total_credit, solde, grand_tot= al_debit, grand_total_credit, libelle_section, lettrage, lines FROM t1 INNER JOIN tblcompte t2 using (id_client, fiscal_year, numero_comp= te) WHERE row_number > 2000 AND row_number < 3001 =20 ORDER BY row_number =09 =20 Sort (cost=3D3925.35..3926.60 rows=3D501 width=3D458) (actual time=3D208.= 061..208.142 rows=3D1000 loops=3D1) Sort Key: t1.row_number Sort Method: quicksort Memory: 384kB Buffers: shared hit=3D3565, temp read=3D341 written=3D298 -> Hash Join (cost=3D352.95..3902.88 rows=3D501 width=3D458) (actual t= ime=3D137.771..206.979 rows=3D1000 loops=3D1) Hash Cond: ((t1.id_client =3D t2.id_client) AND (t1.fiscal_year = =3D t2.fiscal_year) AND (t1.numero_compte =3D t2.numero_compte)) Buffers: shared hit=3D3565, temp read=3D341 written=3D298 -> Subquery Scan on t1 (cost=3D1.75..3520.49 rows=3D3484 width= =3D434) (actual time=3D132.109..195.096 rows=3D1000 loops=3D1) Filter: (t1.row_number > 2000) Rows Removed by Filter: 2000 Buffers: shared hit=3D3480, temp read=3D341 written=3D298 -> WindowAgg (cost=3D1.75..3389.84 rows=3D10452 width=3D43= 4) (actual time=3D123.125..194.702 rows=3D3000 loops=3D1) Filter: ((row_number() OVER (?)) < 3001) Rows Removed by Filter: 15188 Buffers: shared hit=3D3480, temp read=3D341 written=3D= 298 -> WindowAgg (cost=3D1.75..2762.72 rows=3D10452 widt= h=3D223) (actual time=3D0.122..96.685 rows=3D18188 loops=3D1) Buffers: shared hit=3D3480 -> WindowAgg (cost=3D1.75..2475.29 rows=3D1045= 2 width=3D159) (actual time=3D0.113..70.644 rows=3D18188 loops=3D1) Run Condition: (row_number() OVER (?) < 30= 01) Buffers: shared hit=3D3480 -> WindowAgg (cost=3D1.75..2266.25 rows= =3D10452 width=3D151) (actual time=3D0.103..55.901 rows=3D18188 loops=3D1) Buffers: shared hit=3D3480 -> Incremental Sort (cost=3D1.75..= 1978.82 rows=3D10452 width=3D119) (actual time=3D0.089..27.708 rows=3D18188= loops=3D1) Sort Key: tbljournal.numero_co= mpte, tbljournal.date_ecriture, tbljournal.id_line Presorted Key: tbljournal.nume= ro_compte Full-sort Groups: 44 Sort Met= hod: quicksort Average Memory: 28kB Peak Memory: 28kB Pre-sorted Groups: 51 Sort Me= thod: quicksort Average Memory: 535kB Peak Memory: 844kB Buffers: shared hit=3D3480 -> Index Scan using tbljourna= l_client_year_compte_idx on tbljournal (cost=3D0.29..1523.37 rows=3D10452 = width=3D119) (actual time=3D0.023..11.331 rows=3D18188 loops=3D1) Index Cond: ((id_client = =3D 2739) AND (fiscal_year =3D 2024)) Buffers: shared hit=3D34= 80 -> Hash (cost=3D181.80..181.80 rows=3D9680 width=3D36) (actual t= ime=3D5.616..5.617 rows=3D9680 loops=3D1) Buckets: 16384 Batches: 1 Memory Usage: 794kB Buffers: shared hit=3D85 -> Seq Scan on tblcompte t2 (cost=3D0.00..181.80 rows=3D96= 80 width=3D36) (actual time=3D0.018..1.888 rows=3D9680 loops=3D1) Buffers: shared hit=3D85 ############################## Explain analyze on the server ############################## 2025-12-04 17:33:00.870 CET [536393] www-data@compta LOG: duration: 879.68= 6 ms plan: Query Text:=20 WITH t1 AS NOT MATERIALIZED ( SELECT id_client, fiscal_year, numero_compte, id_entry, id_line, date_ecri= ture, substring(libelle_journal FOR 24) as libelle_journal, substring(coale= sce(id_facture, ' ') FOR 14) as id_facture, substring(coalesce(id_paie= ment, ' ') 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::num= eric) over (PARTITION BY numero_compte), '999G999G999G990D00') as total_deb= it, to_char(sum(credit/100::numeric) over (PARTITION BY numero_compte), '99= 9G999G999G990D00') as total_credit, to_char(sum((credit-debit)/100::numeric= ) over (PARTITION BY numero_compte ORDER BY date_ecriture, id_line), '999G9= 99G999G990D00') as solde, to_char(sum(debit/100::numeric) over (), '999G999= G999G990D00') 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 =3D $1 and fiscal_year =3D $2 =20 ORDER BY numero_compte, date_ecriture, id_line=20 )=20 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 cr= edit, lettrage_check, pointage, total_debit, total_credit, solde, grand_tot= al_debit, grand_total_credit, libelle_section, lettrage, lines FROM t1 INNER JOIN tblcompte t2 using (id_client, fiscal_year, numero_comp= te) WHERE row_number > 2000 AND row_number < 3001 =20 ORDER BY row_number =09 =20 Sort (cost=3D3705.52..3706.69 rows=3D469 width=3D458) (actual time=3D872.= 263..872.511 rows=3D1000 loops=3D1) Sort Key: t1.row_number Sort Method: quicksort Memory: 384kB Buffers: shared hit=3D3577 -> Hash Join (cost=3D363.42..3684.71 rows=3D469 width=3D458) (actual t= ime=3D582.015..867.062 rows=3D1000 loops=3D1) Hash Cond: ((t1.id_client =3D t2.id_client) AND (t1.fiscal_year = =3D t2.fiscal_year) AND (t1.numero_compte =3D t2.numero_compte)) Buffers: shared hit=3D3574 -> Subquery Scan on t1 (cost=3D1.66..3293.96 rows=3D3234 width= =3D434) (actual time=3D564.122..819.731 rows=3D1000 loops=3D1) Filter: (t1.row_number > 2000) Rows Removed by Filter: 2000 Buffers: shared hit=3D3486 -> WindowAgg (cost=3D1.66..3172.67 rows=3D9703 width=3D434= ) (actual time=3D529.975..817.859 rows=3D3000 loops=3D1) Filter: ((row_number() OVER (?)) < 3001) Rows Removed by Filter: 15188 Buffers: shared hit=3D3486 -> WindowAgg (cost=3D1.66..2590.49 rows=3D9703 width= =3D223) (actual time=3D0.696..436.937 rows=3D18188 loops=3D1) Buffers: shared hit=3D3486 -> WindowAgg (cost=3D1.66..2323.66 rows=3D9703= width=3D159) (actual time=3D0.652..315.506 rows=3D18188 loops=3D1) Run Condition: (row_number() OVER (?) < 30= 01) Buffers: shared hit=3D3486 -> WindowAgg (cost=3D1.66..2129.60 rows= =3D9703 width=3D151) (actual time=3D0.547..242.007 rows=3D18188 loops=3D1) Buffers: shared hit=3D3486 -> Incremental Sort (cost=3D1.66..= 1862.77 rows=3D9703 width=3D119) (actual time=3D0.519..94.824 rows=3D18188 = loops=3D1) Sort Key: tbljournal.numero_co= mpte, tbljournal.date_ecriture, tbljournal.id_line Presorted Key: tbljournal.nume= ro_compte Full-sort Groups: 44 Sort Met= hod: quicksort Average Memory: 28kB Peak Memory: 28kB Pre-sorted Groups: 51 Sort Me= thod: quicksort Average Memory: 535kB Peak Memory: 844 kB Buffers: shared hit=3D3486 -> Index Scan using tbljourna= l_client_year_compte_idx on tbljournal (cost=3D0.29..1446.57 rows=3D9703 w= idth=3D119) (actual time=3D0.098..36.042 rows=3D18188 loops=3D1) Index Cond: ((id_client = =3D 2739) AND (fiscal_year =3D 2024)) Buffers: shared hit=3D34= 77 -> Hash (cost=3D187.55..187.55 rows=3D9955 width=3D36) (actual t= ime=3D17.560..17.563 rows=3D9955 loops=3D1) Buckets: 16384 Batches: 1 Memory Usage: 813kB Buffers: shared hit=3D88 -> Seq Scan on tblcompte t2 (cost=3D0.00..187.55 rows=3D99= 55 width=3D36) (actual time=3D0.048..6.363 rows=3D9955 loops=3D1) Buffers: shared hit=3D88 #PostreSQL Settings name | current_setting | source =20 -------------------------------+--------------------+-------------------- 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 | Defau= lt =20 -----------------+---------+-----------+----------+------------------------= --------------------- date_ecriture | date | | not null |=20 id_facture | text | | |=20 libelle | text | | |=20 debit | integer | | not null | 0 credit | integer | | not null | 0 lettrage | text | | |=20 id_line | integer | | not null | nextval('tbljournal_id_= line_seq'::regclass) id_entry | integer | | not null |=20 id_paiement | text | | |=20 numero_compte | text | | not null |=20 fiscal_year | integer | | not null |=20 id_client | integer | | not null |=20 libelle_journal | text | | not null |=20 id_export | integer | | |=20 pointage | boolean | | not null | false date_validation | date | | not null | 'now'::text::date libelle_section | text | | |=20 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, nume= ro_compte) "tbljournal_client_year_libelle_journal_idx" btree (id_client, fiscal_y= ear, 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, fis= cal_year, libelle_journal) REFERENCES tbljournal_liste(id_client, fiscal_ye= ar, libelle_journal) ON UPDATE CASCADE "tbljournal_client_year_libelle_section_fk" FOREIGN KEY (id_client, fis= cal_year, libelle_section) REFERENCES tblanalytics(id_client, fiscal_year, = libelle_section) ON UPDATE CASCADE "tbljournal_client_year_numero_compte_fk" FOREIGN KEY (id_client, fisca= l_year, numero_compte) REFERENCES tblcompte(id_client, fiscal_year, numero_= compte) ON UPDATE CASCADE "tbljournal_id_client_fkey" FOREIGN KEY (id_client) REFERENCES compta_c= lient(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() --=20 Bien =E0 vous, Vincent Veyron=20 https://compta.libremen.com Logiciel libre de comptabilit=E9 g=E9n=E9rale et analytique en partie double