X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 0D6869DC803 for ; Thu, 12 Jan 2006 14:34:17 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 67388-03 for ; Thu, 12 Jan 2006 14:34:15 -0400 (AST) X-Greylist: delayed 00:05:12.206106 by SQLgrey- Received: from pillette.com (adsl-67-119-5-202.dsl.snfc21.pacbell.net [67.119.5.202]) by postgresql.org (Postfix) with ESMTP id 6A3E39DC83A for ; Thu, 12 Jan 2006 14:34:12 -0400 (AST) Received: from [192.168.1.234] ([192.168.1.234]) by pillette.com (8.11.6/8.11.6) with ESMTP id k0CIStJ03177; Thu, 12 Jan 2006 10:28:55 -0800 Message-ID: <43C6A31F.3010007@pillette.com> Date: Thu, 12 Jan 2006 10:42:39 -0800 From: Andrew Lazarus Reply-To: andrew@pillette.com Organization: Pillette Investment Management User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Jamal Ghaffour CC: pgsql-performance@postgresql.org Subject: Re: Please Help: PostgreSQL performance Optimization References: <43C66742.9060104@elios-informatique.fr> <43C66D67.1000502@elios-informatique.fr> In-Reply-To: <43C66D67.1000502@elios-informatique.fr> Content-Type: multipart/mixed; boundary="------------010002000102050508090103" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200601/160 X-Sequence-Number: 16638 This is a multi-part message in MIME format. --------------010002000102050508090103 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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>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. --------------010002000102050508090103 Content-Type: text/x-vcard; charset=utf-8; name="andrew.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="andrew.vcf" 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 --------------010002000102050508090103--