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
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@xxxxxxxxxxxxxxxxxxxxx
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
