From: Mark Lewis <mark.lewis@mir3.com>
To: Karthikeyan Mahadevan <karthikeyan.mahadevan@in.ibm.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: LIKE query verses =
Date: Wed, 29 Aug 2007 10:12:31 -0700
Message-ID: <1188407551.22730.113.camel@archimedes> (raw)
In-Reply-To: <OF2E7129A7.60680FDA-ON65257346.00445AD3-65257346.0044EA98@in.ibm.com>
References: <OF2E7129A7.60680FDA-ON65257346.00445AD3-65257346.0044EA98@in.ibm.com>
On Wed, 2007-08-29 at 18:01 +0530, Karthikeyan Mahadevan wrote:
>
> *************************************************************************************************************************
> 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
> ------------------------------------------------------------------------------------------------------------------------------------------------------
Remember that using LIKE causes PG to interpret an underscore as 'any
character', which means that it can only scan the index for all records
that start with 'reporting', and then it needs to apply a filter to each
match. This is going to be slower than just going directly to the
matching index entry.
What you probably want to do is tell PG that you're looking for a
literal underscore and not for any matching character by escaping the
underscore, that will allow it to do a much quicker index scan.
Something like:
cat.name like 'reporting|_group.Tier2%' ESCAPE '|'
-- Mark Lewis
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark.lewis@mir3.com, karthikeyan.mahadevan@in.ibm.com
Subject: Re: LIKE query verses =
In-Reply-To: <1188407551.22730.113.camel@archimedes>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox