Hi all, I have performance issue for a pretty simple request in a PostgreSQL server 14.10
* Request SELECT p.id_parcelle FROM private.parcelles p WHERE (p.dep IN ( '08', '10', '54', '57', '67', '68')) ; * Table definition (extract) Table « private.parcelles » Colonne | Type | Collationnement | NULL-able | Par défaut -----------------------+-----------------------------+-----------------+-----------+------------ id | integer | | | geom | geometry(MultiPolygon,2154) | | | fid | bigint | | | id_parcelle | character varying(14) | | not null | insee_col | character varying(5) | | | nom_col | character varying | | | section | character varying(2) | | | numero | character varying(4) | | | contenance | bigint | | | epci_nom | character varying | | | dep | character varying | | | dep_nom | character varying | | | Index : "foncier_pkey" PRIMARY KEY, btree (id_parcelle) "idx_extension_eol_parcelle" btree (extension_eol) "idx_lien_hubspot_parcelels" btree (lien_hubspot) "idx_reg_parcelle" btree (reg) "idx_type_ener_parcelles" btree (type_d_energie) "parcelles_dep_idx" btree (dep) "parcelles_id_parcelle_idx" btree (id_parcelle) "parcelles_inseecol_idx" btree (insee_col) "parcelles_object_id_idx" btree (hs_object_id) "parcelles_pipelinestage_idx" btree (hs_pipeline_stage) "parcelles_synctohubspot_idx" btree (synctohubspot) "sidx_foncier_geom" gist (geom) -> First comment, the primary Key should be on id (integer) and not on id_parcelle (a text code) * Statistiques lizmap_synerdev_carto=# SELECT * FROM pg_stat_all_tables WHERE schemaname = 'private' AND relname = 'parcelles'; -[ RECORD 1 ]-------+------------------------------ relid | 2364725 schemaname | private relname | parcelles seq_scan | 1891 seq_tup_read | 552509679 idx_scan | 19144304 idx_tup_fetch | 38926631 n_tup_ins | 3 n_tup_upd | 3073182 n_tup_del | 0 n_tup_hot_upd | 2996591 n_live_tup | 92876681 n_dead_tup | 1836882 n_mod_since_analyze | 769313 n_ins_since_vacuum | 3 last_vacuum | last_autovacuum | last_analyze | 2024-02-08 15:33:14.008286+01 last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 1 autoanalyze_count | 0 * Plan : https://explain.dalibo.com/plan/47391e3g8c2589cf#plan/node/2 It seems PostgreSQL does not use the index parcelles_dep_idx on "dep" (text field), even if the corresponding number of lines for this WHERE clause is a smal subset of the entire data: approx 6M against 80M in total Thanks in advance for any hint regarding this cumbersome query. Regards Kimaidou