slow query - Mailing list pgsql-general

Hey Folks,

This query is running really slowly.  Sometimes much slower then others.  I have a feeling that there may be contention on one of the indices it is using.

Query and explain plan are below.  Seems like it spend the most time doing
Index Scan using i_tablea_atextfield on tablea ru  (cost=0.00..2265.28 rows=2 width=12) (actual time=0.624..881.313 rows=228 loops=1)

Any suggestions?

SELECT z.atextfield,    
       z.btextfield,    
       z.abigintfield,    
       p.achar255field, p.ptextfield,    
       z.achar1field,     u.aboolfield,    
       z.textfield1,    
       z.achar8field,    
       z.achar16field  
FROM tablea ru   
INNER JOIN tableb u ON ( u.atextfield = ru.anothertextfield )   
INNER JOIN tablec z ON u.atextfield = z.atextfield   
INNER JOIN tabled p ON p.id = z.pid   
LEFT JOIN tablee m ON u.atextfield = m.atextfield  AND m.boolcol5
WHERE ru.atextfield = 'thelookupval'  
  AND u.boolcol1 IS TRUE  
  AND u.boolcol2 IS FALSE  
  AND ru.achar1field <> 'N'  
  AND ru.boolcol3 IS FALSE  
  AND NOT EXISTS ( SELECT 'x' FROM tablea fru WHERE fru.atextfield = ru.anothertextfield AND fru.boolcol3 IS TRUE )
  AND EXISTS ( SELECT 'x' FROM tablef s WHERE s.atextfield = ru.atextfield AND s.boolcol4 IS TRUE )
  ORDER by ru.anothertextfield asc



Sort  (cost=2341.96..2341.97 rows=2 width=146) (actual time=1118.810..1119.098 rows=228 loops=1)
  Sort Key: ru.anothertextfield
  ->  Nested Loop Left Join  (cost=0.00..2341.95 rows=2 width=146) (actual time= 0.930..1117.258 rows=228 loops=1)
        ->  Nested Loop  (cost=0.00..2313.36 rows=2 width=131) (actual time=0.842..914.554 rows=228 loops=1)
              ->  Nested Loop  (cost=0.00..2296.65 rows=2 width=93) (actual time= 0.765..901.916 rows=228 loops=1)
                    ->  Nested Loop  (cost=0.00..2281.98 rows=2 width=72) (actual time=0.690..893.648 rows=228 loops=1)
                          ->  Index Scan using i_tablea_atextfield on tablea ru  (cost= 0.00..2265.28 rows=2 width=12) (actual time=0.624..881.313 rows=228 loops=1)
                                Index Cond: (atextfield = 'thelookupval'::text)
                                Filter: ((achar1field <> 'N'::bpchar) AND (boolcol3 IS FALSE) AND (NOT (subplan)) AND (subplan))
                                SubPlan
                                  ->  Index Scan using tablef_pkey on tablef s  (cost=0.00..8.34 rows=1 width=0) (actual time=0.016..0.016 rows=1 loops=228)
                                        Index Cond: (atextfield = $1)
                                        Filter: (boolcol4 IS TRUE)
                                  ->  Bitmap Heap Scan on tablea fru  (cost=4.61..90.41 rows=1 width=0) (actual time=3.590..3.590 rows=0 loops=243)
                                        Recheck Cond: (atextfield = $0)
                                        Filter: (boolcol3 IS TRUE)
                                        ->  Bitmap Index Scan on i_tablea_atextfield  (cost= 0.00..4.61 rows=22 width=0) (actual time=0.044..0.044 rows=17 loops=243)
                                              Index Cond: (atextfield = $0)
                          ->  Index Scan using tablec_pkey on tablec z  (cost= 0.00..8.34 rows=1 width=60) (actual time=0.047..0.049 rows=1 loops=228)
                                Index Cond: (z.atextfield = ru.anothertextfield)
                    ->  Index Scan using tabled_pkey on tabled p  (cost= 0.00..7.32 rows=1 width=29) (actual time=0.030..0.031 rows=1 loops=228)
                          Index Cond: (p.id = z.pid)
              ->  Index Scan using tableb_pkey on tableb u  (cost= 0.00..8.34 rows=1 width=38) (actual time=0.049..0.051 rows=1 loops=228)
                    Index Cond: (u.atextfield = ru.anothertextfield)
                    Filter: ((boolcol1 IS TRUE) AND (boolcol2" IS FALSE))"
        ->  Index Scan using tablee_atextfield_idx on tablee m  (cost=0.00..14.28 rows=1 width=39) (actual time=0.883..0.883 rows=0 loops=228)
              Index Cond: (u.atextfield = m.atextfield)