Re: Please Help: PostgreSQL performance Optimization

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