On 2/2/15 8:07 PM, Anand Kumar, Karthik wrote:
2)When we run the selects on each table separately, the query runs
really fast. The moment we introduce the join (AND EXISTS), the sql
takes over 30 seconds.
3)The explain plan of this query shows that Primary key on
reg_email_subscriptions and unique index on iru_tags table is being used.
site=# explain(analyze on, verbose on, costs on, buffers on, timing on)
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);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=0.57..290191.08 rows=45 width=8) (actual
time=89.536..89.536 rows=0 loops=1)
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.
1)Did setting session_replication_role to replica before inserting
(duplicate) records corrupt the primary key or any of the indexes on
reg_email_subscriptions table?
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.
2)If so, should a reindex on the corrupt PK or index identify the
corruption and report/fix it?
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.)
3)Is this even a problem with index/data corruption?
I doubt it, though you certainly could have corruption.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
