Skip site navigation (1) Skip section navigation (2)

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

From: Richard Yen <dba(at)richyen(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: query plan with index having a btrim is different for strings of different length
Date: 2008-12-10 22:04:57
Message-ID: 129E8D79-341B-411A-83B7-E2B509A7B6E2@richyen.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Vladimir SitnikovDate: 2008-12-10 22:36:51
Subject: Re: query plan with index having a btrim is different for strings of different length
Previous:From: Richard YenDate: 2008-12-10 21:54:50
Subject: Re: query plan with index having a btrim is different for strings of different length

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group