Re: Why is my query 3 times faster on my workstation than on my server?

Hi

čt 4. 12. 2025 v 19:46 odesílatel Vincent Veyron <vv(dot)lists(at)wanadoo(dot)fr>
napsal:

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

Intel Atom is slow CPU

https://en.wikipedia.org/wiki/Intel_Atom

Regards

Pavel

>
> 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(at)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, '&nbsp;') FOR 14) as id_facture,
> substring(coalesce(id_paiement, '&nbsp;') FOR 14) as id_paiement,
> substring(coalesce(libelle, '&nbsp;') 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',
> '&nbsp;', 'g') as libelle_compte, id_entry, id_line, date_ecriture,
> libelle_journal, coalesce(id_facture, '&nbsp;') as id_facture,
> coalesce(id_paiement, '&nbsp;') as id_paiement, coalesce(libelle, '&nbsp;')
> 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(at)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, '&nbsp;') FOR 14) as id_facture,
> substring(coalesce(id_paiement, '&nbsp;') FOR 14) as id_paiement,
> substring(coalesce(libelle, '&nbsp;') 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',
> '&nbsp;', 'g') as libelle_compte, id_entry, id_line, date_ecriture,
> libelle_journal, coalesce(id_facture, '&nbsp;') as id_facture,
> coalesce(id_paiement, '&nbsp;') as id_paiement, coalesce(libelle, '&nbsp;')
> 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(at)%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
>
>
>