Question on COUNT performance
| От | Anders Østergaard Jensen |
|---|---|
| Тема | Question on COUNT performance |
| Дата | |
| Msg-id | AANLkTimyhXoid1bAk2FQZUx9TC3Qgh2Ba__-Fu4B8Bco@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Question on COUNT performance
(Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>)
Re: Question on COUNT performance (Lee Hachadoorian <lee.hachadoorian@gmail.com>) |
| Список | pgsql-sql |
Hello mailing list,
--
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: aj@itersys.dk / aj@meeho.dk
Phone: +61 406 880 313
Web (Intl): http://www.meeho.net/
Web (DK): http:/www.meeho.dk/
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: aj@itersys.dk / aj@meeho.dk
Phone: +61 406 880 313
Web (Intl): http://www.meeho.net/
Web (DK): http:/www.meeho.dk/
В списке pgsql-sql по дате отправления: