Re: Please Help: PostgreSQL performance Optimization - Mailing list pgsql-performance
From | Jamal Ghaffour |
---|---|
Subject | Re: Please Help: PostgreSQL performance Optimization |
Date | |
Msg-id | 43C76F41.1030902@elios-informatique.fr Whole thread Raw |
In response to | Re: Please Help: PostgreSQL performance Optimization (Andrew Lazarus <andrew@pillette.com>) |
List | pgsql-performance |
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
Attachment
pgsql-performance by date: