Thread: Performance Issues

Performance Issues

From
Dhanaraj M
Date:
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


Re: Performance Issues

From
Douglas McNaught
Date:
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


Re: Performance Issues

From
Richard Huxton
Date:
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


Re: Performance Issues

From
Dave Cramer
Date:
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
>



Re: Performance Issues

From
"Mark Woodward"
Date:
> 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.


Re: Performance Issues

From
Dhanaraj M
Date:
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.
>  
>



Re: Performance Issues

From
Christopher Kings-Lynne
Date:
> 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.



Re: Performance Issues

From
Tom Lane
Date:
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