From: Andrew Lazarus <andrew@pillette.com>
To: Jamal Ghaffour <Jamal.Ghaffour@elios-informatique.fr>
Cc: pgsql-performance@postgresql.org
Subject: Re: Please Help: PostgreSQL performance Optimization
Date: Thu, 12 Jan 2006 10:42:39 -0800
Message-ID: <43C6A31F.3010007@pillette.com> (raw)
In-Reply-To: <43C66D67.1000502@elios-informatique.fr>
References: <43C66742.9060104@elios-informatique.fr>
<43C66D67.1000502@elios-informatique.fr>
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.
Attachments:andrew.vcf (text/x-vcard, 296B) download
begin:vcard
fn:Andrew Lazarus
n:Lazarus;Andrew
org:Pillette Investment Management;Research and Development
adr;dom:;;3028 Fillmore;San Francisco;CA;94123
email;internet:andrew@pillette.com
title:Director
tel;work:800-366-0688
tel;fax:415-440-4093
url:http://www.pillette.com
version:2.1
end:vcard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andrew@pillette.com, Jamal.Ghaffour@elios-informatique.fr
Subject: Re: Please Help: PostgreSQL performance Optimization
In-Reply-To: <43C6A31F.3010007@pillette.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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