Re: Question on session_replication_role

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