> Here's the part that's slow. The index scan on each partition is taking
> ~2.5ms, and is being repeated 1847 times *for each partition*.
> What is the table partitioned on?
The table is partitioned on registration_id.
CREATE TABLE emailsubscription.reg_email_subscriptions_p00
(
CONSTRAINT reg_email_subscriptions_p00_pkey PRIMARY KEY (registration_id,
subscription_id),
CONSTRAINT reg_email_subscriptions_p00_registration_id_check CHECK
((abs(registration_id) % 8::bigint) = 0)
)
INHERITS (emailsubscription.reg_email_subscriptions)
WITH (
OIDS=FALSE
);
Update:
We changed the query from
SELECT DISTINCT it.recipient_id
FROM iru.iru_tags it
WHERE it.recipient_id BETWEEN 758587587 and 968696896
AND it.status = 0
AND it.last_update_date >= date_trunc('day', now() - interval '90 days')
AND EXISTS (SELECT DISTINCT res.registration_id
FROM emailsubscription.reg_email_subscriptions res
WHERE res.registration_id = it.recipient_id
AND res.subscription_id = 200400);
to:
SELECT DISTINCT it.recipient_id
FROM iru.iru_tags it
where it.STATUS = 0
AND it.last_update_date >= date_trunc('day', now() - interval '90 days')
AND EXISTS (SELECT res.registration_id
FROM
emailsubscription.reg_email_subscriptions res
WHERE res.registration_id = it.recipient_id
and res.registration_id BETWEEN
8706059856 AND 8706077435
AND res.subscription_id = 200400);
And it slightly improved the performance.
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=733840.24..734045.35 rows=6837 width=8) (actual
time=14208.223..14208.414 rows=891 loops=1)
Output: it.recipient_id
Buffers: shared hit=73563 read=18189
I/O Timings: read=12490.324
-> Nested Loop (cost=30901.28..733823.14 rows=6837 width=8) (actual
time=6445.168..14203.967 rows=2547 loops=1)
Output: it.recipient_id
Buffers: shared hit=73563 read=18189
I/O Timings: read=12490.324
-> HashAggregate (cost=30900.72..31284.18 rows=12782 width=8)
(actual time=6394.062..6413.045 rows=14452 loops=1)
Output: res.registration_id
Buffers: shared hit=14158 read=914
I/O Timings: read=5771.423
-> Append (cost=0.00..30868.76 rows=12782 width=8) (actual
time=85.811..6361.937 rows=14452 loops=1)
Buffers: shared hit=14158 read=914
I/O Timings: read=5771.423
-> Seq Scan on emailsubscription.reg_email_subscriptions
res (cost=0.00..3470.45 rows=1 width=8) (actual time=75.713..75.713 rows=0
loops=1)
Output: res.registration_id
Filter: ((res.registration_id >= 8706059856::bigint)
AND (res.registration_id <= 8706077435::bigint) AND (res.subscription_id =
200400))
Rows Removed by Filter: 77239
Buffers: shared hit=569
-> Index Only Scan using reg_email_subscriptions_p00_pkey
on emailsubscription.reg_email_subscriptions_p00 res_1 (cost=0.57..3406.75
rows=1612 width=8) (actual time=10.095..611.086 rows=1802 loops=1)
Output: res_1.registration_id
Index Cond: ((res_1.registration_id >=
8706059856::bigint) AND (res_1.registration_id <= 8706077435::bigint) AND
(res_1.subscription_id = 200400))
Heap Fetches: 1806
Buffers: shared hit=1695 read=110
I/O Timings: read=562.961
-> Index Only Scan using reg_email_subscriptions_p01_pkey
on emailsubscription.reg_email_subscriptions_p01 res_2 (cost=0.57..3061.12
rows=1401 width=8) (actual time=19.052..849.618 rows=1794 loops=1)
Output: res_2.registration_id
Index Cond: ((res_2.registration_id >=
8706059856::bigint) AND (res_2.registration_id <= 8706077435::bigint) AND
(res_2.subscription_id = 200400))
Heap Fetches: 1794
Buffers: shared hit=1674 read=120
I/O Timings: read=739.525
-> Index Only Scan using reg_email_subscriptions_p02_pkey
on emailsubscription.reg_email_subscriptions_p02 res_3 (cost=0.57..3495.50
rows=1662 width=8) (actual time=19.480..1037.415 rows=1806 loops=1)
Output: res_3.registration_id
Index Cond: ((res_3.registration_id >=
8706059856::bigint) AND (res_3.registration_id <= 8706077435::bigint) AND
(res_3.subscription_id = 200400))
Heap Fetches: 1807
Buffers: shared hit=1687 read=117
I/O Timings: read=946.189
-> Index Only Scan using reg_email_subscriptions_p03_pkey
on emailsubscription.reg_email_subscriptions_p03 res_4 (cost=0.57..3349.44
rows=1532 width=8) (actual time=15.859..776.363 rows=1819 loops=1)
Output: res_4.registration_id
Index Cond: ((res_4.registration_id >=
8706059856::bigint) AND (res_4.registration_id <= 8706077435::bigint) AND
(res_4.subscription_id = 200400))
Heap Fetches: 1821
Buffers: shared hit=1710 read=120
I/O Timings: read=718.126
-> Index Only Scan using reg_email_subscriptions_p04_pkey
on emailsubscription.reg_email_subscriptions_p04 res_5 (cost=0.57..3600.05
rows=1713 width=8) (actual time=37.922..817.469 rows=1806 loops=1)
Output: res_5.registration_id
Index Cond: ((res_5.registration_id >=
8706059856::bigint) AND (res_5.registration_id <= 8706077435::bigint) AND
(res_5.subscription_id = 200400))
Heap Fetches: 1809
Buffers: shared hit=1705 read=110
I/O Timings: read=740.888
-> Index Only Scan using reg_email_subscriptions_p05_pkey
on emailsubscription.reg_email_subscriptions_p05 res_6 (cost=0.57..3156.12
rows=1445 width=8) (actual time=26.584..645.433 rows=1789 loops=1)
Output: res_6.registration_id
Index Cond: ((res_6.registration_id >=
8706059856::bigint) AND (res_6.registration_id <= 8706077435::bigint) AND
(res_6.subscription_id = 200400))
Heap Fetches: 1792
Buffers: shared hit=1691 read=112
I/O Timings: read=627.311
-> Index Only Scan using reg_email_subscriptions_p06_pkey
on emailsubscription.reg_email_subscriptions_p06 res_7 (cost=0.57..3369.90
rows=1605 width=8) (actual time=18.880..803.968 rows=1820 loops=1)
Output: res_7.registration_id
Index Cond: ((res_7.registration_id >=
8706059856::bigint) AND (res_7.registration_id <= 8706077435::bigint) AND
(res_7.subscription_id = 200400))
Heap Fetches: 1821
Buffers: shared hit=1718 read=110
I/O Timings: read=742.403
-> Index Only Scan using reg_email_subscriptions_p07_pkey
on emailsubscription.reg_email_subscriptions_p07 res_8 (cost=0.57..3959.44
rows=1811 width=8) (actual time=15.284..738.786 rows=1816 loops=1)
Output: res_8.registration_id
Index Cond: ((res_8.registration_id >=
8706059856::bigint) AND (res_8.registration_id <= 8706077435::bigint) AND
(res_8.subscription_id = 200400))
Heap Fetches: 1819
Buffers: shared hit=1709 read=115
I/O Timings: read=694.020
-> Index Scan using iru_tags_n1 on iru.iru_tags it (cost=0.57..54.93
rows=1 width=8) (actual time=0.423..0.537 rows=0 loops=14452)
Output: it.recipient_id, it.tagger_id, it.tag_id, it.batch_id,
it.status, it.creation_date, it.last_update_date
Index Cond: (it.recipient_id = res.registration_id)
Filter: ((it.status = 0) AND (it.last_update_date >=
date_trunc('day'::text, (now() - '90 days'::interval))))
Rows Removed by Filter: 1
Buffers: shared hit=59405 read=17275
I/O Timings: read=6718.901
Total runtime: 14209.137 ms
> That's good. Now the question is: why was the index corrupted? It's
> *extremely* unlikely that it's Postgres. In my experience, corruption is
> caused be bad hardware, or a misconfiguration (specifically, fsync not doing
> what it's supposed to).
I am not sure if there was a corruption in the first place. Since we suddenly
started having problem with a query that was running much faster before, we
were wondering if there was a corruption. But, vacuum did not indicate that we
had one. Details on the maintenance we did the night before is in the original
post.
Thank You for your time
Ramya
