Performance Issues

Dhanaraj M <Dhanaraj.M@Sun.COM> writes:

I have the following doubts.

1. Does postgres create an index on every primary key? Usually,
queries are performed against a table on the primary key, so, an index
on it will be very useful.

To enforce the primary key constraint, PG creates a unique index when
the table is created (I think it even tells you this after CREATE
TABLE).

2. If 'm executing a complex query and it takes 10 seconds to return
the results -- it takes 10 seconds to execute the next time also. I'm
wondering if there's any kind of caching that can be enabled -- so,
the next time it takes <10 seconds to return the results.

All kinds of data is cached in shared memory. Did you tune the
shared_buffers setting in postgresql.conf? It's set quite low by
default to make sure the server can start on systems with low shared
memory limits.

The online documentation has this info and lots more--I suggest you
read it.

-Doug

Dhanaraj M wrote:

I have the following doubts.

1. Does postgres create an index on every primary key? Usually, queries
are performed against a table on the primary key, so, an index on it
will be very useful.

Yes, a unique index is used to enforce the primary-key.

2. If 'm executing a complex query and it takes 10 seconds to return the
results -- it takes 10 seconds to execute the next time also. I'm
wondering if there's any kind of caching that can be enabled -- so, the
next time it takes <10 seconds to return the results.

Not of query results. Obviously data itself might be cached. You might
want to look at memcached for this sort of thing.

--
Richard Huxton
Archonet Ltd

On 23-May-06, at 10:24 AM, Richard Huxton wrote:

Dhanaraj M wrote:

I have the following doubts.
1. Does postgres create an index on every primary key? Usually,
queries are performed against a table on the primary key, so, an
index on it will be very useful.

Yes, a unique index is used to enforce the primary-key.

2. If 'm executing a complex query and it takes 10 seconds to
return the results -- it takes 10 seconds to execute the next time
also. I'm wondering if there's any kind of caching that can be
enabled -- so, the next time it takes <10 seconds to return the
results.

Not of query results. Obviously data itself might be cached. You
might want to look at memcached for this sort of thing.

Postgresql relies on the kernel buffers, and shared buffers for caching.

As someone else said postgresql is quite conservative when shipped.
Tuning helps considerably

Dave

Dhanaraj M wrote:

I have the following doubts.

1. Does postgres create an index on every primary key? Usually, queries
are performed against a table on the primary key, so, an index on it
will be very useful.

Yes, a unique index is used to enforce the primary-key.

Well, here is an interesting question that I have suddenly become very
curious of, if you have a primary key, obviously a unique index, is it, in
fact, use this index regardless of analyzing the table?

2. If 'm executing a complex query and it takes 10 seconds to return the
results -- it takes 10 seconds to execute the next time also. I'm
wondering if there's any kind of caching that can be enabled -- so, the
next time it takes <10 seconds to return the results.

Not of query results. Obviously data itself might be cached. You might
want to look at memcached for this sort of thing.

I am looking at this string of posts and it occurs to me that he should
run analyze. Maybe I'm jumping at the wrong point.

Thank you for your help. I found that an implicit index is created for
the primary key in the current version. However, it is not done in 7.x
version.

Mark Woodward wrote:

Thank you for your help. I found that an implicit index is created for
the primary key in the current version. However, it is not done in 7.x
version.

It absolutely is created in all 7.x versions of PostgreSQL.

Christopher Kings-Lynne <chris.kings-lynne@calorieking.com> writes:

Thank you for your help. I found that an implicit index is created for
the primary key in the current version. However, it is not done in 7.x
version.

It absolutely is created in all 7.x versions of PostgreSQL.

And every other version too. PRIMARY KEY/UNIQUE syntax was not
supported before this patch:

1997-12-04 18:07 thomas

* src/backend/parser/: analyze.c, gram.y: Add SQL92-compliant
syntax for constraints. Implement PRIMARY KEY and UNIQUE clauses
using indices.

and in that patch and every subsequent version, unique constraints are
associated with indexes. In fact, we do not even *have* any
implementation method for unique constraints other than the duplicate-
entry-detection code in the btree index AM.

regards, tom lane