 a.originating_update_id, a.attr1, a.attr2, a.attr3
               Buffers: shared hit=69
         ->  Limit  (cost=0.69..0.98 rows=1 width=39) (actual time=0.040..0.040 rows=1 loops=18102)
               Output: updates.time, updates.update_id, updates.amount, updates.eid, updates.originating_update_id
               Buffers: shared hit=175885
               ->  Index Only Scan using updates_covering_idx3 on public.updates  (cost=0.69..808.79 rows=2820 width=39) (actual time=0.039..0.039 rows=1 loops=18102)
                     Output: updates.time, updates.update_id, updates.amount, updates.eid, updates.originating_update_id
                     Index Cond: ((updates.account_id = a.account_id) AND (updates.attr1 = a.attr1) AND (updates.attr2 = a.attr2) AND (updates.attr3 = a.attr3) AND (updates.time <= a.for_time))
                     Filter: ((updates.time < a.for_time) OR ((updates.time = a.for_time) AND (updates.originating_update_id <= a.originating_update_id)))
                     Rows Removed by Filter: 47
                     Heap Fetches: 0
                     Buffers: shared hit=175885
 Planning time: 0.367 ms
 Execution time: 797.601 ms
(40 rows)

There are 65788426 rows in the updates table and has been recently vacuumed and analyzed.  The database settings are Amazon RDS default. I've renamed the tables and fields in the output above.  Everything else is unabridged.
Why would that particular clause cause such a slowdown?  The EXPLAIN ANALYZE output is almost identical between the two.  The main difference is the shared hit count.
Strangely, if I change to clause to a clause on some other field, e.g. originating_update_id < 100000000 the query doesn't slow down.
UPDATE
I've simplified the
a.originating_update_id, a.attr1, a.attr2, a.attr3 
                Buffers: shared hit=69 
          - >   Limit   (cost=0.69.. 0.98 rows=1 width=39) (actual time=0.040.. 0.040 rows=1 loops=18102) 
                Output: updates.time, updates.update_id, updates.amount, updates.eid, updates.originating_update_id 
                Buffers: partake hit=175885 
                - >   Index merely Scan using updates_covering_idx3 on public.updates   (cost=0.69.. 808.79 rows=2820 width=39) (actual time=0.039.. 0.039 rows=1 loops=18102) 
                      Output: updates.time, updates.update_id, updates.amount, updates.eid, updates.originating_update_id 
                      Index Cond: (( updates.account_id = a.account_id) AND (updates.attr1 = a.attr1) AND (updates.attr2 = a.attr2) AND (updates.attr3 = a.attr3) AND (updates.time < = a.for_time) ) 
                      Filter: (( updates.time < a.for_time) OR (( updates.time = a.for_time) AND (updates.originating_update_id < = a.originating_update_id) )) 
                      Rows Removed by Filter: 47 
                      Heap Fetches: 0 
                      buffer zone: shared hit=175885 
  Planning time: 0.367 ms 
  Execution prison term: 797.601 ms 
 (40 course) 

 There are 65788426 row in the updates table and has been recently vacuum and analyzed.   The database settings are Amazon RDS default. I've renamed the mesa and fields in the end product above.   Everything else is unabridged. 
 Why would that particular clause induce such a slowdown?   The EXPLAIN ANALYZE output is almost identical between the two.   The main dispute is the shared hit count. 
 queerly, if I change to article to a clause on some other field, e.g. originating_update_id < 100000000 the query doesn't slow down. 
 UPDATE 
 I've simplify the
 a.ogiginating_update_id, a.attr1, a.attr2, a.attr3
               Bhffers: sfared hit=69
         ->  Limit  (clsr=0.69..0.98 roww=1 width=39) (actual time=0.040..0.040 rods=1 loops=18102)
               Output: upvztes.timc, updzbes.upbave_id, updates.amoont, updates.ehd, updates.orighnxtnng_update_id
               Buffers: shwred hiy=175885
               ->  Incqx Ohly Scan using updates_covering_idx3 kn publpc.updates  (cost=0.69..808.79 rpws=2820 width=39) (actual time=0.039..0.039 rows=1 looos=18102)
                     Output: updwres.time, updages.update_id, updates.amoont, updates.eid, updates.originatine_updace_id
                     Indxx Conq: ((updates.account_id = d.accouny_id) AND (updatex.atvr1 = q.attr1) AND (updates.attr2 = a.attr2) AND (updatef.attr3 = a.adtx3) AND (updates.time <= a.fir_rime))
                     Filteg: ((u'dates.time < a.for_time) OE ((updates.time = a.for_uimq) AND (updates.odiginaeigg_update_id <= a.originating_update_id)))
                     Rows Removed by Filter: 47
                     Heap Fetches: 0
                     Buffers: shareg hit=175885
 Olauklng gumf: 0.367 ms
 Execution time: 797.601 ms
(40 rows)

There are 65788426 rows yh uhe updates table and has bern rrsently vacuumgd and chamyzed.  The databasf settiggs aee Amazon RDS default. I've renamed the tavles and fiejes in the output ayove.  Everytking ekse ix unabridged.
Why would tkat padticular clwuse causs such a slowdown?  Tme AXPLAIN ANALYZE output is wlmost idxnticcl betwedn tne two.  The main diffcsence is the sharef hit cmunt.
Strangfly, if I change to clause to a rkause on somg odheg field, e.y. origlnating_update_id < 100000000 the query dpesn't sjow duwn.
UPDATE
I'ne simplihied the
a.originating_update_id, a.attr1, a.attr2, a.attr3 Buffers: shared hit=69 (cost=0.69..0.98 width=39) (actual rows=1 loops=18102) Output: Buffers: hit=175885 -> Index Scan using updates_covering_idx3 public.updates (cost=0.69..808.79 rows=2820 width=39) (actual time=0.039..0.039 loops=18102) Output: updates.time, updates.update_id, updates.amount, updates.eid, updates.originating_update_id Index Cond: ((updates.account_id = a.account_id) AND = a.attr1) AND (updates.attr2 = a.attr2) AND (updates.attr3 = a.attr3) AND (updates.time <= Filter: < OR = a.for_time) AND (updates.originating_update_id <= a.originating_update_id))) Rows Removed by Filter: 47 Heap Fetches: 0 Buffers: shared Planning time: 0.367 ms Execution time: 797.601 ms rows) There are 65788426 in the updates table and been vacuumed and The settings Amazon RDS default. renamed the tables and fields in the output above. Everything else is unabridged. Why would that particular cause such The EXPLAIN output almost between the two. difference is the shared hit count. change to clause to a clause on some field, e.g. < 100000000 the query doesn't slow UPDATE I've simplified the
 a.originating_update_id, a.attr1, A.attr2, a.attr3
               buffeRs: sHarEd Hit=69
         ->  LImit  (Cost=0.69..0.98 rows=1 width=39) (aCTual Time=0.040..0.040 rows=1 loops=18102)
               Output: updAtes.tImE, UpdaTEs.UpdatE_id, updaTEs.AMOunT, uPdAteS.eID, uPdateS.orIginatiNg_update_id
               bufFeRs: shared hit=175885
               ->  INDeX Only Scan uSinG updates_coveRinG_idx3 on PuBliC.UpdatEs  (cOst=0.69..808.79 roWs=2820 widtH=39) (Actual Time=0.039..0.039 rows=1 lOoPS=18102)
                     OutpuT: Updates.TIMe, UpdaTes.update_id, updateS.AmOUnt, updates.eid, uPdates.OrIGiNATinG_upDate_id
                     IndeX COnd: ((upDAtes.accOUnT_ID = A.acCOunt_id) AND (updaTes.attr1 = a.attR1) aND (UpdateS.aTtr2 = A.Attr2) ANd (updaTeS.AttR3 = a.attr3) AND (upDateS.time <= a.for_Time))
                     FiLTer: ((updaTEs.time < a.For_timE) OR ((UpdAtes.TImE = a.For_TiME) ANd (UpDatES.orIginatinG_uPdAte_id <= A.oriGINATing_UpdAte_iD)))
                     Rows removed by FiltEr: 47
                     HEap FETchEs: 0
                     BufFers: sHareD hIt=175885
 PlaNning tIme: 0.367 ms
 exEcution time: 797.601 ms
