public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Rowley <[email protected]>
To: Jean-Christophe BOGGIO <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Why isn't PG using an index-only scan?
Date: Thu, 18 Sep 2025 19:20:46 +1200
Message-ID: <CAApHDvoUdvw68oHnfACQE=u8XPJ+Te49dZsfC2yW+DVfzdT2+Q@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
On Thu, 18 Sept 2025 at 18:36, Jean-Christophe BOGGIO
<[email protected]> 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
view thread (9+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Why isn't PG using an index-only scan?
In-Reply-To: <CAApHDvoUdvw68oHnfACQE=u8XPJ+Te49dZsfC2yW+DVfzdT2+Q@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox