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

LIKE query verses =

From: Karthikeyan Mahadevan <karthikeyan(dot)mahadevan(at)in(dot)ibm(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: LIKE query verses =
Date: 2007-08-29 12:31:43
Message-ID: OF2E7129A7.60680FDA-ON65257346.00445AD3-65257346.0044EA98@in.ibm.com (view raw or flat)
Thread:
Lists: pgsql-performance
*************************************************************************************************************************
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(at)in(dot)ibm(dot)com 
"Doesn't expecting the unexpected make the unexpected become the expected? 
" 
---------------------------------------------------------------------------- 

Responses

pgsql-performance by date

Next:From: Jens ReufsteckDate: 2007-08-29 14:33:14
Subject: Performance issue with nested loop
Previous:From: Jens ReufsteckDate: 2007-08-29 10:15:49
Subject: Performance issue with nested loop

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