pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
best practices with index on varchar column
7+ messages / 6 participants
[nested] [flat]

* best practices with index on varchar column
@ 2005-03-22 09:22  Layet Benjamin <benjamin@moonfactory.co.jp>
  0 siblings, 2 replies; 7+ messages in thread

From: Layet Benjamin @ 2005-03-22 09:22 UTC (permalink / raw)
  To: pgsql-performance

Hi everyone,
I hope it is the correct newsletter for this question.

Can I use an index on a varchar column to optimize the SELECT queries that 
use " column LIKE 'header%'  "?
If yes what is the best tree algotithm to use ?

I don't care about optimising INSERT, DELETE and UPDATE queries, as they are 
only done at night when the load is very low.
Thank you very much for any help,
Benjamin Layet

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

* Re: best practices with index on varchar column
@ 2005-03-22 09:49  Dawid Kuroczko <qnex42@gmail.com>
  parent: Layet Benjamin <benjamin@moonfactory.co.jp>
  1 sibling, 0 replies; 7+ messages in thread

From: Dawid Kuroczko @ 2005-03-22 09:49 UTC (permalink / raw)
  To: pgsql-performance

On Tue, 22 Mar 2005 18:22:24 +0900, Layet Benjamin
<benjamin@moonfactory.co.jp> wrote:
> Can I use an index on a varchar column to optimize the SELECT queries that
> use " column LIKE 'header%'  "? 
> If yes what is the best tree algotithm to use ? 

Yes, that is the correct place.  The best tree algorithm is B-Tree,
which is the default.  So no need for giving 'USING ...' to CREATE INDEX.

The other types of indexes are either not trees (HASH), different
and more complex (GiST, RTREE) kinds of trees which are there
for different kinds of data (spatial, full text, etc).

Remember to VACUUM ANALYZE this table from time to time,
so the planner can judge efficiently whether to use this new
index or not.

Use EXPLAIN ANALYZE SELECT .... to see whether the index
is really used.

> I don't care about optimising INSERT, DELETE and UPDATE queries, as they are
> only done at night when the load is very low. 
> Thank you very much for any help, 

Oh, they can benefit from the index anyhow. :)

  Regards,
     Dawid



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

* Re: best practices with index on varchar column
@ 2005-03-22 10:49  PFC <lists@boutiquenumerique.com>
  parent: Layet Benjamin <benjamin@moonfactory.co.jp>
  1 sibling, 2 replies; 7+ messages in thread

From: PFC @ 2005-03-22 10:49 UTC (permalink / raw)
  To: Layet Benjamin <benjamin@moonfactory.co.jp>; pgsql-performance


> Can I use an index on a varchar column to optimize the SELECT queries  
> that
> use " column LIKE 'header%'  "?

	Yes

> If yes what is the best tree algotithm to use ?

	Btree

	Note that if you want case insensitive matching you need to make an index  
on lower(column) and SELECT WHERE lower(column) LIKE 'header%'

	Locales may bite you.



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

* Re: best practices with index on varchar column
@ 2005-03-22 18:08  Tom Lane <tgl@sss.pgh.pa.us>
  parent: PFC <lists@boutiquenumerique.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Tom Lane @ 2005-03-22 18:08 UTC (permalink / raw)
  To: PFC <lists@boutiquenumerique.com>; +Cc: Layet Benjamin <benjamin@moonfactory.co.jp>; pgsql-performance

PFC <lists@boutiquenumerique.com> writes:
>> Can I use an index on a varchar column to optimize the SELECT queries  
>> that use " column LIKE 'header%'  "?

> 	Yes

> 	Note that if you want case insensitive matching you need to make an index  
> on lower(column) and SELECT WHERE lower(column) LIKE 'header%'

> 	Locales may bite you.

Yes.  If your database locale is not "C" then the default btree index
behavior does not match up with what LIKE needs.  In that case you need
a special index using the appropriate "pattern_ops" opclass, eg

CREATE INDEX test_index ON test_table (col varchar_pattern_ops);

or if you want case insensitive matching

CREATE INDEX test_index ON test_table (lower(col) varchar_pattern_ops);

and then write the queries with lower() as PFC illustrates.  *Don't* use
ILIKE --- it basically can't use indexes at all.

For more info see
http://www.postgresql.org/docs/8.0/static/indexes-opclass.html

			regards, tom lane



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