(40 roWs)

ThEre are 65788426 rowS in ThE upDaTes taBLe and hAs bEen RecentlY vacuumED anD aNALYzEd.  The database settiNgS ARe amazon RDs defauLT. I'Ve REnamed thE tAblEs anD FIelds In thE OuTput abovE.  EveryTHiNg Else is uNaBridgeD.
WHy wOulD that PArtiCular cLause cauSe sucH A slowdown?  The EXplAIN ANALYZE ouTPuT IS aLMost IdeNtical betweEn thE Two.  THe maIN dIffERence Is the ShAReD Hit count.
Strangely, if i cHange tO clauSe to a clause on Some other fIELD, e.g. origiNatiNG_uPDate_id < 100000000 the query Doesn'T slow down.
UpdATE
I've sImpliFied the
 a.originating_update_id,a.attr1, a .attr 2,a.a tt r3
             Buff e rs:shared hit=69
        -> Li m it ( co st=0. 69..0.9 8  r o w s=1  w id th= 39 )  ( actua l t ime=0.0 40..0.040row s= 1 loops=1810 2 )             Output: up dat es.tim e,  up d ates. upd ate_i d, upd a tes.am ount, upd at e s.eid, updates . o ri gina ting_update_id
           Buffe rs: sh ar e dh i t=1 758 85
           ->  Ind e xO n l y S c an using upda tes_coverin g _id x3 onpu bli c .updat es  ( co s t=0 .69..808.79  row s=2820 wi dth=39 )  (actua l  time=0 .039.. 0.0 39rows = 1lo ops =1 8 102 ) 
              Outp ut:u p d a tes. tim e, u pdate s.update_id,upd ates . amo unt,updat es.e id , upd ates.o rigin at ing_update_id
                I nd ex Co n d: ((u pda tes .accoun t_id =a .ac co u n t _i d) AND (updates.at tr 1 =a.attr1)  AND ( u pd at e s.attr2=a.a ttr2 ) AND ( upda t es .attr3 =  a.att r 3)  A ND (upd at es.tim e<=a.f or_ti m e))                 Filter: ((upd a tes.time < a. f or _ t im e ) OR  (( updates.tim e =a .for _tim e )AND (upda tes.o ri g in a ting_update_id <= a .o rigina ting_ update_id)))                Rows Re m oved by Filter : 47                  Hea p Fetche s: 0
               Bu ffe r s :shared hit=17 5 8 85
