Thread
-
Re: Why is my query 3 times faster on my workstation than on my server?
Pavel Stehule <pavel.stehule@gmail.com> — 2025-12-04T18:53:34Z
Hi čt 4. 12. 2025 v 19:46 odesílatel Vincent Veyron <vv.lists@wanadoo.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@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 > > >