pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
LIKE should use index when condition doesn't include wildcard
7+ messages / 3 participants
[nested] [flat]

* LIKE should use index when condition doesn't include wildcard
@ 2004-03-30 23:06  Palle Girgensohn <girgen@pingpong.net>
  0 siblings, 1 reply; 7+ messages in thread

From: Palle Girgensohn @ 2004-03-30 23:06 UTC (permalink / raw)
  To: pgsql-performance

Hi,

Shouldn't the optimizer use indices if the like condition does not have any 
wildcards?

An example:

girgen=# explain analyze select * from person where userid = 'girgen';
                                                     QUERY PLAN 

---------------------------------------------------------------------------
------------------------------------------
 Index Scan using person_pkey on person  (cost=0.00..5.98 rows=1 width=84) 
(actual time=0.034..0.039 rows=1 loops=1)
   Index Cond: (userid = 'girgen'::text)
 Total runtime: 0.091 ms
(3 rader)

girgen=# explain analyze select * from person where userid like 'girgen';
                                            QUERY PLAN 

---------------------------------------------------------------------------
-----------------------
 Seq Scan on person  (cost=0.00..77.08 rows=1 width=84) (actual 
time=1.137..1.143 rows=1 loops=1)
   Filter: (userid ~~ 'girgen'::text)
 Total runtime: 1.193 ms
(3 rader)

The result cannot be different between the two cases. The second query does 
not use the index since database is initiaized with a locale, 
sv_SE.ISO8859-1, and I need it for correct sorting. (Still dreaming about 
indices with like and locale)... But, since there is no wildcard in the 
string 'girgen', it should easily be able to use the index, if it only 
bothered to note that there is a wildcard around, right?


Another thing on the same subject:

I use an app that builds searches using some standard method, and it wants 
to always search case-insensitive. Hence, it uses ILIKE instead of `=', 
even for joins, and even for integers. This is a bit lazy, indeed, and also 
wrong. While this is wrong, no doubt,  the odd thing I realized was that 
the optimizer didn't make use of the indices. Same thing here, the 
optimizer should ideally know that it is dealing with integers, where ILIKE 
and LIKE has no meaning, and it should use `=' instead implicitally, hence 
using indices. This one might be kind of low priority, but the one above 
really isn't, IMO.

/Palle




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: LIKE should use index when condition doesn't include wildcard
@ 2004-03-31 00:16  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Palle Girgensohn <girgen@pingpong.net>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2004-03-31 00:16 UTC (permalink / raw)
  To: Palle Girgensohn <girgen@pingpong.net>; +Cc: pgsql-performance

Palle Girgensohn <girgen@pingpong.net> writes:
> Shouldn't the optimizer use indices if the like condition does not have any 
> wildcards?

I can't get excited about this; if you are depending on LIKE to be fast
then you should have locale-insensitive indexes in place to support it.
Switching the tests around so that this special case is supported even
with an index that doesn't otherwise support LIKE would complicate the
code unduly IMHO, to support a rather pointless corner case...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: LIKE should use index when condition doesn't include
@ 2004-03-31 00:28  Palle Girgensohn <girgen@pingpong.net>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Palle Girgensohn @ 2004-03-31 00:28 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance



--On tisdag, mars 30, 2004 19.16.44 -0500 Tom Lane <tgl@sss.pgh.pa.us> 
wrote:

> Palle Girgensohn <girgen@pingpong.net> writes:
>> Shouldn't the optimizer use indices if the like condition does not have
>> any  wildcards?
>
> I can't get excited about this; if you are depending on LIKE to be fast
> then you should have locale-insensitive indexes in place to support it.
> Switching the tests around so that this special case is supported even
> with an index that doesn't otherwise support LIKE would complicate the
> code unduly IMHO, to support a rather pointless corner case...

OK, I agree. Sad, though, that throw away ability to use order by is the 
only way to get index scans using LIKE... :(

But what about ILIKE. It does not take advantage of indices built with 
lower():

girgen=# create index person_foo on person (lower(last_name));
girgen=# vacuum analyze person;
girgen=# explain select * from person where  lower(last_name) = 
'girgensohn';
                                 QUERY PLAN 

---------------------------------------------------------------------------
--
 Index Scan using person_foo on person  (cost=0.00..137.58 rows=78 width=96)
   Index Cond: (lower(last_name) = 'girgensohn'::text)
(2 rows)

girgen=# explain select * from person where  last_name = 'Girgensohn';
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on person  (cost=0.00..441.35 rows=4 width=96)
   Filter: (last_name = 'Girgensohn'::text)
(2 rows)

girgen=# explain select * from person where  lower(last_name) like 
'girgen%';
                                          QUERY PLAN 

---------------------------------------------------------------------------
-------------------
 Index Scan using person_foo on person  (cost=0.00..137.58 rows=78 width=96)
   Index Cond: ((lower(last_name) >= 'girgen'::text) AND (lower(last_name) 
< 'girgeo'::text))
   Filter: (lower(last_name) ~~ 'girgen%'::text)
(3 rows)

girgen=# explain select * from person where  last_name ilike 'girgen%';
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on person  (cost=0.00..441.35 rows=5 width=96)
   Filter: (last_name ~~* 'girgen%'::text)
(2 rows)


postgresql 7.4.2, freebsd 4.9 stable.


/Palle




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: LIKE should use index when condition doesn't include
@ 2004-03-31 00:56  Josh Berkus <josh@agliodbs.com>
  parent: Palle Girgensohn <girgen@pingpong.net>
  0 siblings, 2 replies; 7+ messages in thread

From: Josh Berkus @ 2004-03-31 00:56 UTC (permalink / raw)
  To: Palle Girgensohn <girgen@pingpong.net>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Palle,

> But what about ILIKE. It does not take advantage of indices built with 
> lower():

Nope.  If you want to use a functional index, you'll need to use the function 
when you call the query.   ILIKE is not somehow aware that it is equivalent 
to lower().

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: LIKE should use index when condition doesn't include
@ 2004-03-31 01:04  Palle Girgensohn <girgen@pingpong.net>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Palle Girgensohn @ 2004-03-31 01:04 UTC (permalink / raw)
  To: josh@agliodbs.com, Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

--On tisdag, mars 30, 2004 16.56.09 -0800 Josh Berkus <josh@agliodbs.com> 
wrote:

> Palle,
>
>> But what about ILIKE. It does not take advantage of indices built with
>> lower():
>
> Nope.  If you want to use a functional index, you'll need to use the
> function  when you call the query.   ILIKE is not somehow aware that it
> is equivalent  to lower().

Too bad... that was my idea, that it would somehow be aware that it is 
equivalent to lower() like. It really is, isn't it? I would have though 
they where synonymous. If not, makes ILIKE kind of unusable, at least 
unless you're pretty certain the field will never indexed.

/Palle




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: LIKE should use index when condition doesn't include
@ 2004-03-31 01:06  Josh Berkus <josh@agliodbs.com>
  parent: Palle Girgensohn <girgen@pingpong.net>
  0 siblings, 0 replies; 7+ messages in thread

From: Josh Berkus @ 2004-03-31 01:06 UTC (permalink / raw)
  To: Palle Girgensohn <girgen@pingpong.net>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Palle,

> Too bad... that was my idea, that it would somehow be aware that it is 
> equivalent to lower() like. It really is, isn't it? I would have though 
> they where synonymous. If not, makes ILIKE kind of unusable, at least 
> unless you're pretty certain the field will never indexed.

Yup.   I use it mostly for lookups in reference lists with < 100 items, where 
an index doesn't matter.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: LIKE should use index when condition doesn't include
@ 2004-03-31 05:33  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Tom Lane @ 2004-03-31 05:33 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: Palle Girgensohn <girgen@pingpong.net>; pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
> ILIKE is not somehow aware that it is equivalent to lower().

Is it?  Given the wild and wonderful behaviors of locales here and
there, I wouldn't want to assume that such an equivalence holds.

In particular I note that iclike() seems to be multibyte-aware while
lower() definitely is not.  Even if that's just a bug, it's a big leap
to assume that ILIKE is equivalent to LIKE on lower().  Think about
Turkish i/I, German esstet (did I spell that right?), ch in various
languages, etc etc.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2004-03-31 05:33 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-30 23:06 LIKE should use index when condition doesn't include wildcard Palle Girgensohn <girgen@pingpong.net>
2004-03-31 00:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-31 00:28   ` Re: LIKE should use index when condition doesn't include Palle Girgensohn <girgen@pingpong.net>
2004-03-31 00:56     ` Re: LIKE should use index when condition doesn't include Josh Berkus <josh@agliodbs.com>
2004-03-31 01:04       ` Re: LIKE should use index when condition doesn't include Palle Girgensohn <girgen@pingpong.net>
2004-03-31 01:06         ` Re: LIKE should use index when condition doesn't include Josh Berkus <josh@agliodbs.com>
2004-03-31 05:33       ` Re: LIKE should use index when condition doesn't include Tom Lane <tgl@sss.pgh.pa.us>

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