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

Re: Is is possible to persuade the query planner that ~* operations are grossly expensive?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: n(dot)howden(at)eris(dot)qinetiq(dot)com
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Is is possible to persuade the query planner that ~* operations are grossly expensive?
Date: 2002-12-17 14:23:36
Message-ID: 6534.1040135016@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-admin
Nick Howden <n(dot)howden(at)eris(dot)qinetiq(dot)com> writes:
> Is is possible to persuade the query planner that ~* operations are grossly 
> expensive - or at least much more expensive than some other operations.

I think you're trying to micro-optimize in the wrong place.

> My reason is that I have a databsase on which we do a lot of queries of the 
> form

> SELECT
>  * 
> FROM 
>  primary_table 
>  secondary_table as t1
>  secondary_table as t2

> WHERE primary_table.id = t1.id
> AND  primary_table.id = t2.id

> AND t1.col1 = 1
> AND t2.col1 = 2
> AND primary_table.blob ~* 'wibble';

I get perfectly reasonable-looking plans for a comparable query
structure in the regression database:

regression=# explain select * from tenk1 a, tenk1 b where
regression-# a.unique1 = b.unique1 and b.unique2 = 42
regression-# and a.stringu1 like '%z%';
                                     QUERY PLAN

-------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..9.04 rows=1 width=488)
   ->  Index Scan using tenk1_unique2 on tenk1 b  (cost=0.00..3.01 rows=1 width=244)
         Index Cond: (unique2 = 42)
   ->  Index Scan using tenk1_unique1 on tenk1 a  (cost=0.00..6.01 rows=1 width=244)
         Index Cond: (a.unique1 = "outer".unique1)
         Filter: (stringu1 ~~ '%z%'::text)
(6 rows)

I don't see much to improve on there in terms of reducing the number of
executions of the LIKE operator.

How about you show us your schema (column types, index definitions) and
your EXPLAIN ANALYZE output, rather than jumping to conclusions about
the form of a solution to your problem?

			regards, tom lane

In response to

pgsql-admin by date

Next:From: Rajesh Kumar Mallah.Date: 2002-12-17 15:50:17
Subject: Is it harmless?
Previous:From: Nick HowdenDate: 2002-12-17 09:22:17
Subject: Is is possible to persuade the query planner that ~* operations are grossly expensive?

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