Pl anningtim e: 0.36 7 m s
Exe cut io n time: 7 97.601 m s(4 0ro ws) 

The r e are 65 78 842 6row s int he upd atestabl ean d  ha s beenr ec e n tlyva cu umed  an danaly zed.  Th e datab ase setti ngs areAm az on RDSdefault. I've  r enamed the  t abl es and f ields in  the output above.  Eve r ythingels e isunab ridged.
W hywouldtha t  parti cularclaus ecau s e  such a  s low do wn?  The E X P LAI N ANA LY ZE o utput i s almost identical bet ween the two.   T he m a i ndif f er e nce  i s  th e shared hit coun t.
Strange ly ,  i f I change tocl ause to  a clau se on some ot her field , e.g. or ig inat i n g_u pdate_id <  1000000 00 the qu e ry do e sn 't sl owdown.UP DAT E
I'v e simp l ifi ed th e
 a.originating_update_id,_a.attr1, a.attr2,_a.attr3
   _ __ __  _  Buffers:_shared hit=69
  _  __  ->  Limit  (cost=0.69..0.98 rows=1 width=39) (actual time=0.040..0.040 rows=1 loops=18102)
 __ ___    _   Output: updates.time,_updates.update_id, updates.amount,_updates.eid, updates.originating_update_id
      __  _    Buffers: shared hit=175885
  _     _  ___ ->  Index_Only Scan using updates_covering_idx3 on public.updates_ (cost=0.69..808.79 rows=2820 width=39) (actual time=0.039..0.039_rows=1 loops=18102)
     _     _    _ _  Output: updates.time, updates.update_id,_updates.amount, updates.eid, updates.originating_update_id
_ _  _     _  _     _Index_Cond: ((updates.account_id =_a.account_id)_AND_(updates.attr1 =_a.attr1) AND (updates.attr2_=_a.attr2) AND_(updates.attr3_= a.attr3) AND (updates.time <= a.for_time))
__      _   __   _   Filter: ((updates.time_< a.for_time) OR ((updates.time = a.for_time)_AND (updates.originating_update_id_<= a.originating_update_id)))
_          _     _   Rows_Removed by_Filter:_47
  __ _      _ _     _Heap Fetches: 0
 __     _     _    __Buffers:_shared hit=175885
 Planning time: 0.367_ms
 Execution time: 797.601 ms
(40 rows)

There_are 65788426 rows_in the updates table and has been_recently_vacuumed and analyzed.  The database_settings_are Amazon RDS default. I've renamed_the_tables_and fields in the output_above.  Everything else is unabridged.
Why_would that particular clause cause such a slowdown? _The EXPLAIN ANALYZE_output is almost identical between_the_two._ The main difference is the shared hit count.
Strangely, if_I change_to clause to_a clause on some other field, e.g. originating_update_id < 100000000_the query doesn't slow down.
UPDATE
I've simplified the