[#1010652] Command checker for output vars is using not performing query

View Trackers | Bugs | Download .csv | Monitor

Date:
2009-06-11 11:39 Priority:
3

State:
Open

Submitted by:
Adriaan van Kekem (avkekem) Assigned to:
Nobody (None)

Npgsql Version:
2.0 Category:

Group: Resolution:
Accepted

Summary:
Command checker for output vars is using not performing query

Detailed description
If you have a lot of tables/relations in postgresql, the pg_type en pg_proc tables become hugh. In my case they have around 100000 records! When analysing queries, i saw queries from ado.net which were very slow on that system. They took around 200ms instead of 1ms in an almost empty database! So i checked if we can optimize the queries. I added some casts so the postgresql query engine uses an index scan rather then a full table scan. This improves the performance of those queries from 200ms to 0.2ms! See the testcase below for exact numbers. Please patch this in the Npgsql library, so all people have profit of this. I used the latest Npgsql version (2.0.5). thanks, Adriaan Original query fired from Npgsql: explain analyze select count() > 0 from pg_proc p where prorettype = ( select oid from pg_type where typname = ((E'refcursor')::text) ) and proargtypes=((E'20 25 25 25 25 25 25 20 20 21 25 ')::oidvector) and proname=((E'"VitalHealthSoft_Countries_update"')::text) ; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=35739.18..35739.19 rows=1 width=0) (actual time=224.477..224.477 rows=1 loops=1) InitPlan -> Seq Scan on pg_type (cost=0.00..4351.65 rows=597 width=4) (actual time=0.033..45.044 rows=1 loops=1) Filter: ((typname)::text = 'refcursor'::text) -> Seq Scan on pg_proc p (cost=0.00..31387.52 rows=1 width=0) (actual time=224.461..224.461 rows=0 loops=1) Filter: ((prorettype = $0) AND (proargtypes = '20 25 25 25 25 25 25 20 20 21 25'::oidvector) AND ((proname)::text = '"VitalHealthSoft_Countries_update"'::text)) Total runtime: 224.557 ms (7 rows) --------- With patch: explain analyze select count() > 0 from pg_proc p where prorettype = ( select oid from pg_type where typname = ((E'refcursor')::text)::name ) and proargtypes=((E'20 25 25 25 25 25 25 20 20 21 25 ')::oidvector) and proname=((E'"VitalHealthSoft_Countries_update"')::text)::name ; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=16.94..16.95 rows=1 width=0) (actual time=0.034..0.034 rows=1 loops=1) InitPlan -> Index Scan using pg_type_typname_nsp_index on pg_type (cost=0.00..8.36 rows=1 width=4) (never executed) Index Cond: (typname = 'refcursor'::name) -> Index Scan using pg_proc_proname_args_nsp_index on pg_proc p (cost=0.00..8.57 rows=1 width=0) (actual time=0.019..0.019 rows=0 loops=1) Index Cond: ((proname = '"VitalHealthSoft_Countries_update"'::name) AND (proargtypes = '20 25 25 25 25 25 25 20 20 21 25'::oidvector)) Filter: (prorettype = $0) Total runtime: 0.172 ms (8 rows)

Followup

Message
Date: 2009-06-13 05:58 Sender: Francisco Figueiredo jr. Hi! This is now fixed in cvs. Please give it a try and let me know if it worked as expected. Thank you for your feedback and analyzes on this.
Date: 2009-06-12 17:57 Sender: Francisco Figueiredo jr. Hmmmmm, I think I can create a patch which translate this query to this: explain analyze select count(*) > 0 from pg_proc p where prorettype = ( select oid from pg_type where typname = ((E'refcursor')::name) ) and proargtypes=((E'20 25 25 25 25 25 25 20 20 21 25 ')::oidvector) and proname=((E'"VitalHealthSoft_Countries_update"')::name) I'll check it at home and let you know when I have it done.

Attached Files:

Attachments:

patchperformance.diff

Changes:

Field	Old Value	Date	By
Resolution	None	2009-06-13 05:58	fxjr
File Added	409: patchperformance.diff	2009-06-11 11:40	avkekem