LIKE query verses =

[Karthikeyan Mahadevan <karthikeyan.mahadevan@xxxxxxxxxx>]

*************************************************************************************************************************
1)  

EXPLAIN ANALYSE SELECT job_category.job_id,job.name,job.state,job.build_id,cat.name
as reporting_group
FROM category,job_category,job,category
as cat
WHERE job.job_id=job_category.job_id
AND job_category.category_id=category.category_id
AND cat.build_id=category.build_id
AND category.name = 'build_id.pap3260-20070828_01'
AND cat.name like ('reporting_group.Tier2%');
           
                     
                     
              QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..291.53
rows=8 width=103) (actual time=98.999..385.590 rows=100 loops=1)
   ->  Nested Loop
 (cost=0.00..250.12 rows=9 width=34) (actual time=98.854..381.106
rows=100 loops=1)
         ->
 Nested Loop  (cost=0.00..123.22 rows=1 width=34) (actual time=98.717..380.185
rows=1 loops=1)
           
   ->  Index Scan using idx_cat_by_name on category cat
 (cost=0.00..5.97 rows=1 width=34) (actual time=95.834..245.276 rows=977
loops=1)
           
         Index Cond: (((name)::text >= 'reporting'::character
varying) AND ((name)::text < 'reportinh'::character varying))
           
         Filter: ((name)::text ~~ 'reporting_group.Tier2%'::text)
           
   ->  Index Scan using idx_cat_by_bld_id on category
 (cost=0.00..117.24 rows=1 width=8) (actual time=0.134..0.134 rows=0
loops=977)
           
         Index Cond: ("outer".build_id
= category.build_id)
           
         Filter: ((name)::text = 'build_id.pap3260-20070828_01'::text)
         ->
 Index Scan using idx_jcat_by_cat_id on job_category  (cost=0.00..126.00
rows=71 width=8) (actual time=0.126..0.569 rows=100 loops=1)
           
   Index Cond: (job_category.category_id = "outer".category_id)
   ->  Index Scan
using job_pkey on job  (cost=0.00..4.59 rows=1 width=73) (actual time=0.033..0.036
rows=1 loops=100)
         Index
Cond: (job.job_id = "outer".job_id)

 Total runtime: 385.882 ms
------------------------------------------------------------------------------------------------------------------------------------------------------



 but , if   I use  AND
cat.name = 'reporting_group.Tier2' ;       

*************************************************************************************************************************
2)

EXPLAIN ANALYSE SELECT job_category.job_id,job.name,job.state,job.build_id,cat.name
as reporting_group
FROM category,job_category,job,category
as cat
WHERE job.job_id=job_category.job_id
AND job_category.category_id=category.category_id
AND cat.build_id=category.build_id
AND category.name = 'build_id.pap3260-20070828_01'
AND cat.name = 'reporting_group.Tier2'
;
           
                     
                     
                   QUERY
PLAN                    
                     
                     
       
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=8186.96..26124.40
rows=796 width=103) (actual time=40.584..48.966 rows=100 loops=1)
   ->  Nested Loop
 (cost=8186.96..21776.35 rows=945 width=34) (actual time=40.445..41.437
rows=100 loops=1)
         ->
 Merge Join  (cost=8186.96..8198.88 rows=107 width=34) (actual
time=40.290..40.303 rows=1 loops=1)
           
   Merge Cond: ("outer".build_id = "inner".build_id)
           
   ->  Sort  (cost=4093.48..4096.19 rows=1085 width=8)
(actual time=0.206..0.211 rows=3 loops=1)
           
         Sort Key: category.build_id
           
         ->  Index Scan using idx_cat_by_name
on category  (cost=0.00..4038.78 rows=1085 width=8) (actual time=0.130..0.183
rows=3 loops=1)
           
               Index Cond: ((name)::text
= 'build_id.pap3260-20070828_01'::text)
           
   ->  Sort  (cost=4093.48..4096.19 rows=1085 width=34)
(actual time=37.424..38.591 rows=956 loops=1)
           
         Sort Key: cat.build_id
           
         ->  Index Scan using idx_cat_by_name
on category cat  (cost=0.00..4038.78 rows=1085 width=34) (actual time=0.076..34.328
rows=962 loops=1)
           
               Index Cond: ((name)::text
= 'reporting_group.Tier2'::text)
         ->
 Index Scan using idx_jcat_by_cat_id on job_category  (cost=0.00..126.00
rows=71 width=8) (actual time=0.139..0.743 rows=100 loops=1)
           
   Index Cond: (job_category.category_id = "outer".category_id)
   ->  Index Scan
using job_pkey on job  (cost=0.00..4.59 rows=1 width=73) (actual time=0.063..0.066
rows=1 loops=100)
         Index
Cond: (job.job_id = "outer".job_id)
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Total runtime: 49.453 ms

How to increase the performance of the
 first query ? 



















Thank you !
Regards, 
Karthi 
------------------------------------------------------------------- 
Karthikeyan Mahadevan
Java Technology Center
IBM Software Labs ,Bangalore, India.
Phone: +91 80 2504 4000 or 2509 4000 Ext: 2413 
Direct : +91 80 25094413 
Email : kamahade@xxxxxxxxxx 
"Doesn't expecting the unexpected make the unexpected become the expected?
" 
----------------------------------------------------------------------------