pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedbest 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