Why is my query 3 times faster on my workstation than on my server? - Mailing list pgsql-performance

Hi,

Using the same query, with the same database on both machine, plans and estimates are quasi identical, but actual cost
ismultiplied by three on my server compared to my workstation, for all nodes in the plan. Can you tell me what explains
thedifference?  

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
thetable'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)
aslibelle_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::numericas credit, lettrage, pointage, 0 as lettrage_check, to_char(sum(debit/100::numeric) over (PARTITION
BYnumero_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_compteORDER 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
(ORDERBY 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, ' ')
asid_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=18188loops=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=18188loops=1) 
                                          Buffers: shared hit=3480
                                          ->  Incremental Sort  (cost=1.75..1978.82 rows=10452 width=119) (actual
time=0.089..27.708rows=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
PeakMemory: 28kB 
                                                Pre-sorted Groups: 51  Sort Method: quicksort  Average Memory: 535kB
PeakMemory: 844kB 
                                                Buffers: shared hit=3480
                                                ->  Index Scan using tbljournal_client_year_compte_idx on tbljournal
(cost=0.29..1523.37rows=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=9680loops=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)
aslibelle_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::numericas credit, lettrage, pointage, 0 as lettrage_check, to_char(sum(debit/100::numeric) over (PARTITION
BYnumero_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_compteORDER 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
(ORDERBY 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, ' ')
asid_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=18188loops=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=18188loops=1) 
                                          Buffers: shared hit=3486
                                          ->  Incremental Sort  (cost=1.66..1862.77 rows=9703 width=119) (actual
time=0.519..94.824rows=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
PeakMemory: 28kB 
                                                Pre-sorted Groups: 51  Sort Method: quicksort  Average Memory: 535kB
PeakMemory: 844 
kB
                                                Buffers: shared hit=3486
                                                ->  Index Scan using tbljournal_client_year_compte_idx on tbljournal
(cost=0.29..1446.57rows=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=9955loops=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