LIKE query verses = - Mailing list pgsql-performance
| From | Karthikeyan Mahadevan |
|---|---|
| Subject | LIKE query verses = |
| Date | |
| Msg-id | OF2E7129A7.60680FDA-ON65257346.00445AD3-65257346.0044EA98@in.ibm.com Whole thread Raw |
| Responses |
Re: LIKE query verses =
|
| List | 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@in.ibm.com
"Doesn't expecting the unexpected make the unexpected become the expected? "
----------------------------------------------------------------------------
pgsql-performance by date: