pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDoes this matter?
10+ messages / 6 participants
[nested] [flat]
* Does this matter?
@ 2002-11-01 20:18 Wei Weng <wweng@kencast.com>
0 siblings, 1 reply; 10+ messages in thread
From: Wei Weng @ 2002-11-01 20:18 UTC (permalink / raw)
To: pgsql-performance@postgresql.org
Does it make a performance difference if I use a char(20) or a char(36)
as the primary key? My thought is no, but I would like to hear more
opinions.
And a little further off topic(since we have many database experts
here), does it matter on MS SQL server 7?
Thanks!
--
Wei Weng
Network Software Engineer
KenCast Inc.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Does this matter?
@ 2002-11-01 20:23 Josh Berkus <josh@agliodbs.com>
parent: Wei Weng <wweng@kencast.com>
0 siblings, 2 replies; 10+ messages in thread
From: Josh Berkus @ 2002-11-01 20:23 UTC (permalink / raw)
To: Wei Weng <wweng@kencast.com>; pgsql-performance@postgresql.org
Wei,
> Does it make a performance difference if I use a char(20) or a char(36)
> as the primary key? My thought is no, but I would like to hear more
> opinions.
Yes, it does, though probably minor unless you have millions of records. CHAR
is padded out to the specified length. Therefore the index on a char(36)
column will be a little larger, and thus a little slower, than the char(20).
Now, there would be no difference between VARCHAR(20) and VARCHAR(36) unless
you used some of the extra 16 characters on most rows.
Either way, for tables of a few thousand records, I doubt that you'll notice
the difference. BTW, why not use a SERIAL value as a surrogate primary key?
> And a little further off topic(since we have many database experts
> here), does it matter on MS SQL server 7?
Yes, same reason.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Does this matter?
@ 2002-11-01 20:52 Wei Weng <wweng@kencast.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 3 replies; 10+ messages in thread
From: Wei Weng @ 2002-11-01 20:52 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-performance@postgresql.org
Josh:
Since I need to use a GUID as the primary key, I have to use the char
datatype.
On Fri, 2002-11-01 at 15:23, Josh Berkus wrote:
> Wei,
>
> > Does it make a performance difference if I use a char(20) or a char(36)
> > as the primary key? My thought is no, but I would like to hear more
> > opinions.
>
> Yes, it does, though probably minor unless you have millions of records. CHAR
> is padded out to the specified length. Therefore the index on a char(36)
> column will be a little larger, and thus a little slower, than the char(20).
Does it affect the INSERT/UPDATE/DELETE operations on tables or simply
the SELECT operation or both?
>
> Now, there would be no difference between VARCHAR(20) and VARCHAR(36) unless
> you used some of the extra 16 characters on most rows.
>
> Either way, for tables of a few thousand records, I doubt that you'll notice
> the difference. BTW, why not use a SERIAL value as a surrogate primary key?
>
> > And a little further off topic(since we have many database experts
> > here), does it matter on MS SQL server 7?
>
> Yes, same reason.
--
Wei Weng
Network Software Engineer
KenCast Inc.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Does this matter?
@ 2002-11-01 20:53 Philip Hallstrom <philip@adhesivemedia.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 2 replies; 10+ messages in thread
From: Philip Hallstrom @ 2002-11-01 20:53 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Wei Weng <wweng@kencast.com>; pgsql-performance@postgresql.org
> Wei,
>
> > Does it make a performance difference if I use a char(20) or a char(36)
> > as the primary key? My thought is no, but I would like to hear more
> > opinions.
>
> Yes, it does, though probably minor unless you have millions of records. CHAR
> is padded out to the specified length. Therefore the index on a char(36)
> column will be a little larger, and thus a little slower, than the char(20).
>
Really? According to this url (search for "Tip") there is no performance
difference just a space difference. I don't know for sure either way, but
if there is a difference the manual needs updating.
http://www.postgresql.org/idocs/index.php?datatype-character.html
-philip
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Does this matter?
@ 2002-11-01 21:10 Andrew Sullivan <andrew@libertyrms.info>
parent: Philip Hallstrom <philip@adhesivemedia.com>
1 sibling, 1 reply; 10+ messages in thread
From: Andrew Sullivan @ 2002-11-01 21:10 UTC (permalink / raw)
To: pgsql-performance@postgresql.org
On Fri, Nov 01, 2002 at 12:53:29PM -0800, Philip Hallstrom wrote:
> > is padded out to the specified length. Therefore the index on a char(36)
> > column will be a little larger, and thus a little slower, than the char(20).
> >
>
> Really? According to this url (search for "Tip") there is no performance
> difference just a space difference. I don't know for sure either way, but
> if there is a difference the manual needs updating.
Hmm. Maybe a clarification, but I don't think this is quite what the
tip is talking about. The tip points out that part of the cost is
"the increased storage" from the blank-padded type (char) as
contrasted with non-padded types (like text). The tip isn't talking
about whether a length of 20 is faster than a length of 36. Anyway,
I can't really believe the length would be a big deal except on
really huge tables.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Does this matter?
@ 2002-11-01 22:00 Josh Berkus <josh@agliodbs.com>
parent: Philip Hallstrom <philip@adhesivemedia.com>
1 sibling, 0 replies; 10+ messages in thread
From: Josh Berkus @ 2002-11-01 22:00 UTC (permalink / raw)
To: Philip Hallstrom <philip@adhesivemedia.com>; +Cc: Wei Weng <wweng@kencast.com>; pgsql-performance@postgresql.org
Phillip,
> Really? According to this url (search for "Tip") there is no performance
> difference just a space difference. I don't know for sure either way, but
> if there is a difference the manual needs updating.
>
> http://www.postgresql.org/idocs/index.php?datatype-character.html
Actually, that note is intended to tell people that CHAR is not any faster
than VARCHAR for the same-length string ... since CHAR *is* faster than
VARCHAR in some systems, like MS SQL Server.
--
-Josh Berkus
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Does this matter?
@ 2002-11-01 22:00 Curtis Faith <curtis@galtair.com>
parent: Andrew Sullivan <andrew@libertyrms.info>
0 siblings, 0 replies; 10+ messages in thread
From: Curtis Faith @ 2002-11-01 22:00 UTC (permalink / raw)
To: Andrew Sullivan <andrew@libertyrms.info>; +Cc: pgsql-performance@postgresql.org
Andrew Sullivan wrote:
> Hmm. Maybe a clarification, but I don't think this is quite what the
> tip is talking about. The tip points out that part of the cost is
> "the increased storage" from the blank-padded type (char) as
> contrasted with non-padded types (like text). The tip isn't talking
> about whether a length of 20 is faster than a length of 36. Anyway,
> I can't really believe the length would be a big deal except on
> really huge tables.
It really depends on the access. I spend quite a bit of time optimizing
database internals and the size of an index matters much more than is
apparent in certain cases. This is especially true for medium sized tables.
The real issue is the number of reads required to find a particular entry in
the index.
Assume a btree that tries to be 70% full. Assume 40 bytes for a header, 8
bytes overhead per index entry and an 8K btree page.
The following represents the number of index entries that can be contained in
both a two level and a three level btree.
Type Bytes Items per page 2 3
---- ------ ----- ------ ----------
char(36) 40 129 16,641 2,146,689
char(20) 24 203 41,209 8,365,427
Depending on the size of the table, the number of pages in the btree affect
performance in two separate ways:
1) Cache hit ratio - This greatly depends on the way the tables are accessed
but more densely packed btree indices are used more often and more likely to
be present in a cache than less densely packed indices.
2) I/O time - If the number of items reaches a particular size then the btree
will add an additional level which could result in a very expensive I/O
operation per access. How this affects performance depends very specifically
on the way the index is used.
The problem is not necessarily the size of the table but the transitions in
numbers of levels in the btree. For a table size of 200 to 15,000 tuples,
there won't be a major difference.
For a table size of 25,000 to 40,000 tuples, and assuming the root page is
cached, an index lookup can be twice as fast with a char(20) as it is for a
char(36) because in the one case a two-level btree handles the table while a
three-level btree is needed for the other.
This won't typically affect multi-user throughput as much since other
backends will be working while the I/O's are waiting but it might affect the
performance as seen from a single client.
- Curtis
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Does this matter?
@ 2002-11-01 22:01 Josh Berkus <josh@agliodbs.com>
parent: Wei Weng <wweng@kencast.com>
2 siblings, 0 replies; 10+ messages in thread
From: Josh Berkus @ 2002-11-01 22:01 UTC (permalink / raw)
To: Wei Weng <wweng@kencast.com>; +Cc: pgsql-performance@postgresql.org
Wei,
> Does it affect the INSERT/UPDATE/DELETE operations on tables or simply
> the SELECT operation or both?
All of the above. How many rows are we talking about, anyway? The difference
may be academic.
--
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Does this matter?
@ 2002-11-04 04:08 Neil Conway <neilc@samurai.com>
parent: Wei Weng <wweng@kencast.com>
2 siblings, 0 replies; 10+ messages in thread
From: Neil Conway @ 2002-11-04 04:08 UTC (permalink / raw)
To: Wei Weng <wweng@kencast.com>; +Cc: josh@agliodbs.com; pgsql-performance@postgresql.org
Wei Weng <wweng@kencast.com> writes:
> Since I need to use a GUID as the primary key, I have to use the char
> datatype.
Try uniqueidentifier:
http://archives.postgresql.org/pgsql-announce/2002-07/msg00001.php
Cheers,
Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] Does this matter?
@ 2002-11-04 04:33 Wei Weng <wweng@kencast.com>
parent: Wei Weng <wweng@kencast.com>
2 siblings, 0 replies; 10+ messages in thread
From: Wei Weng @ 2002-11-04 04:33 UTC (permalink / raw)
To: pgsql-sql
Thanks, I noticed that sweet addon and will try to integrate it into our
system once 7.3 is officially released. :)
btw, do we have a release date yet?
Thanks
Wei
At 11:08 PM 11/3/2002 -0500, you wrote:
>Wei Weng <wweng@kencast.com> writes:
> > Since I need to use a GUID as the primary key, I have to use the char
> > datatype.
>
>Try uniqueidentifier:
>
> http://archives.postgresql.org/pgsql-announce/2002-07/msg00001.php
>
>Cheers,
>
>Neil
>
>--
>Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2002-11-04 04:33 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-01 20:18 Does this matter? Wei Weng <wweng@kencast.com>
2002-11-01 20:23 ` Josh Berkus <josh@agliodbs.com>
2002-11-01 20:52 ` Wei Weng <wweng@kencast.com>
2002-11-01 22:01 ` Josh Berkus <josh@agliodbs.com>
2002-11-04 04:08 ` Neil Conway <neilc@samurai.com>
2002-11-04 04:33 ` Wei Weng <wweng@kencast.com>
2002-11-01 20:53 ` Philip Hallstrom <philip@adhesivemedia.com>
2002-11-01 21:10 ` Andrew Sullivan <andrew@libertyrms.info>
2002-11-01 22:00 ` Curtis Faith <curtis@galtair.com>
2002-11-01 22:00 ` Josh Berkus <josh@agliodbs.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