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

Re: How to use index in strpos function

From: "Adam Rich" <adam(dot)r(at)sbcglobal(dot)net>
To: <pgsql-general(at)postgresql(dot)org>
Cc: "'Tuan Hoang Anh'" <hatuan05(at)gmail(dot)com>
Subject: Re: How to use index in strpos function
Date: 2008-12-31 05:11:01
Message-ID: 046801c96b06$2cb14280$8613c780$ (view raw, whole thread or download thread mbox)
Lists: pgsql-general

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Tuan Hoang Anh
> Sent: Tuesday, December 30, 2008 10:49 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] How to use index in strpos function
> I have table command
> CREATE TABLE command
> And one index
> CREATE INDEX command_command
>   ON command
>   USING btree(upper(command));
> And have a query use it
> explain select * from command where strpos('APCTPN1.EXE PN1',
> UPPER(command)) > 0 AND UPPER(command) <> ''
> "Seq Scan on command  (cost=100000000.00..100000015.26 rows=92
> width=200)"
>     "  Filter: ((upper((command)::text) <> ''::text) AND
> (strpos('APCTPN1.EXE PN1'::text, upper((command)::text)) > 0))"
> This command is called a lot, so i want to use index in it. How to use
> index on this command

Is the first argument to strops always the same ('APCTPN1.EXE PN1') ?
if so, you can create an index like this:

 CREATE INDEX strpos_command
   ON command
   USING (strpos('APCTPN1.EXE PN1', UPPER(command.command)))

However, if the argument is different for each query, then you will
not be able to utilize an functional index for this type of query.

If you mean to query for "starts with" you can rewrite your query as:

select * from command 
where UPPER(command.command) LIKE 'APCTPN1.EXE PN1%' 

However, if you mean to query for "contains substring" then a regular
index or functional index will not help.  A full-text index might help,
but it is more complex to setup and use.  The documentation for that
is here:

In response to

pgsql-general by date

Next:From: Reg Me PleaseDate: 2008-12-31 08:39:59
Subject: Re: [PGSQL 8.3.5] Use of a partial indexes
Previous:From: Tuan Hoang AnhDate: 2008-12-31 04:49:05
Subject: How to use index in strpos function

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