Hello mailing list,
I have a performance problem with my postgres 8.4.4 database. The query is
the following:
SELECT count(*) AS count_all FROM "plan_events" WHERE (f_plan_event_acl(17,
plan_events.id))
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2859.77..2859.78 rows=1 width=0) (actual
time=4641.720..4641.720 rows=1 loops=1)
-> Seq Scan on plan_events (cost=0.00..2851.44 rows=3331 width=0)
(actual time=32.821..4640.116 rows=2669 loops=1)
Filter: f_plan_event_acl(17, id)
Total runtime: 4641.753 ms
(4 rows)
What can I do to improve the performance? The table has around 3000+ rows,
so the data size is really limited.
The table has the following definition:
-----
metabase=# \d plan_events;
Table "public.plan_events"
Column | Type |
Modifiers
------------------+--------------------------+-------------------------------------------------------------------
id | integer | not null default
nextval(('"plan_event_id_seq"'::text)::regclass)
description | text | not null
status | text | not null
pct_completed | integer | default 0
due | timestamp with time zone | not null
due_to | timestamp with time zone | not null
priority | integer | not null default 1
created | timestamp with time zone | not null
user_id_created | integer | not null
plan_type_id | integer | not null
finished | boolean | not null default false
duration | double precision | not null default 0.0
search_idx | tsvector |
org_id | integer | not null default 1
personal_user_id | integer |
place | text |
contact_log_id | integer |
Indexes:
"plan_events_pkey" PRIMARY KEY, btree (id)
"plan_event_contact_log_idx" btree (contact_log_id)
"plan_event_search_idx" gin (search_idx)
"plan_events_created_idx" btree (created)
"plan_events_due_idx" btree (due)
"plan_events_org_idx" btree (org_id)
"plan_events_personal_user_idx" btree (personal_user_id)
"plan_events_plan_type_id_idx" btree (plan_type_id)
"plan_events_user_id_created_idx" btree (user_id_created)
Foreign-key constraints:
"plan_events_contact_log_id_fkey" FOREIGN KEY (contact_log_id)
REFERENCES contact_logs(id)
"plan_events_org_id_fkey" FOREIGN KEY (org_id) REFERENCES orgs(id)
"plan_events_personal_user_id_fkey" FOREIGN KEY (personal_user_id)
REFERENCES users(id)
"plan_events_plan_type_id_fkey" FOREIGN KEY (plan_type_id) REFERENCES
plan_types(id)
"plan_events_user_id_created_fkey" FOREIGN KEY (user_id_created)
REFERENCES users(id)
Referenced by:
TABLE "contact_plan_events" CONSTRAINT
"contact_plan_events_plan_event_id_fkey" FOREIGN KEY (plan_event_id)
REFERENCES plan_events(id)
TABLE "custom_values" CONSTRAINT "custom_values_plan_event_id_fkey"
FOREIGN KEY (plan_event_id) REFERENCES plan_events(id)
TABLE "customer_plan_events" CONSTRAINT
"customer_plan_events_plan_event_id_fkey" FOREIGN KEY (plan_event_id)
REFERENCES plan_events(id)
TABLE "generic_comments" CONSTRAINT
"generic_comments_plan_event_id_fkey" FOREIGN KEY (plan_event_id) REFERENCES
plan_events(id)
TABLE "mail_queue_items" CONSTRAINT
"mail_queue_items_plan_event_id_fkey" FOREIGN KEY (plan_event_id) REFERENCES
plan_events(id)
TABLE "plan_event_notifications" CONSTRAINT
"plan_event_notifications_plan_event_id_fkey" FOREIGN KEY (plan_event_id)
REFERENCES plan_events(id)
TABLE "project_plan_events" CONSTRAINT
"project_plan_events_plan_event_id_fkey" FOREIGN KEY (plan_event_id)
REFERENCES plan_events(id)
TABLE "to_do_list_events" CONSTRAINT
"to_do_list_events_plan_event_id_fkey" FOREIGN KEY (plan_event_id)
REFERENCES plan_events(id)
TABLE "user_plan_events" CONSTRAINT
"user_plan_events_plan_event_id_fkey" FOREIGN KEY (plan_event_id) REFERENCES
plan_events(id)
Triggers:
plan_events_update BEFORE INSERT OR UPDATE ON plan_events FOR EACH ROW
EXECUTE PROCEDURE tsvector_update_trigger('search_idx', 'pg_catalog.danish',
'description', 'status')
------
And the f_plan_event function has the following definition:
metabase=# \df+ f_plan_event_acl
List of
functions
Schema | Name | Result data type | Argument data types
| Type | Volatility | Owner | Language |
Source code
|
Description
--------+------------------+------------------+------------------------------+--------+------------+----------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
public | f_plan_event_acl | boolean | uid integer, plan_id integer
| normal | volatile | postgres | plpgsql |
|
: declare
: user record;
: customer record;
: project record;
: pcount_rel record;
: ccount_rel record;
: begin
: select into user *
from users where id = uid;
: select into pcount_rel
COUNT(*) as acl_count FROM project_plan_events ppe WHERE ppe.plan_event_id =
plan_id;
: if
(pcount_rel.acl_count > 0) then
: SELECT INTO
project * FROM projects WHERE id IN (SELECT project_id FROM
project_plan_events ppe2 WHERE ppe2.plan_event_id = plan_id) OFFSET 0 LIMIT
1;
: return
f_project_acl(uid, project.id);
: end if;
: select into ccount_rel
COUNT(*) as acl_count FROM customer_plan_events cpe WHERE cpe.plan_event_id
= plan_id;
: if
(ccount_rel.acl_count > 0) then
: select into
customer * from customers where id in (select customer_id from
customer_plan_events cpe2 where cpe2.plan_event_id = plan_id) offset 0 limit
1;
: return
f_customer_acl(uid, customer.id);
: end if;
: return true;
: end;
:
--
Best Regards,
Anders Østergaard Jensen, B.Sc.
Technical Director & Managing Partner
Meeho! ApS
Meeho! Australia
5/28 Onslow Ave
Elizabeth Bay
2011 NSW
E-mail: a...@itersys.dk / a...@meeho.dk
Phone: +61 406 880 313
Web (Intl): http://www.meeho.net/
Web (DK): http:/www.meeho.dk/
