Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uz8Fx-00H6eE-Mp for pgsql-performance@arkaria.postgresql.org; Thu, 18 Sep 2025 06:36:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uz8Fw-00EBg5-Co for pgsql-performance@arkaria.postgresql.org; Thu, 18 Sep 2025 06:36:36 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with utf8esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uz8Fv-00EBfY-VW for pgsql-performance@lists.postgresql.org; Thu, 18 Sep 2025 06:36:36 +0000 Received: from facteur.thefreecat.org ([212.129.38.6]) by magus.postgresql.org with utf8esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1uz8Fn-001SMO-06 for pgsql-performance@lists.postgresql.org; Thu, 18 Sep 2025 06:36:30 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=thefreecat.org; s=thefreecat; t=1758177386; bh=Vb/2F5ZnvxH1gLDBNStMZm+lxDLWLrcg9bGw+foHiKU=; h=Date:To:From:Subject; b=ALEa1NA6Z8teOD8kJU4TYEsyDHLx6NkTaLsRm1DGVSwOCgOp73joLebNninF9m2VG RGAx1/+PLQxbDffYtFp6+mbPz4ALNeFesaZW134TpLUho7R+byB1U+/0Lq7ZcLgKgF cjREWOubS79WsOXU1kFiJoCM0kDNWrOKKUiD1gOkMAxYYgzfJC/i8gU7O9u0e Original-Subject: Why isn't PG using an index-only scan? Original-From: Jean-Christophe BOGGIO Received: from [192.168.1.182] (91-167-190-143.subs.proxad.net [::ffff:91.167.190.143]) (AUTH: LOGIN cat, TLS: TLS1.3,128bits,ECDHE_RSA_AES_128_GCM_SHA256) by facteur.thefreecat.org with ESMTPSA id 0000000009DC06B5.0000000068CBA86A.002F53DD; Thu, 18 Sep 2025 08:36:26 +0200 Content-Type: multipart/alternative; boundary="------------I9cZD0arlLuwOGLpnPX5bom0" Message-ID: <8f25c08c-acb2-47ca-b8c1-6664fc31a361@thefreecat.org> Date: Thu, 18 Sep 2025 08:36:25 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Content-Language: en-US, fr To: "pgsql-performance@lists.postgresql.org" From: Jean-Christophe BOGGIO Subject: Why isn't PG using an index-only scan? List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------I9cZD0arlLuwOGLpnPX5bom0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Hello, I have this very simple query: INSERT INTO copyrightad (idoeu, idad, role, mechowned, perfowned, iscontrolled) SELECT o.idoeu, c.idad, LEFT(sipa_capacity1,3), sipa_mech_owned, sipa_perf_owned, sipa_controlled='Y' FROM imaestro.msipfl ip JOIN oeu o ON o.imworkid=ip.sipa_song_code JOIN ad c ON c.imcompid=ip.sipa_ip_code WHERE ip.sipa_comp_or_publ='C'; And here are the number of elements in each table: imaestro.msipfl: 1550019 oeu: 1587533 ad: 304986 The explain plan is saying this:                                                QUERY PLAN ---------------------------------------------------------------------------------------------------------- Insert on copyrightad  (cost=613790.59..4448045.97 rows=0 width=0)   ->  Merge Join  (cost=613790.59..4448045.97 rows=84972138 width=328)         Merge Cond: (((c.imcompid)::numeric) = ip.sipa_ip_code)         ->  Sort  (cost=35712.97..36475.44 rows=304986 width=8)               Sort Key: ((c.imcompid)::numeric)               ->  Index Only Scan using ix_ad_imcompid on ad c  (cost=0.42..7931.21 rows=304986 width=8)         ->  Materialize  (cost=578077.61..594521.17 rows=3288712 width=23)               ->  Sort  (cost=578077.61..586299.39 rows=3288712 width=23)                     Sort Key: ip.sipa_ip_code                     ->  Hash Join  (cost=56043.04..154644.48 rows=3288712 width=23)                           Hash Cond: ((o.imworkid)::numeric = ip.sipa_song_code)                           ->  Seq Scan on oeu o  (cost=0.00..41901.33 rows=1587533 width=8)                           ->  Hash  (cost=48542.24..48542.24 rows=600064 width=25)                                 ->  Seq Scan on msipfl ip  (cost=0.00..48542.24 rows=600064 width=25)                                       Filter: ((sipa_comp_or_publ)::text = 'C'::text) Table ad has this index: "ix_ad_imcompid" btree (imcompid, idad) Table oeu has this one: "ix_oeu_imcompid" btree (imworkid, idoeu) idad and idoeu are both primary keys of, respectively, ad and oeu. The resultset should be 591615 rows because: select sipa_comp_or_publ, count(*) from imaestro.msipfl group by 1; sipa_comp_or_publ | count -------------------+-------- C                 | 591615 P                 | 958404 Is it normal that PG is doing a seq scan on table oeu but an index-only scan on ad? I had to stop the query after 5 hours, how can I make this faster? Of course I ran VACUUM ANALYZE. These are the memory settings I have, but I have plenty of unused RAM. Should I bump something up? shared_buffers = 16GB                   # min 128kB #huge_pages = try                       # on, off, or try #huge_page_size = 0                     # zero for system default #temp_buffers = 8MB                     # min 800kB #max_prepared_transactions = 0          # zero disables the feature work_mem = 128MB                                # min 64kB $ free -h               total        used        free      shared  buff/cache   available Mem:           125Gi        18Gi       3.1Gi        15Gi       121Gi       106Gi Swap:          4.0Gi       2.0Gi       2.0Gi Thanks for your help, JC --------------I9cZD0arlLuwOGLpnPX5bom0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

