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 F173A9DD624 for ; Fri, 13 Jan 2006 05:10:51 -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 54472-07 for ; Fri, 13 Jan 2006 05:10:52 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp10.clb.oleane.net (smtp10.clb.oleane.net [213.56.31.32]) by postgresql.org (Postfix) with ESMTP id 0111A9DD5F2 for ; Fri, 13 Jan 2006 05:10:48 -0400 (AST) Received: from smtp10.clb.oleane.net (localhost [127.0.0.1]) by smtp10.clb.oleane.net (antivirus) with ESMTP id k0D9AkN9007244; Fri, 13 Jan 2006 10:10:46 +0100 Received: from [192.168.8.219] ([62.160.127.238]) (authenticated) by smtp10.clb.oleane.net with ESMTP id k0D9Aj6S007141; Fri, 13 Jan 2006 10:10:45 +0100 Message-ID: <43C76F41.1030902@elios-informatique.fr> Date: Fri, 13 Jan 2006 10:13:37 +0100 From: Jamal Ghaffour User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050716) X-Accept-Language: fr, en MIME-Version: 1.0 To: andrew@pillette.com CC: pgsql-performance@postgresql.org, systemguards@gmail.com Subject: Re: Please Help: PostgreSQL performance Optimization References: <43C66742.9060104@elios-informatique.fr> <43C66D67.1000502@elios-informatique.fr> <43C6A31F.3010007@pillette.com> In-Reply-To: <43C6A31F.3010007@pillette.com> Content-Type: multipart/mixed; boundary="------------080302050007030004030709" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.06 required=5 tests=[AWL=0.060] X-Spam-Score: 0.06 X-Spam-Level: X-Archive-Number: 200601/164 X-Sequence-Number: 16642 This is a multi-part message in MIME format. --------------080302050007030004030709 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit Andrew Lazarus a écrit : > 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. Hi, I created an index into the cookies table CREATE INDEX index_cookies_select ON cookies (domain, principalid, timestamp); and execute my UPDATE and select queries: 1 - The first select quey give the following results: icap=# EXPLAIN ANALYZE 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'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on cookies (cost=4.02..8.04 rows=1 width=268) (actual time=0.107..0.108 rows=1 loops=1) Recheck Cond: (((("domain")::text = 'ping.icap-elios.com'::text) AND ((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now())) OR ((("domain")::text = '.icap-elios.com'::text) AND ((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now()))) Filter: (("timestamp" > now()) AND (NOT secure)) -> BitmapOr (cost=4.02..4.02 rows=1 width=0) (actual time=0.091..0.091 rows=0 loops=1) -> Bitmap Index Scan on index_cookies_select (cost=0.00..2.01 rows=1 width=0) (actual time=0.077..0.077 rows=1 loops=1) Index Cond: ((("domain")::text = 'ping.icap-elios.com'::text) AND ((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now())) -> Bitmap Index Scan on index_cookies_select (cost=0.00..2.01 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=1) Index Cond: ((("domain")::text = '.icap-elios.com'::text) AND ((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now())) Total runtime: 0.155 ms (9 rows) 2- After that, i launch my test code that execute continuely the UPDATE and select queries (in loop manner), after 1 minute of continuous execution, i obtain the following result: icap=# EXPLAIN ANALYZE 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'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on cookies (cost=4.02..8.04 rows=1 width=268) (actual time=39.545..39.549 rows=1 loops=1) Recheck Cond: (((("domain")::text = 'ping.icap-elios.com'::text) AND ((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now())) OR ((("domain")::text = '.icap-elios.com'::text) AND ((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now()))) Filter: (("timestamp" > now()) AND (NOT secure)) -> BitmapOr (cost=4.02..4.02 rows=1 width=0) (actual time=39.512..39.512 rows=0 loops=1) -> Bitmap Index Scan on index_cookies_select (cost=0.00..2.01 rows=1 width=0) (actual time=39.471..39.471 rows=2 loops=1) Index Cond: ((("domain")::text = 'ping.icap-elios.com'::text) AND ((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now())) -> Bitmap Index Scan on index_cookies_select (cost=0.00..2.01 rows=1 width=0) (actual time=0.036..0.036 rows=0 loops=1) Index Cond: ((("domain")::text = '.icap-elios.com'::text) AND ((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now())) Total runtime: 39.616 ms (9 rows) I notice that the time execution increases significantly. and i need the vacuum query to obtain normal time execution: 3- After vacuum execution: icap=# vacuum cookies; VACUUM icap=# EXPLAIN ANALYZE 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'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on cookies (cost=4.02..8.04 rows=1 width=268) (actual time=0.111..0.112 rows=1 loops=1) Recheck Cond: (((("domain")::text = 'ping.icap-elios.com'::text) AND ((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now())) OR ((("domain")::text = '.icap-elios.com'::text) AND ((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now()))) Filter: (("timestamp" > now()) AND (NOT secure)) -> BitmapOr (cost=4.02..4.02 rows=1 width=0) (actual time=0.095..0.095 rows=0 loops=1) -> Bitmap Index Scan on index_cookies_select (cost=0.00..2.01 rows=1 width=0) (actual time=0.081..0.081 rows=1 loops=1) Index Cond: ((("domain")::text = 'ping.icap-elios.com'::text) AND ((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now())) -> Bitmap Index Scan on index_cookies_select (cost=0.00..2.01 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=1) Index Cond: ((("domain")::text = '.icap-elios.com'::text) AND ((principalid)::text = '192.168.8.219'::text) AND ("timestamp" > now())) Total runtime: 0.159 ms (9 rows) Thanks, Jamal --------------080302050007030004030709 Content-Type: text/x-vcard; charset=utf-8; name="Jamal.Ghaffour.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="Jamal.Ghaffour.vcf" begin:vcard fn:Jamal Ghaffour n:Ghaffour;Jamal org:ELIOS Informatique adr;quoted-printable:;;1, sq de ch=C3=AAne Germain,;CESSON SEVIGNE;;35510;FRANCE email;internet:Jamal.Ghaffour@elios-informatique.fr tel;work:(+33) 2.99.63.85.30 tel;fax:(+33) 2.99.63.85.93 tel;home:(+33) 2 99 36 73 96 tel;cell:(+33) 6.21.85.15.91 url:http://www.elios-informatique.fr version:2.1 end:vcard --------------080302050007030004030709--