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