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