Re: Question on session_replication_role

Jim,

 I am OP (Karthik)'s colleague. Please see the responses below.

> Your EXPLAIN ANALYZE output doesn't show this taking 30 seconds, it shows it taking 90ms. Please provide an EXPLAIN
ANALYZEThat actually demonstrates the problem. 
SELECT DISTINCT it.recipient_id FROM iru.iru_tags it WHERE it.recipient_id BETWEEN 7257057171 AND 7257704235 AND
it.status= 0 AND it.last_update_date >= date_trunc('day', now() - interval '90 days') AND EXISTS (SELECT DISTINCT
res.registration_idFROM emailsubscription.reg_email_subscriptions res WHERE res.registration_id = it.recipient_id AND
res.subscription_id= 200400); 
                                                                                                       QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=0.57..296573.59 rows=46 width=8) (actual time=24.613..21194.635 rows=904 loops=1)
   Output: it.recipient_id
   Buffers: shared hit=1094265 read=718
   I/O Timings: read=28.674
   ->  Nested Loop Semi Join  (cost=0.57..296570.25 rows=1335 width=8) (actual time=24.608..21190.382 rows=1264
loops=1)
         Output: it.recipient_id
         Buffers: shared hit=1094265 read=718
         I/O Timings: read=28.674
         ->  Index Only Scan using iru_tags_n31 on iru.iru_tags it  (cost=0.57..337.19 rows=1335 width=8) (actual
time=0.184..25.875rows=1847 loops=1) 
               Output: it.status, it.recipient_id, it.last_update_date
               Index Cond: ((it.status = 0) AND (it.recipient_id >= 7257057171::bigint) AND (it.recipient_id <=
7257704235::bigint)AND (it.last_update_date >= date_trunc('day'::text, (now() - '90 days'::interval)))) 
               Heap Fetches: 103
               Buffers: shared hit=820 read=180
               I/O Timings: read=7.614
         ->  Append  (cost=0.00..1748.87 rows=17 width=8) (actual time=11.454..11.454 rows=1 loops=1847)
               Buffers: shared hit=1093445 read=538
               I/O Timings: read=21.060
               ->  Seq Scan on emailsubscription.reg_email_subscriptions res  (cost=0.00..1728.07 rows=1 width=8)
(actualtime=11.316..11.316 rows=0 loops=1847) 
                     Output: res.registration_id
                     Filter: ((res.subscription_id = 200400) AND (it.recipient_id = res.registration_id))
                     Rows Removed by Filter: 77271
                     Buffers: shared hit=1050943
               ->  Index Only Scan using reg_email_subscriptions_p00_pkey on
emailsubscription.reg_email_subscriptions_p00res_1  (cost=0.57..2.60 rows=2 width=8) (actual time=0.033..0.033 rows=0
loops=1847)
                     Output: res_1.registration_id
                     Index Cond: ((res_1.registration_id = it.recipient_id) AND (res_1.subscription_id = 200400))
                     Heap Fetches: 0
                     Buffers: shared hit=7415 read=65
                     I/O Timings: read=2.802
               ->  Index Only Scan using reg_email_subscriptions_p01_pkey on
emailsubscription.reg_email_subscriptions_p01res_2  (cost=0.57..2.60 rows=2 width=8) (actual time=0.017..0.017 rows=0
loops=1710)
                     Output: res_2.registration_id
                     Index Cond: ((res_2.registration_id = it.recipient_id) AND (res_2.subscription_id = 200400))
                     Heap Fetches: 0
                     Buffers: shared hit=6866 read=67
                     I/O Timings: read=3.053
               ->  Index Only Scan using reg_email_subscriptions_p02_pkey on
emailsubscription.reg_email_subscriptions_p02res_3  (cost=0.57..2.60 rows=2 width=8) (actual time=0.017..0.017 rows=0
loops=1567)
                     Output: res_3.registration_id
                     Index Cond: ((res_3.registration_id = it.recipient_id) AND (res_3.subscription_id = 200400))
                     Heap Fetches: 0
                     Buffers: shared hit=6296 read=68
                     I/O Timings: read=2.812
               ->  Index Only Scan using reg_email_subscriptions_p03_pkey on
emailsubscription.reg_email_subscriptions_p03res_4  (cost=0.57..2.60 rows=2 width=8) (actual time=0.016..0.016 rows=0
loops=1406)
                     Output: res_4.registration_id
                     Index Cond: ((res_4.registration_id = it.recipient_id) AND (res_4.subscription_id = 200400))
                     Heap Fetches: 0
                     Buffers: shared hit=5678 read=68
                     I/O Timings: read=2.645
               ->  Index Only Scan using reg_email_subscriptions_p04_pkey on
emailsubscription.reg_email_subscriptions_p04res_5  (cost=0.57..2.60 rows=2 width=8) (actual time=0.019..0.019 rows=0
loops=1246)
                     Output: res_5.registration_id
                     Index Cond: ((res_5.registration_id = it.recipient_id) AND (res_5.subscription_id = 200400))
                     Heap Fetches: 0
                     Buffers: shared hit=5016 read=67
                     I/O Timings: read=2.647
               ->  Index Only Scan using reg_email_subscriptions_p05_pkey on
emailsubscription.reg_email_subscriptions_p05res_6  (cost=0.57..2.60 rows=2 width=8) (actual time=0.017..0.017 rows=0
loops=1082)
                     Output: res_6.registration_id
                     Index Cond: ((res_6.registration_id = it.recipient_id) AND (res_6.subscription_id = 200400))
                     Heap Fetches: 0
                     Buffers: shared hit=4355 read=67
                     I/O Timings: read=2.471
               ->  Index Only Scan using reg_email_subscriptions_p06_pkey on
emailsubscription.reg_email_subscriptions_p06res_7  (cost=0.57..2.60 rows=2 width=8) (actual time=0.019..0.019 rows=0
loops=946)
                     Output: res_7.registration_id
                     Index Cond: ((res_7.registration_id = it.recipient_id) AND (res_7.subscription_id = 200400))
                     Heap Fetches: 2
                     Buffers: shared hit=3828 read=69
                     I/O Timings: read=2.363
               ->  Index Only Scan using reg_email_subscriptions_p07_pkey on
emailsubscription.reg_email_subscriptions_p07res_8  (cost=0.57..2.60 rows=2 width=8) (actual time=0.021..0.021 rows=0
loops=752)
                     Output: res_8.registration_id
                     Index Cond: ((res_8.registration_id = it.recipient_id) AND (res_8.subscription_id = 200400))
                     Heap Fetches: 0
                     Buffers: shared hit=3048 read=67
                     I/O Timings: read=2.267
 Total runtime: 21195.648 ms
(71 rows)


> I don't believe that session_replication_role does anything to affect indexing, nor do the docs indicate that. I also
seenothing indicating that in the source code (search for SessionReplicationRole). 
> So if you suddenly started seeing dupes then I think your index is corrupted.
 How can we tell if any index is corrupted or not?
 If vacuum full re-indexes all the indexes in the table, would that have fixed the corruption, if we had any?

> It won't report it, but it would fix it. (Note that it would throw an error if the data in the table isn't actually
unique.)
 We did not get any error during re-indexing. So our understanding that there are no more duplicates in the table is
correct!


Thank You for your time
Ramya