Re: Please Help: PostgreSQL performance Optimization

Jamal Ghaffour wrote:

>>CREATE TABLE cookies (
>> domain varchar(50) NOT NULL,
>> path varchar(50) NOT NULL,
>> name varchar(50) NOT NULL,
>> principalid varchar(50) NOT NULL,
>> host text NOT NULL,
>> value text NOT NULL,
>> secure bool NOT NULL,
>> timestamp timestamp with time zone NOT NULL DEFAULT
>>CURRENT_TIMESTAMP+TIME '04:00:00',
>> PRIMARY KEY (domain,path,name,principalid)
>>)
[snip]
>>SELECT path, upper(name) AS name, value FROM cookies WHERE timestamp<CURRENT_TIMESTAMP AND principalid='192.168.8.219' AND
>>secure=FALSE AND (domain='ping.icap-elios.com' OR domain='.icap-elios.com')

I think the problem here is that the column order in the index doesn't
match the columns used in the WHERE clause criteria. Try adding an index
on (domain,principalid) or (domain,principalid,timestamp). If these are
your only queries, you can get the same effect by re-ordering the
columns in the table so that this is the column order used by the
primary key and its implicit index.

You should check up on EXPLAIN and EXPLAIN ANALYZE to help you debug
slow queries.