From: Jamal Ghaffour <Jamal.Ghaffour@elios-informatique.fr>
To: andrew@pillette.com
Cc: pgsql-performance@postgresql.org
Cc: systemguards@gmail.com
Subject: Re: Please Help: PostgreSQL performance Optimization
Date: Fri, 13 Jan 2006 10:13:37 +0100
Message-ID: <43C76F41.1030902@elios-informatique.fr> (raw)
In-Reply-To: <43C6A31F.3010007@pillette.com>
References: <43C66742.9060104@elios-informatique.fr>
<43C66D67.1000502@elios-informatique.fr>
<43C6A31F.3010007@pillette.com>
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<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.
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
Attachments:Jamal.Ghaffour.vcf (text/x-vcard, 376B) download
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
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: Jamal.Ghaffour@elios-informatique.fr, andrew@pillette.com, systemguards@gmail.com
Subject: Re: Please Help: PostgreSQL performance Optimization
In-Reply-To: <43C76F41.1030902@elios-informatique.fr>
* 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