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
