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 <karthi...@directi.com>
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