* Re: best practices with index on varchar column
@ 2005-03-23 04:11  Michael Ryan S. Puncia <mpuncia@census.gov.ph>
  parent: PFC <lists@boutiquenumerique.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Michael Ryan S. Puncia @ 2005-03-23 04:11 UTC (permalink / raw)
  To: pgsql-performance


I have an experience using LIKE in a VARCHAR column and select statement
suffers a lot so I decided to go back in CHAR 

Note: my database has about 50 millions records a b tree index 



> Can I use an index on a varchar column to optimize the SELECT queries  
> that
> use " column LIKE 'header%'  "?

	Yes

> If yes what is the best tree algotithm to use ?

	Btree

	Note that if you want case insensitive matching you need to make an
index  
on lower(column) and SELECT WHERE lower(column) LIKE 'header%'

	Locales may bite you.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

__________ NOD32 1.1023 (20050310) Information __________

This message was checked by NOD32 Antivirus System.
http://www.nod32.com





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

* Re: best practices with index on varchar column
@ 2005-03-23 09:35  Dawid Kuroczko <qnex42@gmail.com>
  parent: Michael Ryan S. Puncia <mpuncia@census.gov.ph>
  0 siblings, 1 reply; 7+ messages in thread

From: Dawid Kuroczko @ 2005-03-23 09:35 UTC (permalink / raw)
  To: pgsql-performance

On Wed, 23 Mar 2005 12:11:56 +0800, Michael Ryan S. Puncia
<mpuncia@census.gov.ph> wrote:
> 
> I have an experience using LIKE in a VARCHAR column and select statement
> suffers a lot so I decided to go back in CHAR
> 
> Note: my database has about 50 millions records a b tree index

Strange...

According to the PostgreSQL's documentation:

     Tip: There are no performance differences between these three types,
apart from the increased storage size when using the blank-padded type.
While character(n) has performance advantages in some other database
systems, it has no such advantages in PostgreSQL. In most situations text
or character varying should be used instead.


To my best knowledge char and varchar are stored in a same way
(4-byte length plus textual value), so using char should make tables
bigger in your case.  Then again, having each row exactly the same
size makes it easier to delete and then later insert a new row in
a same spot.  Am I thinking correct?  Is it a case where using char(n)
makes that table avoid hmm fragmentation of some sort?

   Regards,
      Dawid



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

* Re: best practices with index on varchar column
@ 2005-03-23 11:31  Richard Huxton <dev@archonet.com>
  parent: Dawid Kuroczko <qnex42@gmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Richard Huxton @ 2005-03-23 11:31 UTC (permalink / raw)
  To: Dawid Kuroczko <qnex42@gmail.com>; +Cc: pgsql-performance

Dawid Kuroczko wrote:
> On Wed, 23 Mar 2005 12:11:56 +0800, Michael Ryan S. Puncia
> <mpuncia@census.gov.ph> wrote:
> 
>>I have an experience using LIKE in a VARCHAR column and select statement
>>suffers a lot so I decided to go back in CHAR

> According to the PostgreSQL's documentation:
> 
>      Tip: There are no performance differences between these three types,
> apart from the increased storage size when using the blank-padded type.
> While character(n) has performance advantages in some other database
> systems, it has no such advantages in PostgreSQL. In most situations text
> or character varying should be used instead.
> 
> 
> To my best knowledge char and varchar are stored in a same way
> (4-byte length plus textual value), so using char should make tables
> bigger in your case.  Then again, having each row exactly the same
> size makes it easier to delete and then later insert a new row in
> a same spot.  Am I thinking correct?  Is it a case where using char(n)
> makes that table avoid hmm fragmentation of some sort?

There aren't any noticeable differences between char and varchar. MVCC 
doesn't overwrite rows anyway, so static size is irrelevant. In any 
case, PG's toast setup splits out large text fields and compresses them 
- so it's not that simple.

-- 
   Richard Huxton
   Archonet Ltd



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


end of thread, other threads:[~2005-03-23 11:31 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-22 09:22 best practices with index on varchar column Layet Benjamin <benjamin@moonfactory.co.jp>
2005-03-22 09:49 ` Dawid Kuroczko <qnex42@gmail.com>
2005-03-22 10:49 ` PFC <lists@boutiquenumerique.com>
2005-03-22 18:08   ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-23 04:11   ` Michael Ryan S. Puncia <mpuncia@census.gov.ph>
2005-03-23 09:35     ` Dawid Kuroczko <qnex42@gmail.com>
2005-03-23 11:31       ` Richard Huxton <dev@archonet.com>

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