pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
is it possible to get the optimizer to use indexes with a like clause
11+ messages / 7 participants
[nested] [flat]

* is it possible to get the optimizer to use indexes with a like clause
@ 2003-12-18 23:18  Dave Cramer <pg@fastcrypt.com>
  0 siblings, 2 replies; 11+ messages in thread

From: Dave Cramer @ 2003-12-18 23:18 UTC (permalink / raw)
  To: pgsql-performance

It appears that the optimizer only uses indexes for = clause? 

Dave




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

* Re: is it possible to get the optimizer to use indexes
@ 2003-12-19 01:38  Doug McNaught <doug@mcnaught.org>
  parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  1 sibling, 1 reply; 11+ messages in thread

From: Doug McNaught @ 2003-12-19 01:38 UTC (permalink / raw)
  To: pgsql-performance

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

>> It appears that the optimizer only uses indexes for = clause?
>
> The optimizer will used indexes for LIKE clauses, so long as the
> clause is a prefix search, eg:
>
> SELECT * FROM test WHERE a LIKE 'prf%';

Doesn't this still depend on your locale?

-Doug




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

* Re: is it possible to get the optimizer to use indexes
@ 2003-12-19 01:38  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Dave Cramer <pg@fastcrypt.com>
  1 sibling, 2 replies; 11+ messages in thread

From: Christopher Kings-Lynne @ 2003-12-19 01:38 UTC (permalink / raw)
  To: pg@fastcrypt.com; +Cc: pgsql-performance

> It appears that the optimizer only uses indexes for = clause? 

The optimizer will used indexes for LIKE clauses, so long as the clause 
is a prefix search, eg:

SELECT * FROM test WHERE a LIKE 'prf%';

Chris




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

* Re: is it possible to get the optimizer to use indexes
@ 2003-12-19 03:08  Dave Cramer <pg@fastcrypt.com>
  parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  1 sibling, 2 replies; 11+ messages in thread

From: Dave Cramer @ 2003-12-19 03:08 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: pgsql-performance

after vacuum verbose analyze, I still get

explain select * from isppm where item_upc_cd like '06038301234';
                              QUERY PLAN
-----------------------------------------------------------------------
 Seq Scan on isppm  (cost=100000000.00..100009684.89 rows=2 width=791)
   Filter: (item_upc_cd ~~ '06038301234'::text)
(2 rows)
 
isp=# explain select * from isppm where item_upc_cd = '06038301234';
                               QUERY PLAN
------------------------------------------------------------------------
 Index Scan using isppm_x0 on isppm  (cost=0.00..5.86 rows=2 width=791)
   Index Cond: (item_upc_cd = '06038301234'::bpchar)
(2 rows)


Dave
On Thu, 2003-12-18 at 20:38, Christopher Kings-Lynne wrote:
> > It appears that the optimizer only uses indexes for = clause? 
> 
> The optimizer will used indexes for LIKE clauses, so long as the clause 
> is a prefix search, eg:
> 
> SELECT * FROM test WHERE a LIKE 'prf%';
> 
> Chris
> 
> 




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

* Re: is it possible to get the optimizer to use indexes with a like clause
@ 2003-12-19 03:22  Christopher Browne <cbbrowne@acm.org>
  parent: Dave Cramer <pg@fastcrypt.com>
  1 sibling, 0 replies; 11+ messages in thread

From: Christopher Browne @ 2003-12-19 03:22 UTC (permalink / raw)
  To: pgsql-performance

pg@fastcrypt.com (Dave Cramer) wrote:
> It appears that the optimizer only uses indexes for = clause? 

It can use indices only if there is a given prefix.

Thus:
   where text_field like 'A%'

can use the index, essentially transforming this into the clauses

   where text_field >= 'A' and
         text_field < 'B'.

You can't get much out of an index for
   where text_field like '%SOMETHING'
