Re: query plan with index having a btrim is different for strings of different length

On Dec 10, 2008, at 11:39 AM, Robert Haas wrote:

>> You guys are right. I tried "Miller" and gave me the same result.
>> Is there
>> any way to tune this so that for the common last names, the query
>> run time
>> doesn't jump from <1s to >300s?
>> Thanks for the help!
>
> Can you send the output of EXPLAIN ANALYZE for both cases?

tii=# explain analyze SELECT m_object_paper.id FROM m_object_paper,
m_assignment WHERE m_object_paper.assignment = m_assignment.id AND
m_object_paper.owner=-1 AND m_assignment.class = 2450798 AND
lower(btrim(x_firstname)) = lower(btrim('Jordan')) and
lower(btrim(x_lastname)) = lower(btrim('Smithers'));

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..10141.07 rows=1 width=4) (actual
time=33.004..33.004 rows=0 loops=1)
-> Index Scan using last_name_fnc_idx on m_object_paper
(cost=0.00..10114.25 rows=11 width=8) (actual time=33.003..33.003
rows=0 loops=1)
Index Cond: (lower(btrim((x_lastname)::text)) =
'smithers'::text)
Filter: ((owner = (-1)) AND
(lower(btrim((x_firstname)::text)) = 'jordan'::text))
-> Index Scan using m_assignment_pkey on m_assignment
(cost=0.00..2.43 rows=1 width=4) (never executed)
Index Cond: (m_assignment.id = m_object_paper.assignment)
Filter: (m_assignment.class = 2450798)
Total runtime: 33.070 ms
(8 rows)

tii=# explain analyze SELECT m_object_paper.id FROM m_object_paper,
m_assignment WHERE m_object_paper.assignment = m_assignment.id AND
m_object_paper.owner=-1 AND m_assignment.class = 2450798 AND
lower(btrim(x_firstname)) = lower(btrim('Jordan')) and
lower(btrim(x_lastname)) = lower(btrim('Smith'));
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=181867.87..291714.78 rows=1 width=4) (actual
time=124746.426..139252.850 rows=1 loops=1)
Hash Cond: (m_object_paper.assignment = m_assignment.id)
-> Bitmap Heap Scan on m_object_paper (cost=181687.88..291532.67
rows=562 width=8) (actual time=124672.328..139248.919 rows=58 loops=1)
Recheck Cond: ((lower(btrim((x_lastname)::text)) =
'smith'::text) AND (owner = (-1)))
Filter: (lower(btrim((x_firstname)::text)) = 'jordan'::text)
-> BitmapAnd (cost=181687.88..181687.88 rows=112429
width=0) (actual time=124245.890..124245.890 rows=0 loops=1)
-> Bitmap Index Scan on last_name_fnc_idx
(cost=0.00..5476.30 rows=496740 width=0) (actual
time=16194.803..16194.803 rows=521382 loops=1)
Index Cond: (lower(btrim((x_lastname)::text)) =
'smith'::text)
-> Bitmap Index Scan on m_object_paper_owner_idx
(cost=0.00..176211.04 rows=16061244 width=0) (actual
time=107928.054..107928.054 rows=15494737 loops=1)
Index Cond: (owner = (-1))
-> Hash (cost=177.82..177.82 rows=174 width=4) (actual
time=3.764..3.764 rows=5 loops=1)
-> Index Scan using m_assignment_class_idx on m_assignment
(cost=0.00..177.82 rows=174 width=4) (actual time=0.039..3.756 rows=5
loops=1)
Index Cond: (class = 2450798)
Total runtime: 139255.109 ms
(14 rows)

This example doesn't have a > 300s run time, but there are a few in my
log that are. In either case, I guess you get the picture.

Thanks for the help!
--Richard