Hello,

I have this very simple query:

INSERT INTO copyrightad (idoeu, idad, role, mechowned, perfowned, iscontrolled)
SELECT o.idoeu, c.idad, LEFT(sipa_capacity1,3), sipa_mech_owned, sipa_perf_owned, sipa_controlled='Y'
FROM imaestro.msipfl ip
JOIN oeu o ON o.imworkid=ip.sipa_song_code
JOIN ad c ON c.imcompid=ip.sipa_ip_code
WHERE ip.sipa_comp_or_publ='C';

And here are the number of elements in each table:

imaestro.msipfl: 1550019

oeu: 1587533

ad: 304986

The explain plan is saying this:

                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------
Insert on copyrightad  (cost=613790.59..4448045.97 rows=0 width=0)
  ->  Merge Join  (cost=613790.59..4448045.97 rows=84972138 width=328)
        Merge Cond: (((c.imcompid)::numeric) = ip.sipa_ip_code)
        ->  Sort  (cost=35712.97..36475.44 rows=304986 width=8)
              Sort Key: ((c.imcompid)::numeric)
              ->  Index Only Scan using ix_ad_imcompid on ad c  (cost=0.42..7931.21 rows=304986 width=8)
        ->  Materialize  (cost=578077.61..594521.17 rows=3288712 width=23)
              ->  Sort  (cost=578077.61..586299.39 rows=3288712 width=23)
                    Sort Key: ip.sipa_ip_code
                    ->  Hash Join  (cost=56043.04..154644.48 rows=3288712 width=23)
                          Hash Cond: ((o.imworkid)::numeric = ip.sipa_song_code)
                          ->  Seq Scan on oeu o  (cost=0.00..41901.33 rows=1587533 width=8)
                          ->  Hash  (cost=48542.24..48542.24 rows=600064 width=25)
                                ->  Seq Scan on msipfl ip  (cost=0.00..48542.24 rows=600064 width=25)
                                      Filter: ((sipa_comp_or_publ)::text = 'C'::text)

Table ad has this index:

"ix_ad_imcompid" btree (imcompid, idad)

Table oeu has this one:

"ix_oeu_imcompid" btree (imworkid, idoeu)

idad and idoeu are both primary keys of, respectively, ad and oeu.

The resultset should be 591615 rows because:

select sipa_comp_or_publ, count(*) from imaestro.msipfl group by 1;
sipa_comp_or_publ | count   
-------------------+--------
C                 | 591615
P                 | 958404

Is it normal that PG is doing a seq scan on table oeu but an index-only scan on ad? I had to stop the query after 5 hours, how can I make this faster? Of course I ran VACUUM ANALYZE.

These are the memory settings I have, but I have plenty of unused RAM. Should I bump something up?

shared_buffers = 16GB                   # min 128kB                                                                                                  
#huge_pages = try                       # on, off, or try                                                                                            
#huge_page_size = 0                     # zero for system default                                                                                    
#temp_buffers = 8MB                     # min 800kB                                                                                                  
#max_prepared_transactions = 0          # zero disables the feature                                                                                  
work_mem = 128MB                                # min 64kB          

$ free -h
              total        used        free      shared  buff/cache   available
Mem:           125Gi        18Gi       3.1Gi        15Gi       121Gi       106Gi
Swap:          4.0Gi       2.0Gi       2.0Gi

Thanks for your help,

JC

--------------I9cZD0arlLuwOGLpnPX5bom0--