Re: Question on session_replication_role

> 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