-- 
(reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc"))
http://www3.sympatico.ca/cbbrowne/wp.html
"When the grammar checker identifies an error, it suggests a
correction and can even makes some changes for you."  
-- Microsoft Word for Windows 2.0 User's Guide, p.35:



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

* Re: is it possible to get the optimizer to use indexes
@ 2003-12-19 03:36  Stephan Szabo <sszabo@megazone.bigpanda.com>
  parent: Dave Cramer <pg@fastcrypt.com>
  1 sibling, 0 replies; 11+ messages in thread

From: Stephan Szabo @ 2003-12-19 03:36 UTC (permalink / raw)
  To: Dave Cramer <pg@fastcrypt.com>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-performance


On Thu, 18 Dec 2003, Dave Cramer wrote:

> after vacuum verbose analyze, I still get
>
> explain select * from isppm where item_upc_cd like '06038301234';
>                               QUERY PLAN
> -----------------------------------------------------------------------
>  Seq Scan on isppm  (cost=100000000.00..100009684.89 rows=2 width=791)
>    Filter: (item_upc_cd ~~ '06038301234'::text)
> (2 rows)

IIRC, the other limitation is that it only does so in "C" locale due to
wierdnesses in other locales.



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

* Re: is it possible to get the optimizer to use indexes
@ 2003-12-19 03:44  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Dave Cramer <pg@fastcrypt.com>
  1 sibling, 1 reply; 11+ messages in thread

From: Tom Lane @ 2003-12-19 03:44 UTC (permalink / raw)
  To: pg@fastcrypt.com; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-performance

Dave Cramer <pg@fastcrypt.com> writes:
> after vacuum verbose analyze, I still get [a seqscan]

The other gating factor is that you have to have initdb'd in C locale.
Non-C locales tend to use wild and wooly sort orders that are not
compatible with what LIKE needs.

			regards, tom lane



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

* Re: is it possible to get the optimizer to use indexes
@ 2003-12-19 09:50  Dave Cramer <pg@fastcrypt.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 11+ messages in thread

From: Dave Cramer @ 2003-12-19 09:50 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-performance

So even in a north-american locale, such as en_CA this will be a
problem?

Dave
On Thu, 2003-12-18 at 22:44, Tom Lane wrote:
> Dave Cramer <pg@fastcrypt.com> writes:
> > after vacuum verbose analyze, I still get [a seqscan]
> 
> The other gating factor is that you have to have initdb'd in C locale.
> Non-C locales tend to use wild and wooly sort orders that are not
> compatible with what LIKE needs.
> 
> 			regards, tom lane
> 
> 




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

* Re: is it possible to get the optimizer to use indexes
@ 2003-12-19 14:38  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Dave Cramer <pg@fastcrypt.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Tom Lane @ 2003-12-19 14:38 UTC (permalink / raw)
  To: pg@fastcrypt.com; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-performance

Dave Cramer <pg@fastcrypt.com> writes:
> So even in a north-american locale, such as en_CA this will be a
> problem?

If it's not "C" we won't try to optimize LIKE.  I know en_US does not
work (case-insensitive, funny rules about spaces, etc) and I would
expect en_CA has the same issues.

If you're using 7.4 you have the option to create a special index
instead of re-initdb'ing your whole database.

			regards, tom lane



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

* Re: is it possible to get the optimizer to use indexes
@ 2003-12-19 16:00  Erki Kaldjärv <erki@webware.ee>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 11+ messages in thread

From: Erki Kaldjärv @ 2003-12-19 16:00 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pg@fastcrypt.com, Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-performance

Hello,

i got indexes to work with "text_pattern_ops" for locale et_EE.

So instead of:
create index some_index_name on some_table(some_text_field);

nor

create index some_index_name on some_table(some_text_field text_ops);

try to create index as follows:
create index some_index_name on some_table(some_text_field 
text_pattern_ops);

Note that text_pattern_ops is available pg >= 7.4.

Regards,

Erki Kaldjärv
Webware OÜ
www.webware.ee

Tom Lane wrote:

>Dave Cramer <pg@fastcrypt.com> writes:
>  
>
>>So even in a north-american locale, such as en_CA this will be a
>>problem?
>>    
>>
>
>If it's not "C" we won't try to optimize LIKE.  I know en_US does not
>work (case-insensitive, funny rules about spaces, etc) and I would
>expect en_CA has the same issues.
>
>If you're using 7.4 you have the option to create a special index
>instead of re-initdb'ing your whole database.
>
>			regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>  
>

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

* Re: is it possible to get the optimizer to use indexes
@ 2003-12-24 13:58  Dave Cramer <pg@fastcrypt.com>
  parent: Doug McNaught <doug@mcnaught.org>
  0 siblings, 0 replies; 11+ messages in thread

From: Dave Cramer @ 2003-12-24 13:58 UTC (permalink / raw)
  To: Doug McNaught <doug@mcnaught.org>; +Cc: pgsql-performance

Doug,

Yes, it does depend on the locale, you can get around this in 7.4 by
building the index with smart operators

Dave
On Thu, 2003-12-18 at 20:38, Doug McNaught wrote:
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> 
> >> It appears that the optimizer only uses indexes for = clause?
> >
> > The optimizer will used indexes for LIKE clauses, so long as the
> > clause is a prefix search, eg:
> >
> > SELECT * FROM test WHERE a LIKE 'prf%';
> 
> Doesn't this still depend on your locale?
> 
> -Doug
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
> 
> 




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


end of thread, other threads:[~2003-12-24 13:58 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-18 23:18 is it possible to get the optimizer to use indexes with a like clause Dave Cramer <pg@fastcrypt.com>
2003-12-19 01:38 ` Re: is it possible to get the optimizer to use indexes Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-12-19 01:38   ` Re: is it possible to get the optimizer to use indexes Doug McNaught <doug@mcnaught.org>
2003-12-24 13:58     ` Re: is it possible to get the optimizer to use indexes Dave Cramer <pg@fastcrypt.com>
2003-12-19 03:08   ` Re: is it possible to get the optimizer to use indexes Dave Cramer <pg@fastcrypt.com>
2003-12-19 03:36     ` Re: is it possible to get the optimizer to use indexes Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-12-19 03:44     ` Re: is it possible to get the optimizer to use indexes Tom Lane <tgl@sss.pgh.pa.us>
2003-12-19 09:50       ` Re: is it possible to get the optimizer to use indexes Dave Cramer <pg@fastcrypt.com>
2003-12-19 14:38         ` Re: is it possible to get the optimizer to use indexes Tom Lane <tgl@sss.pgh.pa.us>
2003-12-19 16:00           ` Re: is it possible to get the optimizer to use indexes Erki Kaldjärv <erki@webware.ee>
2003-12-19 03:22 ` Christopher Browne <cbbrowne@acm.org>

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