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 ANALYZE That 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_id FROM 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.875 rows=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) (actual time=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_p00 res_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_p01 res_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_p02 res_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_p03 res_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_p04 res_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_p05 res_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_p06 res_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_p07 res_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 see nothing 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
