Re: DATA corruption after promoting slave to master

[Kirit Parmar <kirit.p@xxxxxxxxxxx>]

Hey Shaun,

Hope you are keeping well. Will you please help me with this un-common behaviour of postgres. When executing the query (1) it takes around 62 seconds. However when disabling the index scan the same query (2) executes in around 2 seconds. Is there any reason why the query planner prefers (1)?

 

----------------------------------- (1) -----------------------------------

 

explain analyze select * from  t1 inner join t2 on t2.orderid = t1.orderid  where t1.currentstatus IN (E'Active') and (t1.parentkey = E'1_2_3' OR t1.parentkey LIKE E'1\\_2\\_3\\_%') order by t1.orderid limit 500 offset 2000;

                                                                                        QUERY PLAN                                                                                     

 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--

 Limit  (cost=61151.75..74956.58 rows=500 width=316) (actual time=48066.053..62264.179 rows=500 loops=1)

   ->  Merge Join  (cost=5932.46..1212391.22 rows=43697 width=316) (actual time=417.461..62262.616 rows=2500 loops=1)

         Merge Cond: (t2.orderid = t1.orderid)

         ->  Index Scan using t1_orderid_creationtime_idx on t1  (cost=0.43..1181104.36 rows=9879754 width=158) (actual time=0.021..60830.724 rows=2416614 loops=1

)

         ->  Sort  (cost=5932.02..6041.26 rows=43697 width=158) (actual time=221.333..225.101 rows=2500 loops=1)

               Sort Key: t1.orderid

               Sort Method: quicksort  Memory: 3573kB

               ->  Bitmap Heap Scan on t1  (cost=59.85..2564.02 rows=43697 width=158) (actual time=11.443..210.783 rows=12005 loops=1)

                     Recheck Cond: ((parentkey = '1_2_3'::text) OR (parentkey ~~ '1\_2\_3\_%'::text))

                     Filter: ((currentstatus = 'Active'::text) AND ((parentkey = '1_2_3'::text) OR (parentkey ~~ '1\_2\_3\_%'::text)))

                     Rows Removed by Filter: 915

                     ->  BitmapOr  (cost=59.85..59.85 rows=649 width=0) (actual time=7.220..7.220 rows=0 loops=1)

                           ->  Bitmap Index Scan on t1_parentkey_idx  (cost=0.00..33.43 rows=649 width=0) (actual time=5.625..5.625 rows=10646 loops=1)

                                 Index Cond: (parentkey = '1_2_3'::text)

                           ->  Bitmap Index Scan on t1_parentkey_idx  (cost=0.00..4.57 rows=1 width=0) (actual time=1.592..1.592 rows=2445 loops=1)

                                 Index Cond: ((parentkey ~>=~ '1_2_3_'::text) AND (parentkey ~<~ '1_2_3`'::text))

 Total runtime: 62265.568 ms

(17 rows)

 

 

----------------------------------- (2) -----------------------------------

 

begin ;

set enable_indexscan=false;

 

explain analyze select * from  t1 inner join t2 on t2.orderid = t1.orderid  where t1.currentstatus IN (E'Active') and (t1.parentkey = E'1_2_3' OR t1.parentkey LIKE E'1\\_2\\_3\\_%') order by t1.orderid limit 500 offset 2000;

 

                                                                           QUERY PLAN                                                                          

----------------------------------------------------------------------------------------------------------------------------------------------------------------

 Limit  (cost=316628.97..316630.22 rows=500 width=316) (actual time=1182.978..1183.196 rows=500 loops=1)

   ->  Sort  (cost=316623.97..316741.70 rows=47094 width=316) (actual time=1182.409..1182.935 rows=2500 loops=1)

         Sort Key: t1.orderid

         Sort Method: top-N heapsort  Memory: 1482kB

         ->  Nested Loop  (cost=64.13..313730.58 rows=47094 width=316) (actual time=17.099..1122.316 rows=12920 loops=1)

               ->  Bitmap Heap Scan on t1  (cost=61.54..2564.10 rows=47094 width=158) (actual time=17.001..501.400 rows=12920 loops=1)

                     Recheck Cond: ((parentkey = '1_2_3'::text) OR (parentkey ~~ '1\_2\_3\_%'::text))

                     Filter: ((parentkey = '1_2_3'::text) OR (parentkey ~~ '1\_2\_3\_%'::text))

                     ->  BitmapOr  (cost=61.54..61.54 rows=649 width=0) (actual time=11.575..11.575 rows=0 loops=1)

                           ->  Bitmap Index Scan on t1_parentkey_idx  (cost=0.00..33.43 rows=649 width=0) (actual time=9.221..9.221 rows=10646 loops=1)

                                 Index Cond: (parentkey = '1_2_3'::text)

                           ->  Bitmap Index Scan on t1_parentkey_idx  (cost=0.00..4.57 rows=1 width=0) (actual time=2.352..2.352 rows=2445 loops=1)

                                 Index Cond: ((parentkey ~>=~ '1_2_3_'::text) AND (parentkey ~<~ '1_2_3`'::text))

               ->  Bitmap Heap Scan on t1  (cost=2.58..6.60 rows=1 width=158) (actual time=0.043..0.043 rows=1 loops=12920)

                     Recheck Cond: (orderid = t1.orderid)

                     ->  Bitmap Index Scan on t1_orderid_creationtime_idx  (cost=0.00..2.58 rows=1 width=0) (actual time=0.038..0.038 rows=1 loops=12920)

                           Index Cond: (orderid = t1.orderid)

 Total runtime: 1184.140 ms

(18 rows)

Looking forward to your help.

 

On Thu, Jun 26, 2014 at 11:07 PM, Karthik Iyer <karthik.i@xxxxxxxxxxx> wrote:

A full dump and restore would definitely help. I tend not to suggest that often because I work with very large databases that are usually extremely cumbersome to dump and restore.

But yeah, if you can get a successful pg_dump from your database, a restore should obviously clean up all of your data and index inconsistencies if you're willing to wait.

Thanks a lot Shaun. Appreciate the help.

- Karthik

--

Kirit Parmar