can you share result for: *explain analyze* SELECT p.id_parcelle FROM private.parcelles p WHERE (p.dep IN ( '08', '10', '54', '57', '67', '68')) ;
On Fri, 9 Feb 2024 at 17:14, kimaidou <kimai...@gmail.com> wrote: > 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 > > > -- https://www.burcinyazici.com
