MIME-Version: 1.0
References: <8f25c08c-acb2-47ca-b8c1-6664fc31a361@thefreecat.org>
In-Reply-To: <8f25c08c-acb2-47ca-b8c1-6664fc31a361@thefreecat.org>
From: David Rowley <dgrowleyml@gmail.com>
Date: Thu, 18 Sep 2025 19:20:46 +1200
Message-ID: 
 <CAApHDvoUdvw68oHnfACQE=u8XPJ+Te49dZsfC2yW+DVfzdT2+Q@mail.gmail.com>
Subject: Re: Why isn't PG using an index-only scan?
To: Jean-Christophe BOGGIO <postgresql@thefreecat.org>
Cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvoUdvw68oHnfACQE%3Du8XPJ%2BTe49dZsfC2yW%2BDVfzdT2%2BQ%40mail.gmail.com>
Precedence: bulk

On Thu, 18 Sept 2025 at 18:36, Jean-Christophe BOGGIO
<postgresql@thefreecat.org> wrote:
> 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)

> 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.

Yes. Since *all* records of "oeu" are required and they're not
required in any particular order, then Seq Scan should be the fastest
way to access those records.

5 hours seems very slow for the estimated number of records. Have you
tried running the SELECT using EXPLAIN ANALYZE without the INSERT
part? Even if the 84 million Merge Join row estimate is accurate, 5
hours seems excessively long.

If it still takes a long time, you might try SET enable_mergejoin = 0;
and run the EXPLAIN ANALYZE SELECT .. part.  That'll at least give us
more accurate row counts of what we're actually working with.

David