pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCreating indexes
5+ messages / 4 participants
[nested] [flat]
* Creating indexes
@ 2008-05-08 19:00 Rauan Maemirov <rauan1987@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: Rauan Maemirov @ 2008-05-08 19:00 UTC (permalink / raw)
To: pgsql-performance
Hi, all. I want to ask what type of index is better to create for
bigint types. I have table with bigint (bigserial) primary key. What
type is better to use for it? I tried btree and hash, but didn't
notice any differences in execution time. For GiST and GIN there is a
trouble that I must create operator class, so I limited myself to use
btree or hash. But if it's better to use gist or gin, coment are
welcome.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Creating indexes
@ 2008-05-08 19:49 Asche <asche.public@mac.com>
parent: Rauan Maemirov <rauan1987@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Asche @ 2008-05-08 19:49 UTC (permalink / raw)
To: pgsql-performance
Hi,
> Hi, all. I want to ask what type of index is better to create for
> bigint types. I have table with bigint (bigserial) primary key. What
http://www.postgresql.org/docs/8.3/static/sql-createtable.html
PostgreSQL automatically creates an index for each unique constraint
and primary key constraint to enforce uniqueness. Thus, it is not
necessary to create an index explicitly for primary key columns.
> type is better to use for it? I tried btree and hash, but didn't
You already have an index on your bigint primary key. I think it is of
type btree.
Jan
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Creating indexes
@ 2008-05-08 19:52 Alan Hodgson <ahodgson@simkin.ca>
parent: Rauan Maemirov <rauan1987@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Alan Hodgson @ 2008-05-08 19:52 UTC (permalink / raw)
To: pgsql-performance
On Thursday 08 May 2008, Rauan Maemirov <rauan1987@gmail.com> wrote:
> Hi, all. I want to ask what type of index is better to create for
> bigint types. I have table with bigint (bigserial) primary key. What
> type is better to use for it? I tried btree and hash, but didn't
> notice any differences in execution time.
A primary key is a unique btree index, and it's as about as good as it gets
for a bigint.
--
Alan
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Creating indexes
@ 2008-05-08 20:08 PFC <lists@peufeu.com>
parent: Rauan Maemirov <rauan1987@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: PFC @ 2008-05-08 20:08 UTC (permalink / raw)
To: Rauan Maemirov <rauan1987@gmail.com>; pgsql-performance
> Hi, all. I want to ask what type of index is better to create for
> bigint types. I have table with bigint (bigserial) primary key. What
> type is better to use for it? I tried btree and hash, but didn't
> notice any differences in execution time. For GiST and GIN there is a
> trouble that I must create operator class, so I limited myself to use
> btree or hash. But if it's better to use gist or gin, coment are
> welcome.
If you use BIGINT, I presume you will have lots of different values, in
that case the best one is the btree. It is the most common and most
optimized index type.
GiST's strength is in using indexes for stuff that can't be done with a
simple btree : geometry, full text, ltree, etc, but gist is slower in the
case of indexing a simple value.
GIN indexes are more compact and very fast for reads but updating is very
slow (they are meant for mostly read-only tables).
Hash is a bit of a fossil. Also it does not support range queries, so if
you need that, btree is definitely better.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Creating indexes
@ 2008-05-09 05:41 Rauan Maemirov <rauan1987@gmail.com>
parent: Asche <asche.public@mac.com>
0 siblings, 0 replies; 5+ messages in thread
From: Rauan Maemirov @ 2008-05-09 05:41 UTC (permalink / raw)
To: pgsql-performance
On May 9, 1:49 am, asche.pub...@mac.com (Asche) wrote:
> Hi,
>
> > Hi, all. I want to ask what type of index is better to create for
> > bigint types. I have table with bigint (bigserial) primary key. What
>
> http://www.postgresql.org/docs/8.3/static/sql-createtable.html
>
> PostgreSQL automatically creates an index for each unique constraint
> and primary key constraint to enforce uniqueness. Thus, it is not
> necessary to create an index explicitly for primary key columns.
>
> > type is better to use for it? I tried btree and hash, but didn't
>
> You already have an index on your bigint primary key. I think it is of
> type btree.
>
> Jan
Aah, I understand. Thanks to all for detailed response.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-05-09 05:41 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-05-08 19:00 Creating indexes Rauan Maemirov <rauan1987@gmail.com>
2008-05-08 19:49 ` Asche <asche.public@mac.com>
2008-05-09 05:41 ` Rauan Maemirov <rauan1987@gmail.com>
2008-05-08 19:52 ` Alan Hodgson <ahodgson@simkin.ca>
2008-05-08 20:08 ` PFC <lists@peufeu.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