Thread: Performance Issues
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. 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. Thanks Dhanaraj
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 > > -- > Richard Huxton > Archonet Ltd > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
> 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: >>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. 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-compliantsyntax for constraints. Implement PRIMARY KEY and UNIQUE
clausesusingindices.
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