Re: Please Help: PostgreSQL performance Optimization

От: Jamal Ghaffour
Тема: Re: Please Help: PostgreSQL performance Optimization
Дата: ,
Msg-id: 43C76F41.1030902@elios-informatique.fr
(см: обсуждение, исходный текст)
Ответ на: Re: Please Help: PostgreSQL performance Optimization  (Andrew Lazarus)
Список: pgsql-performance

Скрыть дерево обсуждения

Please Help: PostgreSQL performance Optimization  (Jamal Ghaffour, )
 Re: Please Help: PostgreSQL performance Optimization  (Jamal Ghaffour, )
  Re: Please Help: PostgreSQL performance Optimization  (Andrew Lazarus, )
   Re: Please Help: PostgreSQL performance Optimization  (Jamal Ghaffour, )
  Re: Please Help: PostgreSQL performance Optimization  (Jaime Casanova, )
 Re: Please Help: PostgreSQL performance Optimization  (Frank Wiles, )

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


Вложения

В списке pgsql-performance по дате сообщения:

От: Jamal Ghaffour
Дата:
Сообщение: Re: Please Help: PostgreSQL performance Optimization
От: Frank Wiles
Дата:
Сообщение: Re: Please Help: PostgreSQL performance Optimization