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)
