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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
