public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jean-Christophe BOGGIO <[email protected]>
To: [email protected] <[email protected]>
Subject: Why isn't PG using an index-only scan?
Date: Thu, 18 Sep 2025 08:36:25 +0200
Message-ID: <[email protected]> (raw)
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
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]
Subject: Re: Why isn't PG using an index-only scan?
In-Reply-To: <[email protected]>
* 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